PG15: JSON_TABLE

[obartunov]

Andrew Dunstan committed SQL_JSON - one of the most awaited part of remaining part of SQL/JSON standard, which our team in Postgres Professional were working since early 2017, the first version was presented in our talk at PGConf.eu - 2017. Kudos, Andrew, you helped us with committing jsonb and help now !

JSON_TABLE produces a relational view of jsonb, so it's possible to use the result of JSONB_TABLE in all normal relational operators, for example, in joins.

JSON_TABLE has several parameters:

• The JSON value on which to operate.
  
• An SQL/JSON path expression to specify zero or more rows. This row pattern path expression is intended to produce an SQL/JSON sequence, with one SQL/JSON item for each row of the output table.
  
• A COLUMNS clause to specify the schema of the output table. The COLUMNS can be nested. The path expressions used in COLUMNS specification decompose SQL/JSON item on columns.
  
• Optionally, JSON_TABLE can have PLAN clause, which specifies how to join nested columns.
  

I have a bit outdated SQL/JSON introduction, let's follow one JSON_TABLE example.





This picture illustrates jsonb below.

CREATE TABLE house(js) AS SELECT jsonb ' -- two-floors house
{
"lift": false,
"floor": [
{
"level": 1,
"apt": [
{"no": 1, "area": 40, "rooms": 1},
{"no": 2, "area": 80, "rooms": 3},
{"no": 3, "area": null, "rooms": 2}
]
},
{
"level": 2,
"apt": [
{"no": 4, "area": 100, "rooms": 3},
{"no": 5, "area": 60, "rooms": 2}
]
}
]
}

';

Now, let's create a relational view of this jsonb.

SELECT
jt.*
FROM
house,
JSON_TABLE(js, '$.floor[*]' COLUMNS (
level int,
NESTED PATH '$.apt[*]' COLUMNS (
no int,
area float,
rooms int
)
)) jt;

level | no | area | rooms
-------+----+--------+-------
1 | 1 | 40 | 1
1 | 2 | 80 | 3
1 | 3 | (null) | 2
2 | 4 | 100 | 3
2 | 5 | 60 | 2
(5 rows)

We can use the result of JSON_TABLE to join with table levels:

CREATE TABLE levels ( level, name ) AS
VALUES (1,'first floor'),(2,'second floor');

SELECT
levels.name, jt.*
FROM
house,
JSON_TABLE(js, '$.floor[*]' COLUMNS (
level int,
NESTED PATH '$.apt[*]' COLUMNS (
no int,
area float,
rooms int
)
)) jt, levels
WHERE jt.level = levels.level;
name | level | no | area | rooms
--------------+-------+----+--------+-------
second floor | 2 | 5 | 60 | 2
second floor | 2 | 4 | 100 | 3
first floor | 1 | 3 | (null) | 2
first floor | 1 | 2 | 80 | 3
first floor | 1 | 1 | 40 | 1
(5 rows)