digoal
2024-04-09
PostgreSQL , PolarDB , DuckDB , json_table , nested path
JSON_TABLE: Add support for NESTED paths and columns
JSON_TABLE: Add support for NESTED paths and columns
author Amit Langote <amitlan@postgresql.org>
Mon, 8 Apr 2024 06:58:58 +0000 (15:58 +0900)
committer Amit Langote <amitlan@postgresql.org>
Mon, 8 Apr 2024 07:14:13 +0000 (16:14 +0900)
commit bb766cde63b4f624d029b34c9cdd3d0a94fd5b46
tree 7637d09824843064c6c1c0cb0bc8504d7e887718 tree
parent f6a2529920cff76cb6e37ea840122574404dde8b commit | diff
JSON_TABLE: Add support for NESTED paths and columns
A NESTED path allows to extract data from nested levels of JSON
objects given by the parent path expression, which are projected as
columns specified using a nested COLUMNS clause, just like the parent
COLUMNS clause. Rows comprised from a NESTED columns are "joined"
to the row comprised from the parent columns. If a particular NESTED
path evaluates to 0 rows, then the nested COLUMNS will emit NULLs,
making it an OUTER join.
NESTED columns themselves may include NESTED paths to allow
extracting data from arbitrary nesting levels, which are likewise
joined against the rows at the parent level.
Multiple NESTED paths at a given level are called "sibling" paths
and their rows are combined by UNIONing them, that is, after being
joined against the parent row as described above.
Author: Nikita Glukhov <n.gluhov@postgrespro.ru>
Author: Teodor Sigaev <teodor@sigaev.ru>
Author: Oleg Bartunov <obartunov@gmail.com>
Author: Alexander Korotkov <aekorotkov@gmail.com>
Author: Andrew Dunstan <andrew@dunslane.net>
Author: Amit Langote <amitlangote09@gmail.com>
Author: Jian He <jian.universality@gmail.com>
Reviewers have included (in no particular order):
Andres Freund, Alexander Korotkov, Pavel Stehule, Andrew Alsup,
Erik Rijkers, Zihong Yu, Himanshu Upadhyaya, Daniel Gustafsson,
Justin Pryzby, Álvaro Herrera, Jian He
Discussion: https://postgr.es/m/cd0bb935-0158-78a7-08b5-904886deac4b@postgrespro.ru
Discussion: https://postgr.es/m/20220616233130.rparivafipt6doj3@alap3.anarazel.de
Discussion: https://postgr.es/m/abd9b83b-aa66-f230-3d6d-734817f0995d%40postgresql.org
Discussion: https://postgr.es/m/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail.gmail.com
例子
+-- JSON_TABLE: plan execution
+CREATE TEMP TABLE jsonb_table_test (js jsonb);
+INSERT INTO jsonb_table_test
+VALUES (
+ '[
+ {"a": 1, "b": [], "c": []},
+ {"a": 2, "b": [1, 2, 3], "c": [10, null, 20]},
+ {"a": 3, "b": [1, 2], "c": []},
+ {"x": "4", "b": [1, 2], "c": 123}
+ ]'
+);
+select
+ jt.*
+from
+ jsonb_table_test jtt,
+ json_table (
+ jtt.js,'strict $[*]' as p
+ columns (
+ n for ordinality,
+ a int path 'lax $.a' default -1 on empty,
+ nested path 'strict $.b[*]' as pb columns (b_id for ordinality, b int path '$' ),
+ nested path 'strict $.c[*]' as pc columns (c_id for ordinality, c int path '$' )
+ )
+ ) jt;
+ n | a | b_id | b | c_id | c
+---+----+------+---+------+----
+ 1 | 1 | | | |
+ 2 | 2 | 1 | 1 | |
+ 2 | 2 | 2 | 2 | |
+ 2 | 2 | 3 | 3 | |
+ 2 | 2 | | | 1 | 10
+ 2 | 2 | | | 2 |
+ 2 | 2 | | | 3 | 20
+ 3 | 3 | 1 | 1 | |
+ 3 | 3 | 2 | 2 | |
+ 4 | -1 | 1 | 1 | |
+ 4 | -1 | 2 | 2 | |
+(11 rows)
+
+-- PASSING arguments are passed to nested paths and their columns' paths
+SELECT *
+FROM
+ generate_series(1, 3) x,
+ generate_series(1, 3) y,
+ JSON_TABLE(jsonb
+ '[[1,2,3],[2,3,4,5],[3,4,5,6]]',
+ 'strict $[*] ? (@[*] <= $x)'
+ PASSING x AS x, y AS y
+ COLUMNS (
+ y text FORMAT JSON PATH '$',
+ NESTED PATH 'strict $[*] ? (@ == $y)'
+ COLUMNS (
+ z int PATH '$'
+ )
+ )
+ ) jt;
+ x | y | y | z
+---+---+--------------+---
+ 1 | 1 | [1, 2, 3] | 1
+ 2 | 1 | [1, 2, 3] | 1
+ 2 | 1 | [2, 3, 4, 5] |
+ 3 | 1 | [1, 2, 3] | 1
+ 3 | 1 | [2, 3, 4, 5] |
+ 3 | 1 | [3, 4, 5, 6] |
+ 1 | 2 | [1, 2, 3] | 2
+ 2 | 2 | [1, 2, 3] | 2
+ 2 | 2 | [2, 3, 4, 5] | 2
+ 3 | 2 | [1, 2, 3] | 2
+ 3 | 2 | [2, 3, 4, 5] | 2
+ 3 | 2 | [3, 4, 5, 6] |
+ 1 | 3 | [1, 2, 3] | 3
+ 2 | 3 | [1, 2, 3] | 3
+ 2 | 3 | [2, 3, 4, 5] | 3
+ 3 | 3 | [1, 2, 3] | 3
+ 3 | 3 | [2, 3, 4, 5] | 3
+ 3 | 3 | [3, 4, 5, 6] | 3
+(18 rows)
+
+-- JSON_TABLE: Test backward parsing with nested paths
+CREATE VIEW jsonb_table_view_nested AS
+SELECT * FROM
+ JSON_TABLE(
+ jsonb 'null', 'lax $[*]' PASSING 1 + 2 AS a, json '"foo"' AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ NESTED PATH '$[1]' AS p1 COLUMNS (
+ a1 int,
+ NESTED PATH '$[*]' AS "p1 1" COLUMNS (
+ a11 text
+ ),
+ b1 text
+ ),
+ NESTED PATH '$[2]' AS p2 COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1" COLUMNS (
+ a21 text
+ ),
+ NESTED PATH '$[*]' AS p22 COLUMNS (
+ a22 text
+ )
+ )
+ )
+ );
+\sv jsonb_table_view_nested
+CREATE OR REPLACE VIEW public.jsonb_table_view_nested AS
+ SELECT id,
+ a1,
+ b1,
+ a11,
+ a21,
+ a22
+ FROM JSON_TABLE(
+ 'null'::jsonb, '$[*]' AS json_table_path_0
+ PASSING
+ 1 + 2 AS a,
+ '"foo"'::json AS "b c"
+ COLUMNS (
+ id FOR ORDINALITY,
+ NESTED PATH '$[1]' AS p1
+ COLUMNS (
+ a1 integer PATH '$."a1"',
+ b1 text PATH '$."b1"',
+ NESTED PATH '$[*]' AS "p1 1"
+ COLUMNS (
+ a11 text PATH '$."a11"'
+ )
+ ),
+ NESTED PATH '$[2]' AS p2
+ COLUMNS (
+ NESTED PATH '$[*]' AS "p2:1"
+ COLUMNS (
+ a21 text PATH '$."a21"'
+ ),
+ NESTED PATH '$[*]' AS p22
+ COLUMNS (
+ a22 text PATH '$."a22"'
+ )
+ )
+ )
+ )
