作者
digoal
日期
2022-04-08
标签
PostgreSQL , json_table
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4e34747c88a03ede6e9d731727815e37273d4bc9
JSON_TABLE
author Andrew Dunstan <andrew@dunslane.net>
Mon, 4 Apr 2022 19:36:03 +0000 (15:36 -0400)
committer Andrew Dunstan <andrew@dunslane.net>
Mon, 4 Apr 2022 20:03:47 +0000 (16:03 -0400)
commit 4e34747c88a03ede6e9d731727815e37273d4bc9
tree c7318a224b908c5dbaba3198324c90ec5429c3a5 tree
parent c42a6fc41dc22b42e5417224440c02893996afb4 commit | diff
JSON_TABLE
This feature allows jsonb data to be treated as a table and thus used in
a FROM clause like other tabular data. Data can be selected from the
jsonb using jsonpath expressions, and hoisted out of nested structures
in the jsonb to form multiple rows, more or less like an outer join.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fadb48b00e02ccfd152baa80942de30205ab3c4f
PLAN clauses for JSON_TABLE
These clauses allow the user to specify how data from nested paths are
joined, allowing considerable freedom in shaping the tabular output of
JSON_TABLE.
PLAN DEFAULT allows the user to specify the global strategies when
dealing with sibling or child nested paths. The is often sufficient to
achieve the necessary goal, and is considerably simpler than the full
PLAN clause, which allows the user to specify the strategy to be used
for each named nested path.
使用例子参考
https://www.depesz.com/2022/04/06/waiting-for-postgresql-15-json_table/
This time something that allows getting data from complicated jsons into recordset.
Let's start with some simple example:
=$ select * from json_table(
'[{"a":10,"b":20},{"a":30,"b":40}]'::jsonb,
'$[*]'
COLUMNS (
column_a int4 path '$.a',
column_b int4 path '$.b'
)
);
column_a | column_b
----------+----------
10 | 20
30 | 40
(2 rows)
I assume the example is easy to understand.
Now, let's assume we want to add serial-like column, named id. Also, I'll show two more tricks:
=$ select * from json_table(
'[{"a":10,"b":20},{"a":30,"b":40}]'::jsonb,
'$[*]'
COLUMNS (
id FOR ORDINALITY,
column_a int4 path '$.a',
column_b int4 path '$.b',
a int4,
b int4,
c text
)
);
id | column_a | column_b | a | b | c
----+----------+----------+----+----+---
1 | 10 | 20 | 10 | 20 |
2 | 30 | 40 | 30 | 40 |
(2 rows)
Adding serial-like id worked. And please note that i also added columns a and b – without specifying their paths. In case path is simply ‘$.SOMETHING' and you want the column to be named SOMETHING you don't need to explicitly state paths.
If there is no such field in the json, it will be returned as null.
Now, pretty commonly one has nested structures. For example, we could have json like this:
=$ select jsonb_pretty(j) from sample;
jsonb_pretty
-----------------------------------------
[ +
{ +
"title": "first post", +
"author": "depesz", +
"comments": [ +
{ +
"body": "comment #1", +
"author": "hubert" +
}, +
{ +
"body": "comment #3", +
"author": "lubaczewski"+
}, +
{ +
"body": "comment #5", +
"author": "someone" +
} +
] +
}, +
{ +
"title": "second post", +
"author": "depesz", +
"comments": [ +
{ +
"body": "comment #2", +
"author": "depesz" +
}, +
{ +
"body": "comment #6", +
"author": "anyone" +
} +
] +
}, +
{ +
"title": "third post", +
"author": "someone else", +
"comments": [ +
{ +
"body": "comment #4", +
"author": "whoever" +
} +
] +
} +
]
(1 row)
Single row, nested structure.
With json_table I can:
=$ select jt.* from sample,
lateral json_table(
j,
'$[*]'
COLUMNS (
id FOR ORDINALITY,
author text,
title text,
NESTED PATH '$.comments[*]'
COLUMNS (
comment_author text PATH '$.author',
comment_body text PATH '$.body'
)
)
) as jt;
id | author | title | comment_author | comment_body
----+--------------+-------------+----------------+--------------
1 | depesz | first post | hubert | comment #1
1 | depesz | first post | lubaczewski | comment #3
1 | depesz | first post | someone | comment #5
2 | depesz | second post | depesz | comment #2
2 | depesz | second post | anyone | comment #6
3 | someone else | third post | whoever | comment #4
(6 rows)
There are, as previously many additional options:
default clauses for fields
exists clauses that return boolean or int depending on jsonpath existing in given place
format specifier (json/jsonb) that allows extraction value from json, as json, even if datatype used is, for example, text
with wrapper, somehow related to making multiple things into array
omit quotes, which, to be fair, I don't understand
https://obartunov.livejournal.com/206765.html
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)
https://www.postgresql.org/docs/devel/functions-json.html
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=src/test/regress/expected/jsonb_sqljson.out;h=144cc0c557854ce87d59613ea9f33982e71edafe;hp=3661b7a810bf71d9ebcd51e2dbbd817d656cf23d;hb=4e34747c88a03ede6e9d731727815e37273d4bc9;hpb=c42a6fc41dc22b42e5417224440c02893996afb4
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=doc/src/sgml/func.sgml;h=569c78e792ad388d1581d752b974ee3742efc54b;hp=2f7aff9f216ad988b2f8f63ec2586a2925f6d8b4;hb=a6baa4baddd5f111bc59d8f5ed5cadbb2d91e98d;hpb=efb0ef909f605817da6b77c1f3fef0a24457ec16
期望 PostgreSQL 增加什么功能?
PolarDB for PostgreSQL云原生分布式开源数据库
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
