我的书签
添加书签
移除书签JSON
SinceVersion 1.2.0
注意：在1.2.x版本中，JSON类型的名字是JSONB，为了尽量跟MySQL兼容，从2.0.0版本开始改名为JSON，老的表仍然可以使用。
description
1. JSON类型
2. 二进制JSON类型，采用二进制JSON格式存储，通过json函数访问JSON内部字段。默认支持1048576 字节（1M），可调大到 2147483643 字节（2G），可通过be配置`jsonb_type_length_soft_limit_bytes`调整
note
1. 与普通STRING类型存储的JSON字符串相比，JSON类型有两点优势
2. 1. 数据写入时进行JSON格式校验
3. 2. 二进制存储格式更加高效，通过json_extract等函数可以高效访问JSON内部字段，比get_json_xx函数快几倍
example
1. 用一个从建表、导数据、查询全周期的例子说明JSON数据类型的功能和用法。
创建库表
1. CREATE DATABASE testdb;
3. USE testdb;
5. CREATE TABLE test_json (
6. id INT,
7. j JSON
8. )
9. DUPLICATE KEY(id)
10. DISTRIBUTED BY HASH(id) BUCKETS 10
11. PROPERTIES("replication_num" = "1");
导入数据
stream load 导入test_json.csv测试数据
- 测试数据有2列，第一列id，第二列是json
- 测试数据有25行，其中前18行的json是合法的，后7行的json是非法的
1. 1 \N
2. 2 null
3. 3 true
4. 4 false
5. 5 100
6. 6 10000
7. 7 1000000000
8. 8 1152921504606846976
9. 9 6.18
10. 10 "abcd"
11. 11 {}
12. 12 {"k1":"v31", "k2": 300}
13. 13 []
14. 14 [123, 456]
15. 15 ["abc", "def"]
16. 16 [null, true, false, 100, 6.18, "abc"]
17. 17 [{"k1":"v41", "k2": 400}, 1, "a", 3.14]
18. 18 {"k1":"v31", "k2": 300, "a1": [{"k1":"v41", "k2": 400}, 1, "a", 3.14]}
19. 19 ''
20. 20 'abc'
21. 21 abc
22. 22 100x
23. 23 6.a8
24. 24 {x
25. 25 [123, abc]
- 由于有28%的非法数据，默认会失败报错 “too many filtered rows”
1. curl --location-trusted -u root: -T test_json.csv http://127.0.0.1:8840/api/testdb/test_json/_stream_load
2. {
3. "TxnId": 12019,
4. "Label": "744d9821-9c9f-43dc-bf3b-7ab048f14e32",
5. "TwoPhaseCommit": "false",
6. "Status": "Fail",
7. "Message": "too many filtered rows",
8. "NumberTotalRows": 25,
9. "NumberLoadedRows": 18,
10. "NumberFilteredRows": 7,
11. "NumberUnselectedRows": 0,
12. "LoadBytes": 380,
13. "LoadTimeMs": 48,
14. "BeginTxnTimeMs": 0,
15. "StreamLoadPutTimeMs": 1,
16. "ReadDataTimeMs": 0,
17. "WriteDataTimeMs": 45,
18. "CommitAndPublishTimeMs": 0,
19. "ErrorURL": "http://172.21.0.5:8840/api/_load_error_log?file=__shard_2/error_log_insert_stmt_95435c4bf5f156df-426735082a9296af_95435c4bf5f156df_426735082a9296af"
20. }
- 设置容错率参数 ‘max_filter_ratio: 0.3’
1. curl --location-trusted -u root: -H 'max_filter_ratio: 0.3' -T test_json.csv http://127.0.0.1:8840/api/testdb/test_json/_stream_load
2. {
3. "TxnId": 12017,
4. "Label": "f37a50c1-43e9-4f4e-a159-a3db6abe2579",
5. "TwoPhaseCommit": "false",
6. "Status": "Success",
7. "Message": "OK",
8. "NumberTotalRows": 25,
9. "NumberLoadedRows": 18,
10. "NumberFilteredRows": 7,
11. "NumberUnselectedRows": 0,
12. "LoadBytes": 380,
13. "LoadTimeMs": 68,
14. "BeginTxnTimeMs": 0,
15. "StreamLoadPutTimeMs": 2,
16. "ReadDataTimeMs": 0,
17. "WriteDataTimeMs": 45,
18. "CommitAndPublishTimeMs": 19,
19. "ErrorURL": "http://172.21.0.5:8840/api/_load_error_log?file=__shard_0/error_log_insert_stmt_a1463f98a7b15caf-c79399b920f5bfa3_a1463f98a7b15caf_c79399b920f5bfa3"
20. }
- 查看stream load导入的数据，JSON类型的列j会自动转成JSON string展示
1. mysql> SELECT * FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+
3. | id | j |
4. +------+---------------------------------------------------------------+
5. | 1 | NULL |
6. | 2 | null |
7. | 3 | true |
8. | 4 | false |
9. | 5 | 100 |
10. | 6 | 10000 |
11. | 7 | 1000000000 |
12. | 8 | 1152921504606846976 |
13. | 9 | 6.18 |
14. | 10 | "abcd" |
15. | 11 | {} |
16. | 12 | {"k1":"v31","k2":300} |
17. | 13 | [] |
18. | 14 | [123,456] |
19. | 15 | ["abc","def"] |
20. | 16 | [null,true,false,100,6.18,"abc"] |
21. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] |
22. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
23. +------+---------------------------------------------------------------+
24. 18 rows in set (0.03 sec)
insert into 插入数据
- insert 1条数据，总数据从18条增加到19条
1. mysql> INSERT INTO test_json VALUES(26, '{"k1":"v1", "k2": 200}');
2. Query OK, 1 row affected (0.09 sec)
3. {'label':'insert_4ece6769d1b42fd_ac9f25b3b8f3dc02', 'status':'VISIBLE', 'txnId':'12016'}
5. mysql> SELECT * FROM test_json ORDER BY id;
6. +------+---------------------------------------------------------------+
7. | id | j |
8. +------+---------------------------------------------------------------+
9. | 1 | NULL |
10. | 2 | null |
11. | 3 | true |
12. | 4 | false |
13. | 5 | 100 |
14. | 6 | 10000 |
15. | 7 | 1000000000 |
16. | 8 | 1152921504606846976 |
17. | 9 | 6.18 |
18. | 10 | "abcd" |
19. | 11 | {} |
20. | 12 | {"k1":"v31","k2":300} |
21. | 13 | [] |
22. | 14 | [123,456] |
23. | 15 | ["abc","def"] |
24. | 16 | [null,true,false,100,6.18,"abc"] |
25. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] |
26. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
27. | 26 | {"k1":"v1","k2":200} |
28. +------+---------------------------------------------------------------+
29. 19 rows in set (0.03 sec)
查询
用json_extract取json内的某个字段
- 获取整个json，$ 在json path中代表root，即整个json
1. +------+---------------------------------------------------------------+---------------------------------------------------------------+
2. | id | j | json_extract(`j`, '$') |
3. +------+---------------------------------------------------------------+---------------------------------------------------------------+
4. | 1 | NULL | NULL |
5. | 2 | null | null |
6. | 3 | true | true |
7. | 4 | false | false |
8. | 5 | 100 | 100 |
9. | 6 | 10000 | 10000 |
10. | 7 | 1000000000 | 1000000000 |
11. | 8 | 1152921504606846976 | 1152921504606846976 |
12. | 9 | 6.18 | 6.18 |
13. | 10 | "abcd" | "abcd" |
14. | 11 | {} | {} |
15. | 12 | {"k1":"v31","k2":300} | {"k1":"v31","k2":300} |
16. | 13 | [] | [] |
17. | 14 | [123,456] | [123,456] |
18. | 15 | ["abc","def"] | ["abc","def"] |
19. | 16 | [null,true,false,100,6.18,"abc"] | [null,true,false,100,6.18,"abc"] |
20. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | [{"k1":"v41","k2":400},1,"a",3.14] |
21. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
22. | 26 | {"k1":"v1","k2":200} | {"k1":"v1","k2":200} |
23. +------+---------------------------------------------------------------+---------------------------------------------------------------+
24. 19 rows in set (0.03 sec)
- 获取k1字段，没有k1字段的行返回NULL
1. mysql> SELECT id, j, json_extract(j, '$.k1') FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+----------------------------+
3. | id | j | json_extract(`j`, '$.k1') |
4. +------+---------------------------------------------------------------+----------------------------+
5. | 1 | NULL | NULL |
6. | 2 | null | NULL |
7. | 3 | true | NULL |
8. | 4 | false | NULL |
9. | 5 | 100 | NULL |
10. | 6 | 10000 | NULL |
11. | 7 | 1000000000 | NULL |
12. | 8 | 1152921504606846976 | NULL |
13. | 9 | 6.18 | NULL |
14. | 10 | "abcd" | NULL |
15. | 11 | {} | NULL |
16. | 12 | {"k1":"v31","k2":300} | "v31" |
17. | 13 | [] | NULL |
18. | 14 | [123,456] | NULL |
19. | 15 | ["abc","def"] | NULL |
20. | 16 | [null,true,false,100,6.18,"abc"] | NULL |
21. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
22. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | "v31" |
23. | 26 | {"k1":"v1","k2":200} | "v1" |
24. +------+---------------------------------------------------------------+----------------------------+
25. 19 rows in set (0.03 sec)
- 获取顶层数组的第0个元素
1. mysql> SELECT id, j, json_extract(j, '$[0]') FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+----------------------------+
3. | id | j | json_extract(`j`, '$[0]') |
4. +------+---------------------------------------------------------------+----------------------------+
5. | 1 | NULL | NULL |
6. | 2 | null | NULL |
7. | 3 | true | NULL |
8. | 4 | false | NULL |
9. | 5 | 100 | NULL |
10. | 6 | 10000 | NULL |
11. | 7 | 1000000000 | NULL |
12. | 8 | 1152921504606846976 | NULL |
13. | 9 | 6.18 | NULL |
14. | 10 | "abcd" | NULL |
15. | 11 | {} | NULL |
16. | 12 | {"k1":"v31","k2":300} | NULL |
17. | 13 | [] | NULL |
18. | 14 | [123,456] | 123 |
19. | 15 | ["abc","def"] | "abc" |
20. | 16 | [null,true,false,100,6.18,"abc"] | null |
21. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | {"k1":"v41","k2":400} |
22. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
23. | 26 | {"k1":"v1","k2":200} | NULL |
24. +------+---------------------------------------------------------------+----------------------------+
25. 19 rows in set (0.03 sec)
- 获取整个json array
1. mysql> SELECT id, j, json_extract(j, '$.a1') FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+------------------------------------+
3. | id | j | json_extract(`j`, '$.a1') |
4. +------+---------------------------------------------------------------+------------------------------------+
5. | 1 | NULL | NULL |
6. | 2 | null | NULL |
7. | 3 | true | NULL |
8. | 4 | false | NULL |
9. | 5 | 100 | NULL |
10. | 6 | 10000 | NULL |
11. | 7 | 1000000000 | NULL |
12. | 8 | 1152921504606846976 | NULL |
13. | 9 | 6.18 | NULL |
14. | 10 | "abcd" | NULL |
15. | 11 | {} | NULL |
16. | 12 | {"k1":"v31","k2":300} | NULL |
17. | 13 | [] | NULL |
18. | 14 | [123,456] | NULL |
19. | 15 | ["abc","def"] | NULL |
20. | 16 | [null,true,false,100,6.18,"abc"] | NULL |
21. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
22. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | [{"k1":"v41","k2":400},1,"a",3.14] |
23. | 26 | {"k1":"v1","k2":200} | NULL |
24. +------+---------------------------------------------------------------+------------------------------------+
25. 19 rows in set (0.02 sec)
- 获取json array中嵌套object的字段
1. mysql> SELECT id, j, json_extract(j, '$.a1[0]'), json_extract(j, '$.a1[0].k1') FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+-------------------------------+----------------------------------+
3. | id | j | json_extract(`j`, '$.a1[0]') | json_extract(`j`, '$.a1[0].k1') |
4. +------+---------------------------------------------------------------+-------------------------------+----------------------------------+
5. | 1 | NULL | NULL | NULL |
6. | 2 | null | NULL | NULL |
7. | 3 | true | NULL | NULL |
8. | 4 | false | NULL | NULL |
9. | 5 | 100 | NULL | NULL |
10. | 6 | 10000 | NULL | NULL |
11. | 7 | 1000000000 | NULL | NULL |
12. | 8 | 1152921504606846976 | NULL | NULL |
13. | 9 | 6.18 | NULL | NULL |
14. | 10 | "abcd" | NULL | NULL |
15. | 11 | {} | NULL | NULL |
16. | 12 | {"k1":"v31","k2":300} | NULL | NULL |
17. | 13 | [] | NULL | NULL |
18. | 14 | [123,456] | NULL | NULL |
19. | 15 | ["abc","def"] | NULL | NULL |
20. | 16 | [null,true,false,100,6.18,"abc"] | NULL | NULL |
21. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL | NULL |
22. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | {"k1":"v41","k2":400} | "v41" |
23. | 26 | {"k1":"v1","k2":200} | NULL | NULL |
24. +------+---------------------------------------------------------------+-------------------------------+----------------------------------+
25. 19 rows in set (0.02 sec)
-
获取具体类型的
-
json_extract_string 获取string类型字段，非string类型转成string
1. mysql> SELECT id, j, json_extract_string(j, '$') FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+---------------------------------------------------------------+
3. | id | j | json_extract_string(`j`, '$') |
4. +------+---------------------------------------------------------------+---------------------------------------------------------------+
5. | 1 | NULL | NULL |
6. | 2 | null | null |
7. | 3 | true | true |
8. | 4 | false | false |
9. | 5 | 100 | 100 |
10. | 6 | 10000 | 10000 |
11. | 7 | 1000000000 | 1000000000 |
12. | 8 | 1152921504606846976 | 1152921504606846976 |
13. | 9 | 6.18 | 6.18 |
14. | 10 | "abcd" | abcd |
15. | 11 | {} | {} |
16. | 12 | {"k1":"v31","k2":300} | {"k1":"v31","k2":300} |
17. | 13 | [] | [] |
18. | 14 | [123,456] | [123,456] |
19. | 15 | ["abc","def"] | ["abc","def"] |
20. | 16 | [null,true,false,100,6.18,"abc"] | [null,true,false,100,6.18,"abc"] |
21. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | [{"k1":"v41","k2":400},1,"a",3.14] |
22. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} |
23. | 26 | {"k1":"v1","k2":200} | {"k1":"v1","k2":200} |
24. +------+---------------------------------------------------------------+---------------------------------------------------------------+
25. 19 rows in set (0.02 sec)
27. mysql> SELECT id, j, json_extract_string(j, '$.k1') FROM test_json ORDER BY id;
28. +------+---------------------------------------------------------------+-----------------------------------+
29. | id | j | json_extract_string(`j`, '$.k1') |
30. +------+---------------------------------------------------------------+-----------------------------------+
31. | 1 | NULL | NULL |
32. | 2 | null | NULL |
33. | 3 | true | NULL |
34. | 4 | false | NULL |
35. | 5 | 100 | NULL |
36. | 6 | 10000 | NULL |
37. | 7 | 1000000000 | NULL |
38. | 8 | 1152921504606846976 | NULL |
39. | 9 | 6.18 | NULL |
40. | 10 | "abcd" | NULL |
41. | 11 | {} | NULL |
42. | 12 | {"k1":"v31","k2":300} | v31 |
43. | 13 | [] | NULL |
44. | 14 | [123,456] | NULL |
45. | 15 | ["abc","def"] | NULL |
46. | 16 | [null,true,false,100,6.18,"abc"] | NULL |
47. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
48. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | v31 |
49. | 26 | {"k1":"v1","k2":200} | v1 |
50. +------+---------------------------------------------------------------+-----------------------------------+
51. 19 rows in set (0.03 sec)
- json_extract_int 获取int类型字段，非int类型返回NULL
1. mysql> SELECT id, j, json_extract_int(j, '$') FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+-----------------------------+
3. | id | j | json_extract_int(`j`, '$') |
4. +------+---------------------------------------------------------------+-----------------------------+
5. | 1 | NULL | NULL |
6. | 2 | null | NULL |
7. | 3 | true | NULL |
8. | 4 | false | NULL |
9. | 5 | 100 | 100 |
10. | 6 | 10000 | 10000 |
11. | 7 | 1000000000 | 1000000000 |
12. | 8 | 1152921504606846976 | NULL |
13. | 9 | 6.18 | NULL |
14. | 10 | "abcd" | NULL |
15. | 11 | {} | NULL |
16. | 12 | {"k1":"v31","k2":300} | NULL |
17. | 13 | [] | NULL |
18. | 14 | [123,456] | NULL |
19. | 15 | ["abc","def"] | NULL |
20. | 16 | [null,true,false,100,6.18,"abc"] | NULL |
21. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
22. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
23. | 26 | {"k1":"v1","k2":200} | NULL |
24. +------+---------------------------------------------------------------+-----------------------------+
25. 19 rows in set (0.02 sec)
27. mysql> SELECT id, j, json_extract_int(j, '$.k2') FROM test_json ORDER BY id;
28. +------+---------------------------------------------------------------+--------------------------------+
29. | id | j | json_extract_int(`j`, '$.k2') |
30. +------+---------------------------------------------------------------+--------------------------------+
31. | 1 | NULL | NULL |
32. | 2 | null | NULL |
33. | 3 | true | NULL |
34. | 4 | false | NULL |
35. | 5 | 100 | NULL |
36. | 6 | 10000 | NULL |
37. | 7 | 1000000000 | NULL |
38. | 8 | 1152921504606846976 | NULL |
39. | 9 | 6.18 | NULL |
40. | 10 | "abcd" | NULL |
41. | 11 | {} | NULL |
42. | 12 | {"k1":"v31","k2":300} | 300 |
43. | 13 | [] | NULL |
44. | 14 | [123,456] | NULL |
45. | 15 | ["abc","def"] | NULL |
46. | 16 | [null,true,false,100,6.18,"abc"] | NULL |
47. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
48. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 300 |
49. | 26 | {"k1":"v1","k2":200} | 200 |
50. +------+---------------------------------------------------------------+--------------------------------+
51. 19 rows in set (0.03 sec)
- json_extract_bigint 获取bigint类型字段，非bigint类型返回NULL
1. mysql> SELECT id, j, json_extract_bigint(j, '$') FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+--------------------------------+
3. | id | j | json_extract_bigint(`j`, '$') |
4. +------+---------------------------------------------------------------+--------------------------------+
5. | 1 | NULL | NULL |
6. | 2 | null | NULL |
7. | 3 | true | NULL |
8. | 4 | false | NULL |
9. | 5 | 100 | 100 |
10. | 6 | 10000 | 10000 |
11. | 7 | 1000000000 | 1000000000 |
12. | 8 | 1152921504606846976 | 1152921504606846976 |
13. | 9 | 6.18 | NULL |
14. | 10 | "abcd" | NULL |
15. | 11 | {} | NULL |
16. | 12 | {"k1":"v31","k2":300} | NULL |
17. | 13 | [] | NULL |
18. | 14 | [123,456] | NULL |
19. | 15 | ["abc","def"] | NULL |
20. | 16 | [null,true,false,100,6.18,"abc"] | NULL |
21. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
22. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
23. | 26 | {"k1":"v1","k2":200} | NULL |
24. +------+---------------------------------------------------------------+--------------------------------+
25. 19 rows in set (0.03 sec)
27. mysql> SELECT id, j, json_extract_bigint(j, '$.k2') FROM test_json ORDER BY id;
28. +------+---------------------------------------------------------------+-----------------------------------+
29. | id | j | json_extract_bigint(`j`, '$.k2') |
30. +------+---------------------------------------------------------------+-----------------------------------+
31. | 1 | NULL | NULL |
32. | 2 | null | NULL |
33. | 3 | true | NULL |
34. | 4 | false | NULL |
35. | 5 | 100 | NULL |
36. | 6 | 10000 | NULL |
37. | 7 | 1000000000 | NULL |
38. | 8 | 1152921504606846976 | NULL |
39. | 9 | 6.18 | NULL |
40. | 10 | "abcd" | NULL |
41. | 11 | {} | NULL |
42. | 12 | {"k1":"v31","k2":300} | 300 |
43. | 13 | [] | NULL |
44. | 14 | [123,456] | NULL |
45. | 15 | ["abc","def"] | NULL |
46. | 16 | [null,true,false,100,6.18,"abc"] | NULL |
47. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
48. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 300 |
49. | 26 | {"k1":"v1","k2":200} | 200 |
50. +------+---------------------------------------------------------------+-----------------------------------+
51. 19 rows in set (0.02 sec)
- json_extract_double 获取double类型字段，非double类型返回NULL
1. mysql> SELECT id, j, json_extract_double(j, '$') FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+--------------------------------+
3. | id | j | json_extract_double(`j`, '$') |
4. +------+---------------------------------------------------------------+--------------------------------+
5. | 1 | NULL | NULL |
6. | 2 | null | NULL |
7. | 3 | true | NULL |
8. | 4 | false | NULL |
9. | 5 | 100 | 100 |
10. | 6 | 10000 | 10000 |
11. | 7 | 1000000000 | 1000000000 |
12. | 8 | 1152921504606846976 | 1.152921504606847e+18 |
13. | 9 | 6.18 | 6.18 |
14. | 10 | "abcd" | NULL |
15. | 11 | {} | NULL |
16. | 12 | {"k1":"v31","k2":300} | NULL |
17. | 13 | [] | NULL |
18. | 14 | [123,456] | NULL |
19. | 15 | ["abc","def"] | NULL |
20. | 16 | [null,true,false,100,6.18,"abc"] | NULL |
21. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
22. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | NULL |
23. | 26 | {"k1":"v1","k2":200} | NULL |
24. +------+---------------------------------------------------------------+--------------------------------+
25. 19 rows in set (0.02 sec)
27. mysql> SELECT id, j, json_extract_double(j, '$.k2') FROM test_json ORDER BY id;
28. +------+---------------------------------------------------------------+-----------------------------------+
29. | id | j | json_extract_double(`j`, '$.k2') |
30. +------+---------------------------------------------------------------+-----------------------------------+
31. | 1 | NULL | NULL |
32. | 2 | null | NULL |
33. | 3 | true | NULL |
34. | 4 | false | NULL |
35. | 5 | 100 | NULL |
36. | 6 | 10000 | NULL |
37. | 7 | 1000000000 | NULL |
38. | 8 | 1152921504606846976 | NULL |
39. | 9 | 6.18 | NULL |
40. | 10 | "abcd" | NULL |
41. | 11 | {} | NULL |
42. | 12 | {"k1":"v31","k2":300} | 300 |
43. | 13 | [] | NULL |
44. | 14 | [123,456] | NULL |
45. | 15 | ["abc","def"] | NULL |
46. | 16 | [null,true,false,100,6.18,"abc"] | NULL |
47. | 17 | [{"k1":"v41","k2":400},1,"a",3.14] | NULL |
48. | 18 | {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} | 300 |
49. | 26 | {"k1":"v1","k2":200} | 200 |
50. +------+---------------------------------------------------------------+-----------------------------------+
51. 19 rows in set (0.03 sec)
- json_extract_bool 获取bool类型字段，非bool类型返回NULL
``
1. mysql> SELECT id, j, json_extract_bool(j, '$') FROM test_json ORDER BY id;
2. +------+---------------------------------------------------------------+------------------------------+
3. | id | j | json_extract_bool(j`, '$') |
4. +------+---------------------------------------------------------------+------------------------------+
5. | 1 | NULL | NULL |
6. | 2 | null | NULL |
7. | 3 | true | 1 |
8. | 4 | false | 0 |
9. | 5 | 100 | NULL |
10. | 6 | 10000 | NULL |
11. | 7 | 1000000000 | NULL |
12. | 8 |
（本文超出站点单文档体积上限，已截断）
