我的书签
添加书签
移除书签JSON
SinceVersion 1.2.0
NOTICE: In version 1.2.x the data type name is JSONB. It’s renamed to JSON to be more compatible to version 2.0.0. And the old tables can still be used.
description
1. JSON (Binary) datatype.
2. Use binary JSON format for storage and json function to extract field. Default support is 1048576 bytes (1M), adjustable up to 2147483643 bytes (2G),and the JSONB type is also limited by the be configuration `jsonb_type_length_soft_limit_bytes`.
note
1. There are some advantanges for JSON over plain JSON STRING.
2. 1. JSON syntax will be validated on write to ensure data quality
3. 2. JSON binary format is more efficient. Using json_extract functions on JSON datatype is 2-4 times faster than get_json_xx on JSON STRING format.
example
A tutorial for JSON datatype including create table, load data and query.
create database and table
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");
Load data
stream load test_json.csv test data
- there are 2 columns, the 1st column is id and the 2nd column is json string
- there are 25 rows, the first 18 rows are valid json and the last 7 rows are invalid
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]
- due to the 28% of rows is invalid，stream load with default configuration will fail with error message “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. }
- stream load will success after set header configuration ‘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. }
- use SELECT to view the data loaded by stream load. The column with JSON type will be displayed as plain 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)
write data using insert into
- total rows increae from 18 to 19 after insert 1 row
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)
Query
extract some filed from json by json_extract functions
- extract the whole json, ‘$’ stands for root in json path
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)
- extract k1 field, return NULL if it does not exist
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)
- extract element 0 of the top level array
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)
- extract a whole json array of name a1
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)
- extract nested field from an object in an array
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)
-
extract field with specific datatype
-
json_extract_string will extract field with string type，convert to string if the field is not 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 will extract field with int type，return NULL if the field is not int
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 will extract field with bigint type，return NULL if the field is not bigint
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 will extract field with double type，return NULL if the field is not double
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 will extract field with boolean type，return NULL if the fie
（本文超出站点单文档体积上限，已截断）
