| blob | d62d32241d3a3c2c2a0b71e5bc9d94d07fd084f6 |
1 -- JSON_TABLE
2 -- Should fail (JSON_TABLE can be used only in FROM clause)
3 SELECT JSON_TABLE('[]', '$');
4 ERROR: syntax error at or near "("
5 LINE 1: SELECT JSON_TABLE('[]', '$');
6 ^
7 -- Only allow EMPTY and ERROR for ON ERROR
8 SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') DEFAULT 1 ON ERROR);
9 ERROR: invalid ON ERROR behavior
10 LINE 1: ...BLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') DEFAULT 1 ...
11 ^
12 DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause.
13 SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') NULL ON ERROR);
14 ERROR: invalid ON ERROR behavior
15 LINE 1: ...BLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') NULL ON ER...
16 ^
17 DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause.
18 SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') EMPTY ON ERROR);
19 js2
20 -----
21 (0 rows)
23 SELECT * FROM JSON_TABLE('[]', 'strict $.a' COLUMNS (js2 int PATH '$') ERROR ON ERROR);
24 ERROR: jsonpath member accessor can only be applied to an object
25 -- Column and path names must be distinct
26 SELECT * FROM JSON_TABLE(jsonb'"1.23"', '$.a' as js2 COLUMNS (js2 int path '$'));
27 ERROR: duplicate JSON_TABLE column or path name: js2
28 LINE 1: ...M JSON_TABLE(jsonb'"1.23"', '$.a' as js2 COLUMNS (js2 int pa...
29 ^
30 -- Should fail (no columns)
31 SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
32 ERROR: syntax error at or near ")"
33 LINE 1: SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS ());
34 ^
35 SELECT * FROM JSON_TABLE (NULL::jsonb, '$' COLUMNS (v1 timestamp)) AS f (v1, v2);
36 ERROR: JSON_TABLE function has 1 columns available but 2 columns specified
37 --duplicated column name
38 SELECT * FROM JSON_TABLE(jsonb'"1.23"', '$.a' COLUMNS (js2 int path '$', js2 int path '$'));
39 ERROR: duplicate JSON_TABLE column or path name: js2
40 LINE 1: ...E(jsonb'"1.23"', '$.a' COLUMNS (js2 int path '$', js2 int pa...
41 ^
42 --return composite data type.
43 create type comp as (a int, b int);
44 SELECT * FROM JSON_TABLE(jsonb '{"rec": "(1,2)"}', '$' COLUMNS (id FOR ORDINALITY, comp comp path '$.rec' omit quotes)) jt;
45 id | comp
46 ----+-------
47 1 | (1,2)
48 (1 row)
50 drop type comp;
51 -- NULL => empty table
52 SELECT * FROM JSON_TABLE(NULL::jsonb, '$' COLUMNS (foo int)) bar;
53 foo
54 -----
55 (0 rows)
57 SELECT * FROM JSON_TABLE(jsonb'"1.23"', 'strict $.a' COLUMNS (js2 int PATH '$'));
58 js2
59 -----
60 (0 rows)
62 --
63 SELECT * FROM JSON_TABLE(jsonb '123', '$'
64 COLUMNS (item int PATH '$', foo int)) bar;
65 item | foo
66 ------+-----
67 123 |
68 (1 row)
70 -- JSON_TABLE: basic functionality
71 CREATE DOMAIN jsonb_test_domain AS text CHECK (value <> 'foo');
72 CREATE TEMP TABLE json_table_test (js) AS
73 (VALUES
74 ('1'),
75 ('[]'),
76 ('{}'),
77 ('[1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""]')
78 );
79 -- Regular "unformatted" columns
80 SELECT *
81 FROM json_table_test vals
82 LEFT OUTER JOIN
83 JSON_TABLE(
84 vals.js::jsonb, 'lax $[*]'
85 COLUMNS (
86 id FOR ORDINALITY,
87 "int" int PATH '$',
88 "text" text PATH '$',
89 "char(4)" char(4) PATH '$',
90 "bool" bool PATH '$',
91 "numeric" numeric PATH '$',
92 "domain" jsonb_test_domain PATH '$',
93 js json PATH '$',
94 jb jsonb PATH '$'
95 )
96 ) jt
97 ON true;
98 js | id | int | text | char(4) | bool | numeric | domain | js | jb
99 ---------------------------------------------------------------------------------------+----+-----+---------+---------+------+---------+---------+--------------+--------------
100 1 | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1
101 [] | | | | | | | | |
102 {} | 1 | | | | | | | {} | {}
103 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | t | 1 | 1 | 1 | 1
104 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | | 1.23 | 1.23 | | 1.23 | 1.23 | 1.23 | 1.23
105 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | 2 | 2 | 2 | | 2 | 2 | "2" | "2"
106 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | | aaaaaaa | | | | aaaaaaa | "aaaaaaa" | "aaaaaaa"
107 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | | foo | foo | | | | "foo" | "foo"
108 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | | | | | | | null | null
109 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | | f | f | f | | false | false | false
110 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | | t | t | t | | true | true | true
111 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | | | | | | | {"aaa": 123} | {"aaa": 123}
112 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | | [1,2] | | | | [1,2] | "[1,2]" | "[1,2]"
113 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | | "str" | | | | "str" | "\"str\"" | "\"str\""
114 (14 rows)
116 -- "formatted" columns
117 SELECT *
118 FROM json_table_test vals
119 LEFT OUTER JOIN
120 JSON_TABLE(
121 vals.js::jsonb, 'lax $[*]'
122 COLUMNS (
123 id FOR ORDINALITY,
124 jst text FORMAT JSON PATH '$',
125 jsc char(4) FORMAT JSON PATH '$',
126 jsv varchar(4) FORMAT JSON PATH '$',
127 jsb jsonb FORMAT JSON PATH '$',
128 jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES
129 )
130 ) jt
131 ON true;
132 js | id | jst | jsc | jsv | jsb | jsbq
133 ---------------------------------------------------------------------------------------+----+--------------+------+------+--------------+--------------
134 1 | 1 | 1 | 1 | 1 | 1 | 1
135 [] | | | | | |
136 {} | 1 | {} | {} | {} | {} | {}
137 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | 1 | 1 | 1 | 1 | 1
138 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | 1.23 | 1.23 | 1.23 | 1.23 | 1.23
139 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | "2" | "2" | "2" | "2" | 2
140 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | "aaaaaaa" | | | "aaaaaaa" |
141 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | "foo" | | | "foo" |
142 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | null | null | null | null | null
143 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | false | | | false | false
144 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | true | true | true | true | true
145 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | {"aaa": 123} | | | {"aaa": 123} | {"aaa": 123}
146 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | "[1,2]" | | | "[1,2]" | [1, 2]
147 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | "\"str\"" | | | "\"str\"" | "str"
148 (14 rows)
150 -- EXISTS columns
151 SELECT *
152 FROM json_table_test vals
153 LEFT OUTER JOIN
154 JSON_TABLE(
155 vals.js::jsonb, 'lax $[*]'
156 COLUMNS (
157 id FOR ORDINALITY,
158 exists1 bool EXISTS PATH '$.aaa',
159 exists2 int EXISTS PATH '$.aaa',
160 exists3 int EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR,
161 exists4 text EXISTS PATH 'strict $.aaa' FALSE ON ERROR
162 )
163 ) jt
164 ON true;
165 js | id | exists1 | exists2 | exists3 | exists4
166 ---------------------------------------------------------------------------------------+----+---------+---------+---------+---------
167 1 | 1 | f | 0 | | false
168 [] | | | | |
169 {} | 1 | f | 0 | | false
170 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | f | 0 | | false
171 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | f | 0 | | false
172 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | f | 0 | | false
173 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | f | 0 | | false
174 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | f | 0 | | false
175 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | f | 0 | | false
176 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | f | 0 | | false
177 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | f | 0 | | false
178 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | t | 1 | 1 | true
179 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | f | 0 | | false
180 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | f | 0 | | false
181 (14 rows)
183 -- Other miscellaneous checks
184 SELECT *
185 FROM json_table_test vals
186 LEFT OUTER JOIN
187 JSON_TABLE(
188 vals.js::jsonb, 'lax $[*]'
189 COLUMNS (
190 id FOR ORDINALITY,
191 aaa int, -- "aaa" has implicit path '$."aaa"'
192 aaa1 int PATH '$.aaa',
193 js2 json PATH '$',
194 jsb2w jsonb PATH '$' WITH WRAPPER,
195 jsb2q jsonb PATH '$' OMIT QUOTES,
196 ia int[] PATH '$',
197 ta text[] PATH '$',
198 jba jsonb[] PATH '$'
199 )
200 ) jt
201 ON true;
202 js | id | aaa | aaa1 | js2 | jsb2w | jsb2q | ia | ta | jba
203 ---------------------------------------------------------------------------------------+----+-----+------+--------------+----------------+--------------+----+----+-----
204 1 | 1 | | | 1 | [1] | 1 | | |
205 [] | | | | | | | | |
206 {} | 1 | | | {} | [{}] | {} | | |
207 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 1 | | | 1 | [1] | 1 | | |
208 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 2 | | | 1.23 | [1.23] | 1.23 | | |
209 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 3 | | | "2" | ["2"] | 2 | | |
210 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 4 | | | "aaaaaaa" | ["aaaaaaa"] | | | |
211 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 5 | | | "foo" | ["foo"] | | | |
212 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 6 | | | null | [null] | null | | |
213 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 7 | | | false | [false] | false | | |
214 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 8 | | | true | [true] | true | | |
215 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 9 | 123 | 123 | {"aaa": 123} | [{"aaa": 123}] | {"aaa": 123} | | |
216 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 10 | | | "[1,2]" | ["[1,2]"] | [1, 2] | | |
217 [1, 1.23, "2", "aaaaaaa", "foo", null, false, true, {"aaa": 123}, "[1,2]", "\"str\""] | 11 | | | "\"str\"" | ["\"str\""] | "str" | | |
218 (14 rows)
220 -- Test using casts in DEFAULT .. ON ERROR expression
221 SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
222 COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT '"foo1"'::jsonb::text ON EMPTY));
223 js1
224 --------
225 "foo1"
226 (1 row)
228 SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
229 COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON EMPTY));
230 ERROR: could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type
231 DETAIL: value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
232 SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
233 COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON EMPTY));
234 js1
235 ------
236 foo1
237 (1 row)
239 SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
240 COLUMNS (js1 jsonb_test_domain PATH '$.d1' DEFAULT 'foo2'::jsonb_test_domain ON ERROR));
241 js1
242 ------
243 foo2
244 (1 row)
246 SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
247 COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON EMPTY));
248 js1
249 -----
250 {1}
251 (1 row)
253 -- JSON_TABLE: Test backward parsing
254 CREATE VIEW jsonb_table_view2 AS
255 SELECT * FROM
256 JSON_TABLE(
257 jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
258 COLUMNS (
259 "int" int PATH '$',
260 "text" text PATH '$',
261 "char(4)" char(4) PATH '$',
262 "bool" bool PATH '$',
263 "numeric" numeric PATH '$',
264 "domain" jsonb_test_domain PATH '$'));
265 CREATE VIEW jsonb_table_view3 AS
266 SELECT * FROM
267 JSON_TABLE(
268 jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
269 COLUMNS (
270 js json PATH '$',
271 jb jsonb PATH '$',
272 jst text FORMAT JSON PATH '$',
273 jsc char(4) FORMAT JSON PATH '$',
274 jsv varchar(4) FORMAT JSON PATH '$'));
275 CREATE VIEW jsonb_table_view4 AS
276 SELECT * FROM
277 JSON_TABLE(
278 jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
279 COLUMNS (
280 jsb jsonb FORMAT JSON PATH '$',
281 jsbq jsonb FORMAT JSON PATH '$' OMIT QUOTES,
282 aaa int, -- implicit path '$."aaa"',
283 aaa1 int PATH '$.aaa'));
284 CREATE VIEW jsonb_table_view5 AS
285 SELECT * FROM
286 JSON_TABLE(
287 jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
288 COLUMNS (
289 exists1 bool EXISTS PATH '$.aaa',
290 exists2 int EXISTS PATH '$.aaa' TRUE ON ERROR,
291 exists3 text EXISTS PATH 'strict $.aaa' UNKNOWN ON ERROR));
292 CREATE VIEW jsonb_table_view6 AS
293 SELECT * FROM
294 JSON_TABLE(
295 jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
296 COLUMNS (
297 js2 json PATH '$',
298 jsb2w jsonb PATH '$' WITH WRAPPER,
299 jsb2q jsonb PATH '$' OMIT QUOTES,
300 ia int[] PATH '$',
301 ta text[] PATH '$',
302 jba jsonb[] PATH '$'));
303 \sv jsonb_table_view2
304 CREATE OR REPLACE VIEW public.jsonb_table_view2 AS
305 SELECT "int",
306 text,
307 "char(4)",
308 bool,
309 "numeric",
310 domain
311 FROM JSON_TABLE(
312 'null'::jsonb, '$[*]' AS json_table_path_0
313 PASSING
314 1 + 2 AS a,
315 '"foo"'::json AS "b c"
316 COLUMNS (
317 "int" integer PATH '$',
318 text text PATH '$',
319 "char(4)" character(4) PATH '$',
320 bool boolean PATH '$',
321 "numeric" numeric PATH '$',
322 domain jsonb_test_domain PATH '$'
323 )
324 )
325 \sv jsonb_table_view3
326 CREATE OR REPLACE VIEW public.jsonb_table_view3 AS
327 SELECT js,
328 jb,
329 jst,
330 jsc,
331 jsv
332 FROM JSON_TABLE(
333 'null'::jsonb, '$[*]' AS json_table_path_0
334 PASSING
335 1 + 2 AS a,
336 '"foo"'::json AS "b c"
337 COLUMNS (
338 js json PATH '$' WITHOUT WRAPPER KEEP QUOTES,
339 jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES,
340 jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES,
341 jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES,
342 jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES
343 )
344 )
345 \sv jsonb_table_view4
346 CREATE OR REPLACE VIEW public.jsonb_table_view4 AS
347 SELECT jsb,
348 jsbq,
349 aaa,
350 aaa1
351 FROM JSON_TABLE(
352 'null'::jsonb, '$[*]' AS json_table_path_0
353 PASSING
354 1 + 2 AS a,
355 '"foo"'::json AS "b c"
356 COLUMNS (
357 jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES,
358 jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES,
359 aaa integer PATH '$."aaa"',
360 aaa1 integer PATH '$."aaa"'
361 )
362 )
363 \sv jsonb_table_view5
364 CREATE OR REPLACE VIEW public.jsonb_table_view5 AS
365 SELECT exists1,
366 exists2,
367 exists3
368 FROM JSON_TABLE(
369 'null'::jsonb, '$[*]' AS json_table_path_0
370 PASSING
371 1 + 2 AS a,
372 '"foo"'::json AS "b c"
373 COLUMNS (
374 exists1 boolean EXISTS PATH '$."aaa"',
375 exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR,
376 exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR
377 )
378 )
379 \sv jsonb_table_view6
380 CREATE OR REPLACE VIEW public.jsonb_table_view6 AS
381 SELECT js2,
382 jsb2w,
383 jsb2q,
384 ia,
385 ta,
386 jba
387 FROM JSON_TABLE(
388 'null'::jsonb, '$[*]' AS json_table_path_0
389 PASSING
390 1 + 2 AS a,
391 '"foo"'::json AS "b c"
392 COLUMNS (
393 js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES,
394 jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES,
395 jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES,
396 ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES,
397 ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES,
398 jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES
399 )
400 )
401 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view2;
402 QUERY PLAN
403 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
404 Table Function Scan on "json_table"
405 Output: "json_table"."int", "json_table".text, "json_table"."char(4)", "json_table".bool, "json_table"."numeric", "json_table".domain
406 Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS ("int" integer PATH '$', text text PATH '$', "char(4)" character(4) PATH '$', bool boolean PATH '$', "numeric" numeric PATH '$', domain jsonb_test_domain PATH '$'))
407 (3 rows)
409 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view3;
410 QUERY PLAN
411 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
412 Table Function Scan on "json_table"
413 Output: "json_table".js, "json_table".jb, "json_table".jst, "json_table".jsc, "json_table".jsv
414 Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jst text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsc character(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsv character varying(4) FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES))
415 (3 rows)
417 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view4;
418 QUERY PLAN
419 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
420 Table Function Scan on "json_table"
421 Output: "json_table".jsb, "json_table".jsbq, "json_table".aaa, "json_table".aaa1
422 Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (jsb jsonb PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsbq jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, aaa integer PATH '$."aaa"', aaa1 integer PATH '$."aaa"'))
423 (3 rows)
425 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view5;
426 QUERY PLAN
427 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
428 Table Function Scan on "json_table"
429 Output: "json_table".exists1, "json_table".exists2, "json_table".exists3
430 Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (exists1 boolean EXISTS PATH '$."aaa"', exists2 integer EXISTS PATH '$."aaa"' TRUE ON ERROR, exists3 text EXISTS PATH 'strict $."aaa"' UNKNOWN ON ERROR))
431 (3 rows)
433 EXPLAIN (COSTS OFF, VERBOSE) SELECT * FROM jsonb_table_view6;
434 QUERY PLAN
435 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
436 Table Function Scan on "json_table"
437 Output: "json_table".js2, "json_table".jsb2w, "json_table".jsb2q, "json_table".ia, "json_table".ta, "json_table".jba
438 Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (js2 json PATH '$' WITHOUT WRAPPER KEEP QUOTES, jsb2w jsonb PATH '$' WITH UNCONDITIONAL WRAPPER KEEP QUOTES, jsb2q jsonb PATH '$' WITHOUT WRAPPER OMIT QUOTES, ia integer[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, ta text[] PATH '$' WITHOUT WRAPPER KEEP QUOTES, jba jsonb[] PATH '$' WITHOUT WRAPPER KEEP QUOTES))
439 (3 rows)
441 -- JSON_TABLE() with alias
442 EXPLAIN (COSTS OFF, VERBOSE)
443 SELECT * FROM
444 JSON_TABLE(
445 jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
446 COLUMNS (
447 id FOR ORDINALITY,
448 "int" int PATH '$',
449 "text" text PATH '$'
450 )) json_table_func;
451 QUERY PLAN
452 -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
453 Table Function Scan on "json_table" json_table_func
454 Output: id, "int", text
455 Table Function Call: JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '"foo"'::jsonb AS "b c" COLUMNS (id FOR ORDINALITY, "int" integer PATH '$', text text PATH '$'))
456 (3 rows)
458 EXPLAIN (COSTS OFF, FORMAT JSON, VERBOSE)
459 SELECT * FROM
460 JSON_TABLE(
461 jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
462 COLUMNS (
463 id FOR ORDINALITY,
464 "int" int PATH '$',
465 "text" text PATH '$'
466 )) json_table_func;
467 QUERY PLAN
468 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
469 [ +
470 { +
471 "Plan": { +
472 "Node Type": "Table Function Scan", +
473 "Parallel Aware": false, +
474 "Async Capable": false, +
475 "Table Function Name": "json_table", +
476 "Alias": "json_table_func", +
477 "Disabled": false, +
478 "Output": ["id", "\"int\"", "text"], +
479 "Table Function Call": "JSON_TABLE('null'::jsonb, '$[*]' AS json_table_path_0 PASSING 3 AS a, '\"foo\"'::jsonb AS \"b c\" COLUMNS (id FOR ORDINALITY, \"int\" integer PATH '$', text text PATH '$'))"+
480 } +
481 } +
482 ]
483 (1 row)
485 DROP VIEW jsonb_table_view2;
486 DROP VIEW jsonb_table_view3;
487 DROP VIEW jsonb_table_view4;
488 DROP VIEW jsonb_table_view5;
489 DROP VIEW jsonb_table_view6;
490 DROP DOMAIN jsonb_test_domain;
491 -- JSON_TABLE: only one FOR ORDINALITY columns allowed
492 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (id FOR ORDINALITY, id2 FOR ORDINALITY, a int PATH '$.a' ERROR ON EMPTY)) jt;
493 ERROR: only one FOR ORDINALITY column is allowed
494 LINE 1: ..._TABLE(jsonb '1', '$' COLUMNS (id FOR ORDINALITY, id2 FOR OR...
495 ^
496 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (id FOR ORDINALITY, a int PATH '$' ERROR ON EMPTY)) jt;
497 id | a
498 ----+---
499 1 | 1
500 (1 row)
502 -- JSON_TABLE: ON EMPTY/ON ERROR behavior
503 SELECT *
504 FROM
505 (VALUES ('1'), ('"err"')) vals(js),
506 JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$')) jt;
507 js | a
508 -------+---
509 1 | 1
510 "err" |
511 (2 rows)
513 SELECT *
514 FROM
515 (VALUES ('1'), ('"err"')) vals(js)
516 LEFT OUTER JOIN
517 JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
518 ON true;
519 ERROR: invalid input syntax for type integer: "err"
520 -- TABLE-level ERROR ON ERROR is not propagated to columns
521 SELECT *
522 FROM
523 (VALUES ('1'), ('"err"')) vals(js)
524 LEFT OUTER JOIN
525 JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
526 ON true;
527 ERROR: invalid input syntax for type integer: "err"
528 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
529 ERROR: no SQL/JSON item found for specified path of column "a"
530 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON ERROR) ERROR ON ERROR) jt;
531 ERROR: jsonpath member accessor can only be applied to an object
532 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
533 ERROR: no SQL/JSON item found for specified path of column "a"
534 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
535 a
536 ---
537 2
538 (1 row)
540 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
541 a
542 ---
543 2
544 (1 row)
546 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
547 a
548 ---
549 1
550 (1 row)
552 -- JSON_TABLE: EXISTS PATH types
553 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$.a' ERROR ON ERROR)); -- ok; can cast to int4
554 a
555 ---
556 0
557 (1 row)
559 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int4 EXISTS PATH '$' ERROR ON ERROR)); -- ok; can cast to int4
560 a
561 ---
562 1
563 (1 row)
565 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int2 EXISTS PATH '$.a'));
566 ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
567 DETAIL: invalid input syntax for type smallint: "false"
568 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int8 EXISTS PATH '$.a'));
569 ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
570 DETAIL: invalid input syntax for type bigint: "false"
571 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a float4 EXISTS PATH '$.a'));
572 ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
573 DETAIL: invalid input syntax for type real: "false"
574 -- Default FALSE (ON ERROR) doesn't fit char(3)
575 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a'));
576 ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
577 DETAIL: value too long for type character(3)
578 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(3) EXISTS PATH '$.a' ERROR ON ERROR));
579 ERROR: value too long for type character(3)
580 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a char(5) EXISTS PATH '$.a' ERROR ON ERROR));
581 a
582 -------
583 false
584 (1 row)
586 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a json EXISTS PATH '$.a'));
587 a
588 -------
589 false
590 (1 row)
592 SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a jsonb EXISTS PATH '$.a'));
593 a
594 -------
595 false
596 (1 row)
598 -- EXISTS PATH domain over int
599 CREATE DOMAIN dint4 AS int;
600 CREATE DOMAIN dint4_0 AS int CHECK (VALUE <> 0 );
601 SELECT a, a::bool FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a dint4 EXISTS PATH '$.a' ));
602 a | a
603 ---+---
604 0 | f
605 (1 row)
607 SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b'));
608 ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
609 DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check"
610 SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' ERROR ON ERROR));
611 ERROR: value for domain dint4_0 violates check constraint "dint4_0_check"
612 SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' FALSE ON ERROR));
613 ERROR: could not coerce ON ERROR expression (FALSE) to the RETURNING type
614 DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check"
615 SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' TRUE ON ERROR));
616 a | a
617 ---+---
618 1 | t
619 (1 row)
621 DROP DOMAIN dint4, dint4_0;
622 -- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns
623 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING));
624 item
625 ---------
626 "world"
627 (1 row)
629 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' OMIT QUOTES ON SCALAR STRING));
630 item
631 -------
632 world
633 (1 row)
635 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' KEEP QUOTES));
636 item
637 ---------
638 "world"
639 (1 row)
641 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' OMIT QUOTES));
642 item
643 -------
644 world
645 (1 row)
647 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITHOUT WRAPPER KEEP QUOTES));
648 item
649 ---------
650 "world"
651 (1 row)
653 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITHOUT WRAPPER OMIT QUOTES));
654 item
655 -------
656 world
657 (1 row)
659 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER));
660 item
661 -----------
662 ["world"]
663 (1 row)
665 -- Error: OMIT QUOTES should not be specified when WITH WRAPPER is present
666 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER OMIT QUOTES));
667 ERROR: SQL/JSON QUOTES behavior must not be specified when WITH WRAPPER is used
668 LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
669 ^
670 -- But KEEP QUOTES (the default) is fine
671 SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text FORMAT JSON PATH '$' WITH WRAPPER KEEP QUOTES));
672 item
673 -----------
674 ["world"]
675 (1 row)
677 -- Test PASSING args
678 SELECT *
679 FROM JSON_TABLE(
680 jsonb '[1,2,3]',
681 '$[*] ? (@ < $x)'
682 PASSING 3 AS x
683 COLUMNS (y text FORMAT JSON PATH '$')
684 ) jt;
685 y
686 ---
687 1
688 2
689 (2 rows)
691 -- PASSING arguments are also passed to column paths
692 SELECT *
693 FROM JSON_TABLE(
694 jsonb '[1,2,3]',
695 '$[*] ? (@ < $x)'
696 PASSING 10 AS x, 3 AS y
697 COLUMNS (a text FORMAT JSON PATH '$ ? (@ < $y)')
698 ) jt;
699 a
700 ---
701 1
702 2
704 (3 rows)
706 -- Should fail (not supported)
707 SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || 'a' COLUMNS (foo int));
708 ERROR: only string constants are supported in JSON_TABLE path specification
709 LINE 1: SELECT * FROM JSON_TABLE(jsonb '{"a": 123}', '$' || '.' || '...
710 ^
711 -- JsonPathQuery() error message mentioning column name
712 SELECT * FROM JSON_TABLE('{"a": [{"b": "1"}, {"b": "2"}]}', '$' COLUMNS (b json path '$.a[*].b' ERROR ON ERROR));
713 ERROR: JSON path expression for column "b" should return single item without wrapper
714 HINT: Use the WITH WRAPPER clause to wrap SQL/JSON items into an array.
715 -- JSON_TABLE: nested paths
716 -- Duplicate path names
717 SELECT * FROM JSON_TABLE(
718 jsonb '[]', '$' AS a
719 COLUMNS (
720 b int,
721 NESTED PATH '$' AS a
722 COLUMNS (
723 c int
724 )
725 )
726 ) jt;
727 ERROR: duplicate JSON_TABLE column or path name: a
728 LINE 5: NESTED PATH '$' AS a
729 ^
730 SELECT * FROM JSON_TABLE(
731 jsonb '[]', '$' AS a
732 COLUMNS (
733 b int,
734 NESTED PATH '$' AS n_a
735 COLUMNS (
736 c int
737 )
738 )
739 ) jt;
740 b | c
741 ---+---
742 |
743 (1 row)
745 SELECT * FROM JSON_TABLE(
746 jsonb '[]', '$'
747 COLUMNS (
748 b int,
749 NESTED PATH '$' AS b
750 COLUMNS (
751 c int
752 )
753 )
754 ) jt;
755 ERROR: duplicate JSON_TABLE column or path name: b
756 LINE 5: NESTED PATH '$' AS b
757 ^
758 SELECT * FROM JSON_TABLE(
759 jsonb '[]', '$'
760 COLUMNS (
761 NESTED PATH '$' AS a
762 COLUMNS (
763 b int
764 ),
765 NESTED PATH '$'
766 COLUMNS (
767 NESTED PATH '$' AS a
768 COLUMNS (
769 c int
770 )
771 )
772 )
773 ) jt;
774 ERROR: duplicate JSON_TABLE column or path name: a
775 LINE 10: NESTED PATH '$' AS a
776 ^
777 -- JSON_TABLE: plan execution
778 CREATE TEMP TABLE jsonb_table_test (js jsonb);
779 INSERT INTO jsonb_table_test
780 VALUES (
781 '[
782 {"a": 1, "b": [], "c": []},
783 {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
784 {"a": 3, "b": [1, 2], "c": []},
785 {"x": "4", "b": [1, 2], "c": 123}
786 ]'
787 );
788 select
789 jt.*
790 from
791 jsonb_table_test jtt,
792 json_table (
793 jtt.js,'strict $[*]' as p
794 columns (
795 n for ordinality,
796 a int path 'lax $.a' default -1 on empty,
797 nested path 'strict $.b[*]' as pb columns (b_id for ordinality, b int path '$' ),
798 nested path 'strict $.c[*]' as pc columns (c_id for ordinality, c int path '$' )
799 )
800 ) jt;
801 n | a | b_id | b | c_id | c
802 ---+----+------+---+------+----
803 1 | 1 | | | |
804 2 | 2 | 1 | 1 | |
805 2 | 2 | 2 | 2 | |
806 2 | 2 | 3 | 3 | |
807 2 | 2 | | | 1 | 10
808 2 | 2 | | | 2 |
809 2 | 2 | | | 3 | 20
810 3 | 3 | 1 | 1 | |
811 3 | 3 | 2 | 2 | |
812 4 | -1 | 1 | 1 | |
813 4 | -1 | 2 | 2 | |
814 (11 rows)
816 -- PASSING arguments are passed to nested paths and their columns' paths
817 SELECT *
818 FROM
819 generate_series(1, 3) x,
820 generate_series(1, 3) y,
821 JSON_TABLE(jsonb
822 '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
823 'strict $[*] ? (@[*] <= $x)'
824 PASSING x AS x, y AS y
825 COLUMNS (
826 y text FORMAT JSON PATH '$',
827 NESTED PATH 'strict $[*] ? (@ == $y)'
828 COLUMNS (
829 z int PATH '$'
830 )
831 )
832 ) jt;
833 x | y | y | z
834 ---+---+--------------+---
835 1 | 1 | [1, 2, 3] | 1
836 2 | 1 | [1, 2, 3] | 1
837 2 | 1 | [2, 3, 4, 5] |
838 3 | 1 | [1, 2, 3] | 1
839 3 | 1 | [2, 3, 4, 5] |
840 3 | 1 | [3, 4, 5, 6] |
841 1 | 2 | [1, 2, 3] | 2
842 2 | 2 | [1, 2, 3] | 2
843 2 | 2 | [2, 3, 4, 5] | 2
844 3 | 2 | [1, 2, 3] | 2
845 3 | 2 | [2, 3, 4, 5] | 2
846 3 | 2 | [3, 4, 5, 6] |
847 1 | 3 | [1, 2, 3] | 3
848 2 | 3 | [1, 2, 3] | 3
849 2 | 3 | [2, 3, 4, 5] | 3
850 3 | 3 | [1, 2, 3] | 3
851 3 | 3 | [2, 3, 4, 5] | 3
852 3 | 3 | [3, 4, 5, 6] | 3
853 (18 rows)
855 -- JSON_TABLE: Test backward parsing with nested paths
856 CREATE VIEW jsonb_table_view_nested AS
857 SELECT * FROM
858 JSON_TABLE(
859 jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
860 COLUMNS (
861 id FOR ORDINALITY,
862 NESTED PATH '$[1]' AS p1 COLUMNS (
863 a1 int,
864 NESTED PATH '$[*]' AS "p1 1" COLUMNS (
865 a11 text
866 ),
867 b1 text
868 ),
869 NESTED PATH '$[2]' AS p2 COLUMNS (
870 NESTED PATH '$[*]' AS "p2:1" COLUMNS (
871 a21 text
872 ),
873 NESTED PATH '$[*]' AS p22 COLUMNS (
874 a22 text
875 )
876 )
877 )
878 );
879 \sv jsonb_table_view_nested
880 CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS
881 SELECT id,
882 a1,
883 b1,
884 a11,
885 a21,
886 a22
887 FROM JSON_TABLE(
888 'null'::jsonb, '$[*]' AS json_table_path_0
889 PASSING
890 1 + 2 AS a,
891 '"foo"'::json AS "b c"
892 COLUMNS (
893 id FOR ORDINALITY,
894 NESTED PATH '$[1]' AS p1
895 COLUMNS (
896 a1 integer PATH '$."a1"',
897 b1 text PATH '$."b1"',
898 NESTED PATH '$[*]' AS "p1 1"
899 COLUMNS (
900 a11 text PATH '$."a11"'
901 )
902 ),
903 NESTED PATH '$[2]' AS p2
904 COLUMNS (
905 NESTED PATH '$[*]' AS "p2:1"
906 COLUMNS (
907 a21 text PATH '$."a21"'
908 ),
909 NESTED PATH '$[*]' AS p22
910 COLUMNS (
911 a22 text PATH '$."a22"'
912 )
913 )
914 )
915 )
916 DROP VIEW jsonb_table_view_nested;
917 CREATE TABLE s (js jsonb);
918 INSERT INTO s VALUES
919 ('{"a":{"za":[{"z1": [11,2222]},{"z21": [22, 234,2345]},{"z22": [32, 204,145]}]},"c": 3}'),
920 ('{"a":{"za":[{"z1": [21,4222]},{"z21": [32, 134,1345]}]},"c": 10}');
921 -- error
922 SELECT sub.* FROM s,
923 JSON_TABLE(js, '$' PASSING 32 AS x, 13 AS y COLUMNS (
924 xx int path '$.c',
925 NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' COLUMNS (z21 int path '$?(@ >= $"x")' ERROR ON ERROR))
926 )) sub;
927 xx | z21
928 ----+------
929 3 |
930 3 | 234
931 3 | 2345
932 10 | 32
933 10 | 134
934 10 | 1345
935 (6 rows)
937 -- Parent columns xx1, xx appear before NESTED ones
938 SELECT sub.* FROM s,
939 (VALUES (23)) x(x), generate_series(13, 13) y,
940 JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS (
941 NESTED PATH '$.a.za[2]' COLUMNS (
942 NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$')),
943 NESTED PATH '$.a.za[1]' columns (d int[] PATH '$.z21'),
944 NESTED PATH '$.a.za[0]' columns (NESTED PATH '$.z1[*]' as z1 COLUMNS (a int PATH '$')),
945 xx1 int PATH '$.c',
946 NESTED PATH '$.a.za[1]' columns (NESTED PATH '$.z21[*]' as z21 COLUMNS (b int PATH '$')),
947 xx int PATH '$.c'
948 )) sub;
949 xx1 | xx | c | d | a | b
950 -----+----+-----+---------------+------+------
951 3 | 3 | 32 | | |
952 3 | 3 | 204 | | |
953 3 | 3 | 145 | | |
954 3 | 3 | | {22,234,2345} | |
955 3 | 3 | | | 11 |
956 3 | 3 | | | 2222 |
957 3 | 3 | | | | 22
958 3 | 3 | | | | 234
959 3 | 3 | | | | 2345
960 10 | 10 | | {32,134,1345} | |
961 10 | 10 | | | 21 |
962 10 | 10 | | | 4222 |
963 10 | 10 | | | | 32
964 10 | 10 | | | | 134
965 10 | 10 | | | | 1345
966 (15 rows)
968 -- Test applying PASSING variables at different nesting levels
969 SELECT sub.* FROM s,
970 (VALUES (23)) x(x), generate_series(13, 13) y,
971 JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y COLUMNS (
972 xx1 int PATH '$.c',
973 NESTED PATH '$.a.za[0].z1[*]' COLUMNS (NESTED PATH '$ ?(@ >= ($"x" -2))' COLUMNS (a int PATH '$')),
974 NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' COLUMNS (b int PATH '$'))
975 )) sub;
976 xx1 | a | b
977 -----+------+------
978 3 | |
979 3 | 2222 |
980 3 | | 2222
981 10 | 21 |
982 10 | 4222 |
983 10 | | 21
984 10 | | 4222
985 (7 rows)
987 -- Test applying PASSING variable to paths all the levels
988 SELECT sub.* FROM s,
989 (VALUES (23)) x(x),
990 generate_series(13, 13) y,
991 JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
992 COLUMNS (
993 xx1 int PATH '$.c',
994 NESTED PATH '$.a.za[1]'
995 COLUMNS (NESTED PATH '$.z21[*]' COLUMNS (b int PATH '$')),
996 NESTED PATH '$.a.za[1] ? (@.z21[*] >= ($"x"-1))' COLUMNS
997 (NESTED PATH '$.z21[*] ? (@ >= ($"y" + 3))' as z22 COLUMNS (a int PATH '$ ? (@ >= ($"y" + 12))')),
998 NESTED PATH '$.a.za[1]' COLUMNS
999 (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (c int PATH '$ ? (@ > ($"x" +111))'))
1000 )) sub;
1001 xx1 | b | a | c
1002 -----+------+------+------
1003 3 | 22 | |
1004 3 | 234 | |
1005 3 | 2345 | |
1006 3 | | |
1007 3 | | 234 |
1008 3 | | 2345 |
1009 3 | | | 234
1010 3 | | | 2345
1011 10 | 32 | |
1012 10 | 134 | |
1013 10 | 1345 | |
1014 10 | | 32 |
1015 10 | | 134 |
1016 10 | | 1345 |
1017 10 | | |
1018 10 | | | 1345
1019 (16 rows)
1021 ----- test on empty behavior
1022 SELECT sub.* FROM s,
1023 (values(23)) x(x),
1024 generate_series(13, 13) y,
1025 JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
1026 COLUMNS (
1027 xx1 int PATH '$.c',
1028 NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$')),
1029 NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))'),
1030 NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int PATH '$')),
1031 NESTED PATH '$.a.za[1]' COLUMNS
1032 (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int PATH '$ ? (@ > ($"x" +111))' DEFAULT 0 ON EMPTY))
1033 )) sub;
1034 xx1 | c | d | a | b
1035 -----+-----+--------------------------+------+------
1036 3 | 32 | | |
1037 3 | 204 | | |
1038 3 | 145 | | |
1039 3 | | {"z21": [22, 234, 2345]} | |
1040 3 | | | 2222 |
1041 3 | | | | 234
1042 3 | | | | 2345
1043 10 | | | |
1044 10 | | | 21 |
1045 10 | | | 4222 |
1046 10 | | | | 0
1047 10 | | | | 1345
1048 (12 rows)
1050 CREATE OR REPLACE VIEW jsonb_table_view7 AS
1051 SELECT sub.* FROM s,
1052 (values(23)) x(x),
1053 generate_series(13, 13) y,
1054 JSON_TABLE(js, '$' AS c1 PASSING x AS x, y AS y
1055 COLUMNS (
1056 xx1 int PATH '$.c',
1057 NESTED PATH '$.a.za[2]' COLUMNS (NESTED PATH '$.z22[*]' as z22 COLUMNS (c int PATH '$' WITHOUT WRAPPER OMIT QUOTES)),
1058 NESTED PATH '$.a.za[1]' COLUMNS (d json PATH '$ ? (@.z21[*] == ($"x" -1))' WITH WRAPPER),
1059 NESTED PATH '$.a.za[0]' COLUMNS (NESTED PATH '$.z1[*] ? (@ >= ($"x" -2))' as z1 COLUMNS (a int PATH '$' KEEP QUOTES)),
1060 NESTED PATH '$.a.za[1]' COLUMNS
1061 (NESTED PATH '$.z21[*] ? (@ >= ($"y" +121))' as z21 COLUMNS (b int PATH '$ ? (@ > ($"x" +111))' DEFAULT 0 ON EMPTY))
1062 )) sub;
1063 \sv jsonb_table_view7
1064 CREATE OR REPLACE VIEW public.jsonb_table_view7 AS
1065 SELECT sub.xx1,
1066 sub.c,
1067 sub.d,
1068 sub.a,
1069 sub.b
1070 FROM s,
1071 ( VALUES (23)) x(x),
1072 generate_series(13, 13) y(y),
1073 LATERAL JSON_TABLE(
1074 s.js, '$' AS c1
1075 PASSING
1076 x.x AS x,
1077 y.y AS y
1078 COLUMNS (
1079 xx1 integer PATH '$."c"',
1080 NESTED PATH '$."a"."za"[2]' AS json_table_path_0
1081 COLUMNS (
1082 NESTED PATH '$."z22"[*]' AS z22
1083 COLUMNS (
1084 c integer PATH '$' WITHOUT WRAPPER OMIT QUOTES
1085 )
1086 ),
1087 NESTED PATH '$."a"."za"[1]' AS json_table_path_1
1088 COLUMNS (
1089 d json PATH '$?(@."z21"[*] == $"x" - 1)' WITH UNCONDITIONAL WRAPPER KEEP QUOTES
1090 ),
1091 NESTED PATH '$."a"."za"[0]' AS json_table_path_2
1092 COLUMNS (
1093 NESTED PATH '$."z1"[*]?(@ >= $"x" - 2)' AS z1
1094 COLUMNS (
1095 a integer PATH '$' WITHOUT WRAPPER KEEP QUOTES
1096 )
1097 ),
1098 NESTED PATH '$."a"."za"[1]' AS json_table_path_3
1099 COLUMNS (
1100 NESTED PATH '$."z21"[*]?(@ >= $"y" + 121)' AS z21
1101 COLUMNS (
1102 b integer PATH '$?(@ > $"x" + 111)' DEFAULT 0 ON EMPTY
1103 )
1104 )
1105 )
1106 ) sub
1107 DROP VIEW jsonb_table_view7;
1108 DROP TABLE s;
1109 -- Prevent ON EMPTY specification on EXISTS columns
1110 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on empty));
1111 ERROR: syntax error at or near "empty"
1112 LINE 1: ...sonb '1', '$' COLUMNS (a int exists empty object on empty));
1113 ^
1114 -- Test ON ERROR / EMPTY value validity for the function and column types;
1115 -- all fail
1116 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ERROR);
1117 ERROR: invalid ON ERROR behavior
1118 LINE 1: ... * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int) NULL ON ER...
1119 ^
1120 DETAIL: Only EMPTY [ ARRAY ] or ERROR is allowed in the top-level ON ERROR clause.
1121 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on empty));
1122 ERROR: invalid ON EMPTY behavior for column "a"
1123 LINE 1: ...T * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int true on em...
1124 ^
1125 DETAIL: Only ERROR, NULL, or DEFAULT expression is allowed in ON EMPTY for scalar columns.
1126 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on error));
1127 ERROR: invalid ON ERROR behavior for column "a"
1128 LINE 1: ...N_TABLE(jsonb '1', '$' COLUMNS (a int omit quotes true on er...
1129 ^
1130 DETAIL: Only ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for formatted columns.
1131 SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty object on error));
1132 ERROR: invalid ON ERROR behavior for column "a"
1133 LINE 1: ...M JSON_TABLE(jsonb '1', '$' COLUMNS (a int exists empty obje...
1134 ^
1135 DETAIL: Only ERROR, TRUE, FALSE, or UNKNOWN is allowed in ON ERROR for EXISTS columns.
1136 -- Test JSON_TABLE() column deparsing -- don't emit default ON ERROR / EMPTY
1137 -- behavior
1138 CREATE VIEW json_table_view8 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$'));
1139 \sv json_table_view8;
1140 CREATE OR REPLACE VIEW public.json_table_view8 AS
1141 SELECT a
1142 FROM JSON_TABLE(
1143 '"a"'::text, '$' AS json_table_path_0
1144 COLUMNS (
1145 a text PATH '$'
1146 )
1147 )
1148 CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') ERROR ON ERROR);
1149 \sv json_table_view9;
1150 CREATE OR REPLACE VIEW public.json_table_view9 AS
1151 SELECT a
1152 FROM JSON_TABLE(
1153 '"a"'::text, '$' AS json_table_path_0
1154 COLUMNS (
1155 a text PATH '$'
1156 ) ERROR ON ERROR
1157 )
1158 DROP VIEW json_table_view8, json_table_view9;
1159 -- Test JSON_TABLE() deparsing -- don't emit default ON ERROR behavior
1160 CREATE VIEW json_table_view8 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') EMPTY ON ERROR);
1161 \sv json_table_view8;
1162 CREATE OR REPLACE VIEW public.json_table_view8 AS
1163 SELECT a
1164 FROM JSON_TABLE(
1165 '"a"'::text, '$' AS json_table_path_0
1166 COLUMNS (
1167 a text PATH '$'
1168 )
1169 )
1170 CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') EMPTY ARRAY ON ERROR);
1171 \sv json_table_view9;
1172 CREATE OR REPLACE VIEW public.json_table_view9 AS
1173 SELECT a
1174 FROM JSON_TABLE(
1175 '"a"'::text, '$' AS json_table_path_0
1176 COLUMNS (
1177 a text PATH '$'
1178 )
1179 )
1180 DROP VIEW json_table_view8, json_table_view9;