mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-08-25 20:01:50 +02:00
Code Issues Projects Releases Packages Wiki Activity
mariadb/mysql-test/main/mdev_32854.result
Rucha Deodhar 2a0fcd3757 MDEV-32854: Make JSON_DEPTH_LIMIT unlimited 
The initial hard capped limit on the depth was 32. It was implemented using
static arrays of relevant type and size 32.
Hence, to implement unlimited depth, dynamic array on mem_root was
implemented which grows by 3200 as needed. Relevant arrays were replaced
with this dynamic array.
2025-07-22 17:32:21 +05:30

454 lines
56 KiB
Text
Raw Permalink Blame History

SET @json_doc_obj_lev45_valid='{ "level1": { "level2": { "level3": { "level4": { "level5": { "level6": { "level7": { "level8": { "level9": { "level10": { "level11": { "level12": { "level13": { "level14": { "level15": { "level16": { "level17": { "level18": { "level19": { "level20": { "level21": { "level22": { "level23": { "level24": { "level25": { "level26": { "level27": { "level28": { "level29": { "level30": { "level31": { "level32": { "level33": { "level34": { "level35": { "level36": { "level37": { "level38": { "level39": { "level40": { "level41": { "level42": { "level43": { "level44": { "level45": "This is level 45" } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } }';
SET @json_doc_arr_lev45_valid='[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]';
CREATE TABLE t1 (val JSON);
INSERT INTO t1 VALUES (@json_doc_arr_lev45_valid), ('[1, 2, 10,3, [4, 5, 6, [7, 8, 9]]]');
SELECT JSON_ARRAY(@json_doc_arr_lev45_valid, 1, 2, 3);
JSON_ARRAY(@json_doc_arr_lev45_valid, 1, 2, 3)
["[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]", 1, 2, 3]
SELECT JSON_ARRAY (@json_doc_obj_lev45_valid, '{"key1":1, "key2":2}');
JSON_ARRAY (@json_doc_obj_lev45_valid, '{"key1":1, "key2":2}')
["{ \"level1\": { \"level2\": { \"level3\": { \"level4\": { \"level5\": { \"level6\": { \"level7\": { \"level8\": { \"level9\": { \"level10\": { \"level11\": { \"level12\": { \"level13\": { \"level14\": { \"level15\": { \"level16\": { \"level17\": { \"level18\": { \"level19\": { \"level20\": { \"level21\": { \"level22\": { \"level23\": { \"level24\": { \"level25\": { \"level26\": { \"level27\": { \"level28\": { \"level29\": { \"level30\": { \"level31\": { \"level32\": { \"level33\": { \"level34\": { \"level35\": { \"level36\": { \"level37\": { \"level38\": { \"level39\": { \"level40\": { \"level41\": { \"level42\": { \"level43\": { \"level44\": { \"level45\": \"This is level 45\" } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } }", "{\"key1\":1, \"key2\":2}"]
SELECT JSON_ARRAYAGG(val) FROM t1;
JSON_ARRAYAGG(val)
[[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]],[1, 2, 10,3, [4, 5, 6, [7, 8, 9]]]]
SELECT JSON_ARRAY_APPEND(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]', false) AS result;
result
[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45, false]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
SELECT JSON_ARRAY_INSERT(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]', false) AS result;
result
[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, false, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
SELECT JSON_ARRAY_INTERSECT(@json_doc_arr_lev45_valid, '[1, [2, [3]]]');
JSON_ARRAY_INTERSECT(@json_doc_arr_lev45_valid, '[1, [2, [3]]]')
[1]
SELECT JSON_COMPACT(@json_doc_obj_lev45_valid);
JSON_COMPACT(@json_doc_obj_lev45_valid)
{"level1":{"level2":{"level3":{"level4":{"level5":{"level6":{"level7":{"level8":{"level9":{"level10":{"level11":{"level12":{"level13":{"level14":{"level15":{"level16":{"level17":{"level18":{"level19":{"level20":{"level21":{"level22":{"level23":{"level24":{"level25":{"level26":{"level27":{"level28":{"level29":{"level30":{"level31":{"level32":{"level33":{"level34":{"level35":{"level36":{"level37":{"level38":{"level39":{"level40":{"level41":{"level42":{"level43":{"level44":{"level45":"This is level 45"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}
SELECT JSON_COMPACT(@json_doc_arr_lev45_valid);
JSON_COMPACT(@json_doc_arr_lev45_valid)
[1,[2,[3,[4,[5,[6,[7,[8,[9,[10,[11,[12,[13,[14,[15,[16,[17,[18,[19,[20,[21,[22,[23,[24,[25,[26,[27,[28,[29,[30,[31,[32,[33,[34,[35,[36,[37,[38,[39,[40,[41,[42,[43,[44,[45,45,45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
SELECT JSON_CONTAINS(@json_doc_obj_lev45_valid, ' { "level45": "This is level 45" }', '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41.level42.level43.level44');
JSON_CONTAINS(@json_doc_obj_lev45_valid, ' { "level45": "This is level 45" }', '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level2
1
SELECT JSON_CONTAINS(@json_doc_arr_lev45_valid, '[45, 45, 45]', '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]');
JSON_CONTAINS(@json_doc_arr_lev45_valid, '[45, 45, 45]', '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]')
1
SELECT JSON_CONTAINS_PATH(@json_doc_arr_lev45_valid, 'all', '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]');
JSON_CONTAINS_PATH(@json_doc_arr_lev45_valid, 'all', '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]')
1
SELECT JSON_CONTAINS_PATH(@json_doc_obj_lev45_valid, 'all', '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41.level42.level43.level44');
JSON_CONTAINS_PATH(@json_doc_obj_lev45_valid, 'all', '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.
1
SELECT JSON_DEPTH(@json_doc_arr_lev45_valid);
JSON_DEPTH(@json_doc_arr_lev45_valid)
46
SELECT JSON_DEPTH(@json_doc_obj_lev45_valid);
JSON_DEPTH(@json_doc_obj_lev45_valid)
46
SELECT JSON_DETAILED(@json_doc_obj_lev45_valid);
JSON_DETAILED(@json_doc_obj_lev45_valid)
{
"level1":
{
"level2":
{
"level3":
{
"level4":
{
"level5":
{
"level6":
{
"level7":
{
"level8":
{
"level9":
{
"level10":
{
"level11":
{
"level12":
{
"level13":
{
"level14":
{
"level15":
{
"level16":
{
"level17":
{
"level18":
{
"level19":
{
"level20":
{
"level21":
{
"level22":
{
"level23":
{
"level24":
{
"level25":
{
"level26":
{
"level27":
{
"level28":
{
"level29":
{
"level30":
{
"level31":
{
"level32":
{
"level33":
{
"level34":
{
"level35":
{
"level36":
{
"level37":
{
"level38":
{
"level39":
{
"level40":
{
"level41":
{
"level42":
{
"level43":
{
"level44":
{
"level45": "This is level 45"
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
SELECT JSON_DETAILED(@json_doc_arr_lev45_valid);
JSON_DETAILED(@json_doc_arr_lev45_valid)
[
1,
[
2,
[
3,
[
4,
[
5,
[
6,
[
7,
[
8,
[
9,
[
10,
[
11,
[
12,
[
13,
[
14,
[
15,
[
16,
[
17,
[
18,
[
19,
[
20,
[
21,
[
22,
[
23,
[
24,
[
25,
[
26,
[
27,
[
28,
[
29,
[
30,
[
31,
[
32,
[
33,
[
34,
[
35,
[
36,
[
37,
[
38,
[
39,
[
40,
[
41,
[
42,
[
43,
[
44,
[
45,
45,
45
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
]
SELECT JSON_EQUALS(@json_doc_arr_lev45_valid, @json_doc_arr_lev45_valid);
JSON_EQUALS(@json_doc_arr_lev45_valid, @json_doc_arr_lev45_valid)
1
SELECT JSON_EQUALS(@json_doc_obj_lev45_valid, @json_doc_obj_lev45_valid);
JSON_EQUALS(@json_doc_obj_lev45_valid, @json_doc_obj_lev45_valid)
1
SELECT JSON_EXISTS(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]');
JSON_EXISTS(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]')
1
SELECT JSON_EXISTS(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41.level42.level43.level44');
JSON_EXISTS(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level2
1
SELECT JSON_EXTRACT(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]');
JSON_EXTRACT(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]')
[45, 45, 45]
SELECT JSON_EXTRACT(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41.level42.level43.level44');
JSON_EXTRACT(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level
{"level45": "This is level 45"}
SELECT JSON_INSERT(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][3]', 46);
JSON_INSERT(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][3]', 46)
[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, [45, 46], 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
SELECT JSON_KEY_VALUE(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41.level42.level43.level44');
JSON_KEY_VALUE(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.lev
[{"key": "level45", "value": "This is level 45"}]
SELECT JSON_KEYS(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41.level42.level43.level44');
JSON_KEYS(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.
["level45"]
SELECT JSON_LENGTH(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41.level42.level43.level44');
JSON_LENGTH(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level2
1
SELECT JSON_LENGTH(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]');
JSON_LENGTH(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]')
1
SELECT JSON_LOOSE(@json_doc_obj_lev45_valid);
JSON_LOOSE(@json_doc_obj_lev45_valid)
{"level1": {"level2": {"level3": {"level4": {"level5": {"level6": {"level7": {"level8": {"level9": {"level10": {"level11": {"level12": {"level13": {"level14": {"level15": {"level16": {"level17": {"level18": {"level19": {"level20": {"level21": {"level22": {"level23": {"level24": {"level25": {"level26": {"level27": {"level28": {"level29": {"level30": {"level31": {"level32": {"level33": {"level34": {"level35": {"level36": {"level37": {"level38": {"level39": {"level40": {"level41": {"level42": {"level43": {"level44": {"level45": "This is level 45"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}
SELECT JSON_LOOSE(@json_doc_arr_lev45_valid);
JSON_LOOSE(@json_doc_arr_lev45_valid)
[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
SELECT JSON_MERGE(@json_doc_obj_lev45_valid, '{"key1":"val1", "key2":"val2"}');
JSON_MERGE(@json_doc_obj_lev45_valid, '{"key1":"val1", "key2":"val2"}')
{"level1": {"level2": {"level3": {"level4": {"level5": {"level6": {"level7": {"level8": {"level9": {"level10": {"level11": {"level12": {"level13": {"level14": {"level15": {"level16": {"level17": {"level18": {"level19": {"level20": {"level21": {"level22": {"level23": {"level24": {"level25": {"level26": {"level27": {"level28": {"level29": {"level30": {"level31": {"level32": {"level33": {"level34": {"level35": {"level36": {"level37": {"level38": {"level39": {"level40": {"level41": {"level42": {"level43": {"level44": {"level45": "This is level 45"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}, "key1": "val1", "key2": "val2"}
SELECT JSON_MERGE(@json_doc_arr_lev45_valid, '[1, 2, 3]');
JSON_MERGE(@json_doc_arr_lev45_valid, '[1, 2, 3]')
[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]], 1, 2, 3]
SELECT JSON_MERGE_PATCH(@json_doc_obj_lev45_valid, '{"key1":"val1", "key2":"val2"}');
JSON_MERGE_PATCH(@json_doc_obj_lev45_valid, '{"key1":"val1", "key2":"val2"}')
{"level1": {"level2": {"level3": {"level4": {"level5": {"level6": {"level7": {"level8": {"level9": {"level10": {"level11": {"level12": {"level13": {"level14": {"level15": {"level16": {"level17": {"level18": {"level19": {"level20": {"level21": {"level22": {"level23": {"level24": {"level25": {"level26": {"level27": {"level28": {"level29": {"level30": {"level31": {"level32": {"level33": {"level34": {"level35": {"level36": {"level37": {"level38": {"level39": {"level40": {"level41": {"level42": {"level43": {"level44": {"level45": "This is level 45"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}, "key1": "val1", "key2": "val2"}
SELECT JSON_MERGE_PATCH(@json_doc_arr_lev45_valid, '[1, 2, 3]');
JSON_MERGE_PATCH(@json_doc_arr_lev45_valid, '[1, 2, 3]')
[1, 2, 3]
SELECT JSON_MERGE_PRESERVE(@json_doc_obj_lev45_valid, @json_doc_obj_lev45_valid);
JSON_MERGE_PRESERVE(@json_doc_obj_lev45_valid, @json_doc_obj_lev45_valid)
{"level1": {"level2": {"level3": {"level4": {"level5": {"level6": {"level7": {"level8": {"level9": {"level10": {"level11": {"level12": {"level13": {"level14": {"level15": {"level16": {"level17": {"level18": {"level19": {"level20": {"level21": {"level22": {"level23": {"level24": {"level25": {"level26": {"level27": {"level28": {"level29": {"level30": {"level31": {"level32": {"level33": {"level34": {"level35": {"level36": {"level37": {"level38": {"level39": {"level40": {"level41": {"level42": {"level43": {"level44": {"level45": ["This is level 45", "This is level 45"]}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}
SELECT JSON_MERGE_PRESERVE(@json_doc_arr_lev45_valid, @json_doc_arr_lev45_valid);
JSON_MERGE_PRESERVE(@json_doc_arr_lev45_valid, @json_doc_arr_lev45_valid)
[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]], 1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
SELECT JSON_NORMALIZE(@json_doc_arr_lev45_valid);
JSON_NORMALIZE(@json_doc_arr_lev45_valid)
[1.0E0,[2.0E0,[3.0E0,[4.0E0,[5.0E0,[6.0E0,[7.0E0,[8.0E0,[9.0E0,[1.0E1,[1.1E1,[1.2E1,[1.3E1,[1.4E1,[1.5E1,[1.6E1,[1.7E1,[1.8E1,[1.9E1,[2.0E1,[2.1E1,[2.2E1,[2.3E1,[2.4E1,[2.5E1,[2.6E1,[2.7E1,[2.8E1,[2.9E1,[3.0E1,[3.1E1,[3.2E1,[3.3E1,[3.4E1,[3.5E1,[3.6E1,[3.7E1,[3.8E1,[3.9E1,[4.0E1,[4.1E1,[4.2E1,[4.3E1,[4.4E1,[4.5E1,4.5E1,4.5E1]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
SELECT JSON_OBJECT('level1', @json_doc_obj_lev45_valid);
JSON_OBJECT('level1', @json_doc_obj_lev45_valid)
{"level1": "{ \"level1\": { \"level2\": { \"level3\": { \"level4\": { \"level5\": { \"level6\": { \"level7\": { \"level8\": { \"level9\": { \"level10\": { \"level11\": { \"level12\": { \"level13\": { \"level14\": { \"level15\": { \"level16\": { \"level17\": { \"level18\": { \"level19\": { \"level20\": { \"level21\": { \"level22\": { \"level23\": { \"level24\": { \"level25\": { \"level26\": { \"level27\": { \"level28\": { \"level29\": { \"level30\": { \"level31\": { \"level32\": { \"level33\": { \"level34\": { \"level35\": { \"level36\": { \"level37\": { \"level38\": { \"level39\": { \"level40\": { \"level41\": { \"level42\": { \"level43\": { \"level44\": { \"level45\": \"This is level 45\" } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } }"}
SELECT JSON_OBJECT_FILTER_KEYS(@json_doc_obj_lev45_valid, '["level1"]');
JSON_OBJECT_FILTER_KEYS(@json_doc_obj_lev45_valid, '["level1"]')
{"level1": {"level2": {"level3": {"level4": {"level5": {"level6": {"level7": {"level8": {"level9": {"level10": {"level11": {"level12": {"level13": {"level14": {"level15": {"level16": {"level17": {"level18": {"level19": {"level20": {"level21": {"level22": {"level23": {"level24": {"level25": {"level26": {"level27": {"level28": {"level29": {"level30": {"level31": {"level32": {"level33": {"level34": {"level35": {"level36": {"level37": {"level38": {"level39": {"level40": {"level41": {"level42": {"level43": {"level44": {"level45": "This is level 45"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}
SELECT JSON_OBJECT_TO_ARRAY(@json_doc_obj_lev45_valid);
JSON_OBJECT_TO_ARRAY(@json_doc_obj_lev45_valid)
[["level1", {"level2": {"level3": {"level4": {"level5": {"level6": {"level7": {"level8": {"level9": {"level10": {"level11": {"level12": {"level13": {"level14": {"level15": {"level16": {"level17": {"level18": {"level19": {"level20": {"level21": {"level22": {"level23": {"level24": {"level25": {"level26": {"level27": {"level28": {"level29": {"level30": {"level31": {"level32": {"level33": {"level34": {"level35": {"level36": {"level37": {"level38": {"level39": {"level40": {"level41": {"level42": {"level43": {"level44": {"level45": "This is level 45"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}]]
CREATE TABLE t2 (val1 VARCHAR(10), val2 JSON);
INSERT INTO t2 VALUES('level0', @json_doc_obj_lev45_valid);
SELECT JSON_OBJECTAGG(val1, val2) FROM t2;
JSON_OBJECTAGG(val1, val2)
{"level0":{ "level1": { "level2": { "level3": { "level4": { "level5": { "level6": { "level7": { "level8": { "level9": { "level10": { "level11": { "level12": { "level13": { "level14": { "level15": { "level16": { "level17": { "level18": { "level19": { "level20": { "level21": { "level22": { "level23": { "level24": { "level25": { "level26": { "level27": { "level28": { "level29": { "level30": { "level31": { "level32": { "level33": { "level34": { "level35": { "level36": { "level37": { "level38": { "level39": { "level40": { "level41": { "level42": { "level43": { "level44": { "level45": "This is level 45" } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } }}
SELECT JSON_OVERLAPS(@json_doc_obj_lev45_valid,'{ "level44": { "level45": "This is level 45" } }');
JSON_OVERLAPS(@json_doc_obj_lev45_valid,'{ "level44": { "level45": "This is level 45" } }')
0
SELECT JSON_QUERY(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]');
JSON_QUERY(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]')
[45, 45, 45]
SELECT JSON_QUOTE(@json_doc_obj_lev45_valid);
JSON_QUOTE(@json_doc_obj_lev45_valid)
"{ \"level1\": { \"level2\": { \"level3\": { \"level4\": { \"level5\": { \"level6\": { \"level7\": { \"level8\": { \"level9\": { \"level10\": { \"level11\": { \"level12\": { \"level13\": { \"level14\": { \"level15\": { \"level16\": { \"level17\": { \"level18\": { \"level19\": { \"level20\": { \"level21\": { \"level22\": { \"level23\": { \"level24\": { \"level25\": { \"level26\": { \"level27\": { \"level28\": { \"level29\": { \"level30\": { \"level31\": { \"level32\": { \"level33\": { \"level34\": { \"level35\": { \"level36\": { \"level37\": { \"level38\": { \"level39\": { \"level40\": { \"level41\": { \"level42\": { \"level43\": { \"level44\": { \"level45\": \"This is level 45\" } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } }"
SELECT JSON_QUOTE(@json_doc_arr_lev45_valid);
JSON_QUOTE(@json_doc_arr_lev45_valid)
"[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]"
SET @json_removed= JSON_REMOVE(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39');
SET @json_removed= JSON_REMOVE(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]');
SELECT @json_removed;
@json_removed
[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
SET @json_schema= '{"type": "object", "properties": { "level1": { "type": "object", "properties": { "level2": { "type": "object", "properties": { "level3": { "type": "object", "properties": { "level4": { "type": "object", "properties": { "level5": { "type": "object", "properties": { "level6": { "type": "object", "properties": { "level7": { "type": "object", "properties": { "level8": { "type": "object", "properties": { "level9": { "type": "object", "properties": { "level10": { "type": "object", "properties": { "level11": { "type": "object", "properties": { "level12": { "type": "object", "properties": { "level13": { "type": "object", "properties": { "level14": { "type": "object", "properties": { "level15": { "type": "object", "properties": { "level16": { "type": "object", "properties": { "level17": { "type": "object", "properties": { "level18": { "type": "object", "properties": { "level19": { "type": "object", "properties": { "level20": { "type": "object", "properties": { "level21": { "type": "object", "properties": { "level22": { "type": "object", "properties": { "level23": { "type": "object", "properties": { "level24": { "type": "object", "properties": { "level25": { "type": "object", "properties": { "level26": { "type": "object", "properties": { "level27": { "type": "object", "properties": { "level28": { "type": "object", "properties": { "level29": { "type": "object", "properties": { "level30": { "type": "object", "properties": { "level31": { "type": "object", "properties": { "level32": { "type": "object", "properties": { "level33": { "type": "object", "properties": { "level34": { "type": "object", "properties": { "level35": { "type": "object", "properties": { "level36": { "type": "object", "properties": { "level37": { "type": "object", "properties": { "level38": { "type": "object", "properties": { "level39": { "type": "object", "properties": { "level40": { "type": "object", "properties": { "level41": { "type": "object", "properties": { "level42": { "type": "object", "properties": { "level43": { "type": "object", "properties": { "level44": { "type": "object", "properties": { "level45": { "type": "string", "const": "This is level 45" } }, "required": ["level45"] } }, "required": ["level44"] } }, "required": ["level43"] } }, "required": ["level42"] } }, "required": ["level41"] } }, "required": ["level40"] } }, "required": ["level39"] } }, "required": ["level38"] } }, "required": ["level37"] } }, "required": ["level36"] } }, "required": ["level35"] } }, "required": ["level34"] } }, "required": ["level33"] } }, "required": ["level32"] } }, "required": ["level31"] } }, "required": ["level30"] } }, "required": ["level29"] } }, "required": ["level28"] } }, "required": ["level27"] } }, "required": ["level26"] } }, "required": ["level25"] } }, "required": ["level24"] } }, "required": ["level23"] } }, "required": ["level22"] } }, "required": ["level21"] } }, "required": ["level20"] } }, "required": ["level19"] } }, "required": ["level18"] } }, "required": ["level17"] } }, "required": ["level16"] } }, "required": ["level15"] } }, "required": ["level14"] } }, "required": ["level13"] } }, "required": ["level12"] } }, "required": ["level11"] } }, "required": ["level10"] } }, "required": ["level9"] } }, "required": ["level8"] } }, "required": ["level7"] } }, "required": ["level6"] } }, "required": ["level5"] } }, "required": ["level4"] } }, "required": ["level3"] } }, "required": ["level2"] } }, "required": ["level1"] }';
SELECT JSON_SCHEMA_VALID(@json_schema, @json_doc_obj_lev45_valid) AS found_path;
found_path
1
SELECT JSON_SCHEMA_VALID(@json_schema, @json_doc_obj_lev45_valid2);
JSON_SCHEMA_VALID(@json_schema, @json_doc_obj_lev45_valid2)
NULL
SELECT JSON_SEARCH(@json_doc_obj_lev45_valid,'one', 'This is level 45');
JSON_SEARCH(@json_doc_obj_lev45_valid,'one', 'This is level 45')
"$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41.level42.level43.level44.level45"
SET @json_updated = JSON_SET(@json_doc_obj_lev45_valid,
'$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41',
'Updated value');
SELECT @json_updated;
@json_updated
{"level1": {"level2": {"level3": {"level4": {"level5": {"level6": {"level7": {"level8": {"level9": {"level10": {"level11": {"level12": {"level13": {"level14": {"level15": {"level16": {"level17": {"level18": {"level19": {"level20": {"level21": {"level22": {"level23": {"level24": {"level25": {"level26": {"level27": {"level28": {"level29": {"level30": {"level31": {"level32": {"level33": {"level34": {"level35": {"level36": {"level37": {"level38": {"level39": {"level40": {"level41": "Updated value"}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}
SELECT JSON_TYPE(@json_doc_obj_lev45_valid);
JSON_TYPE(@json_doc_obj_lev45_valid)
OBJECT
SELECT JSON_TYPE(@json_doc_arr_lev45_valid);
JSON_TYPE(@json_doc_arr_lev45_valid)
ARRAY
SELECT JSON_UNQUOTE(JSON_QUOTE(@json_doc_obj_lev45_valid));
JSON_UNQUOTE(JSON_QUOTE(@json_doc_obj_lev45_valid))
{ "level1": { "level2": { "level3": { "level4": { "level5": { "level6": { "level7": { "level8": { "level9": { "level10": { "level11": { "level12": { "level13": { "level14": { "level15": { "level16": { "level17": { "level18": { "level19": { "level20": { "level21": { "level22": { "level23": { "level24": { "level25": { "level26": { "level27": { "level28": { "level29": { "level30": { "level31": { "level32": { "level33": { "level34": { "level35": { "level36": { "level37": { "level38": { "level39": { "level40": { "level41": { "level42": { "level43": { "level44": { "level45": "This is level 45" } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } } }
SELECT JSON_UNQUOTE(JSON_QUOTE(@json_doc_arr_lev45_valid));
JSON_UNQUOTE(JSON_QUOTE(@json_doc_arr_lev45_valid))
[1, [2, [3, [4, [5, [6, [7, [8, [9, [10, [11, [12, [13, [14, [15, [16, [17, [18, [19, [20, [21, [22, [23, [24, [25, [26, [27, [28, [29, [30, [31, [32, [33, [34, [35, [36, [37, [38, [39, [40, [41, [42, [43, [44, [45, 45, 45]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]
SELECT JSON_VALID(@json_doc_obj_lev45_valid);
JSON_VALID(@json_doc_obj_lev45_valid)
1
SELECT JSON_VALID(@json_doc_arr_lev45_valid);
JSON_VALID(@json_doc_arr_lev45_valid)
1
SELECT JSON_VALUE(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28.level29.level30.level31.level32.level33.level34.level35.level36.level37.level38.level39.level40.level41.level42.level43.level44.level45');
JSON_VALUE(@json_doc_obj_lev45_valid, '$.level1.level2.level3.level4.level5.level6.level7.level8.level9.level10.level11.level12.level13.level14.level15.level16.level17.level18.level19.level20.level21.level22.level23.level24.level25.level26.level27.level28
This is level 45
SELECT JSON_VALUE(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]');
JSON_VALUE(@json_doc_arr_lev45_valid, '$[1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1][1]')
45
DROP TABLE t1 ,t2;
Reference in a new issue View git blame Copy permalink