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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
|
select * from json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt;
a
1
2
select * from JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
a b
1 11
1 111
2 22
2 222
SELECT * FROM JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'), NESTED PATH '$.b[*]' COLUMNS (c INT PATH '$') ) ) jt;
a b c
1 11 NULL
1 111 NULL
1 NULL 11
1 NULL 111
2 22 NULL
2 222 NULL
2 NULL 22
2 NULL 222
create table t1 (id varchar(5), json varchar(1024));
insert into t1 values ('j1', '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]');
insert into t1 values ('j2', '[{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}]');
select id, json, a from t1, json_table(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a')) as tt;
id json a
j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1
j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2
j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3
j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 4
j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 5
select * from t1, JSON_TABLE(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt;
id json js_id a l_js_id b
j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 1 1 11
j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 1 2 111
j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 2 1 22
j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 2 2 222
j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 1 3 1 11
j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 1 3 2 111
j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 2 4 1 22
j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 2 4 2 222
j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 5 1 22
j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 5 2 222
select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt;
ERROR 42S22: Unknown column 't1.no_field' in 'JSON_TABLE argument'
select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, a INT PATH '$'))) as jt;
ERROR 42S21: Duplicate column name 'a'
DROP TABLE t1;
create table t1 (item_name varchar(32), item_props varchar(1024));
insert into t1 values ('Laptop', '{"color": "black", "price": 1000}');
insert into t1 values ('Jeans', '{"color": "blue", "price": 50}');
select * from t1 left join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1;
item_name item_props color
Laptop {"color": "black", "price": 1000} black
Jeans {"color": "blue", "price": 50} blue
select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1;
ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions
DROP TABLE t1;
select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
a b
101 11
101 111
2 22
2 222
select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
a b
NULL 11
NULL 111
2 22
2 222
select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
a b
NULL 11
NULL 111
2 22
2 222
select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
a b
202 11
202 111
2 22
2 222
select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3} xx YY]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
ERROR HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at position 65
select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' error on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'.
select * from json_table('{"a":0}',"$" columns(a decimal(1,1) path '$.a')) foo;
a
0.0
connect con1,localhost,root,,;
select a from json_table('{"a":0}',"$" columns(a for ordinality)) foo;
a
1
connection default;
disconnect con1;
create database db;
use db;
create table t (a text);
insert into t values ('{"foo":"bar"}');
create user u@localhost;
grant select (a) on db.t to u@localhost;
connect con1,localhost,u,,db;
select a from t;
a
{"foo":"bar"}
select * from t, json_table(t.a, '$' columns(f varchar(20) path '$.foo')) as jt;
a f
{"foo":"bar"} bar
connection default;
disconnect con1;
drop user u@localhost;
drop database db;
use test;
create table t1 (
color varchar(32),
price int
);
insert into t1 values ("red", 100), ("blue", 50);
insert into t1 select * from t1;
insert into t1 select * from t1;
set optimizer_switch='firstmatch=off';
select * from
json_table('[{"color": "blue", "price": 50},
{"color": "red", "price": 100}]',
'$[*]' columns( color varchar(100) path '$.color',
price text path '$.price'
)
) as T
where
T.color in (select color from t1 where t1.price=T.price);
color price
blue 50
red 100
drop table t1;
select * from
json_table(' [ {"color": "blue", "sizes": [1,2,3,4], "prices" : [10,20]},
{"color": "red", "sizes": [10,11,12,13,14], "prices" : [100,200,300]} ]',
'$[*]' columns(
color varchar(4) path '$.color',
seq0 for ordinality,
nested path '$.sizes[*]'
columns (seq1 for ordinality,
size int path '$'),
nested path '$.prices[*]'
columns (seq2 for ordinality,
price int path '$')
)
) as T;
color seq0 seq1 size seq2 price
blue 1 1 1 NULL NULL
blue 1 2 2 NULL NULL
blue 1 3 3 NULL NULL
blue 1 4 4 NULL NULL
blue 1 NULL NULL 1 10
blue 1 NULL NULL 2 20
red 2 1 10 NULL NULL
red 2 2 11 NULL NULL
red 2 3 12 NULL NULL
red 2 4 13 NULL NULL
red 2 5 14 NULL NULL
red 2 NULL NULL 1 100
red 2 NULL NULL 2 200
red 2 NULL NULL 3 300
select * from json_table('[{"color": "blue", "price": 50},
{"color": "red", "price": 100},
{"color": "rojo", "price": 10.0},
{"color": "blanco", "price": 11.0}]',
'$[*]' columns( color varchar(100) path '$.color',
price text path '$.price', seq for ordinality)) as T order by color desc;
color price seq
rojo 10.0 3
red 100 2
blue 50 1
blanco 11.0 4
create view v as select * from json_table('{"as":"b", "x":123}',"$" columns(a varchar(8) path '$.a' default '-' on empty, x int path '$.x')) x;
select * from v;
a x
- 123
show create table v;
View Create View character_set_client collation_connection
v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `x`.`a` AS `a`,`x`.`x` AS `x` from JSON_TABLE('{"as":"b", "x":123}', "$" COLUMNS (`a` varchar(8) PATH "$.a" DEFAULT '-' ON EMPTY, `x` int(11) PATH "$.x")) x latin1 latin1_swedish_ci
drop view v;
select * from json_table('{"as":"b", "x":123}',
"$" columns(a varchar(8) path '$.a' default '-' on empty null on error null on empty, x int path '$.x')) x;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null on empty, x int path '$.x')) x' at line 2
select * from json_table('{"a":"foo","b":"bar"}', '$'
columns (v varchar(20) path '$.*')) as jt;
v
NULL
select * from json_table('{"a":"foo","b":"bar"}', '$'
columns (v varchar(20) path '$.*' default '-' on error)) as jt;
v
-
select * from json_table('{"b":"bar"}', '$'
columns (v varchar(20) path '$.*' default '-' on error)) as jt;
v
bar
create table t1 (a varchar(100));
insert into t1 values ('1');
select * from t1 as T, json_table(T.a, '$[*]' columns(color varchar(100) path '$.nonexistent', seq for ordinality)) as T;
ERROR 42000: Not unique table/alias: 'T'
drop table t1;
prepare s from 'select * from
json_table(?,
\'$[*]\' columns( color varchar(100) path \'$.color\',
price text path \'$.price\',
seq for ordinality)) as T
order by color desc; ';
execute s using '[{"color": "red", "price":1}, {"color":"brown", "price":2}]';
color price seq
red 1 1
brown 2 2
deallocate prepare s;
create view v2 as select * from json_table('[{"co\\\\lor": "blue", "price": 50}]', '$[*]' columns( color varchar(100) path '$.co\\\\lor') ) as T;
select * from v2;
color
blue
drop view v2;
#
# End of 10.5 tests
#
|