使用 JSONB 加入 PostgreSQL

我有這個 SQL：

CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB);

INSERT INTO test(data) VALUES
  ('{"parent":null,"children":[2,3]}'),
  ('{"parent":1,   "children":[4,5]}'),
  ('{"parent":1,   "children":[]}'),
  ('{"parent":2,   "children":[]}'),
  ('{"parent":2,   "children":[]}');

那將給出：

id |                 data                 
----+--------------------------------------
 1 | {"parent": null, "children": [2, 3]}
 2 | {"parent": 1, "children": [4, 5]}
 3 | {"parent": 1, "children": []}
 4 | {"parent": 2, "children": []}
 5 | {"parent": 2, "children": []}

當進行正常的一對多時，它會顯示如下內容：

SELECT * 
FROM test x1
 LEFT JOIN test x2
   ON x1.id = (x2.data->>'parent')::INT;
id |                 data                 | id |               data                
----+--------------------------------------+----+-----------------------------------
 1 | {"parent": null, "children": [2, 3]} |  2 | {"parent": 1, "children": [4, 5]}
 1 | {"parent": null, "children": [2, 3]} |  3 | {"parent": 1, "children": []}
 2 | {"parent": 1, "children": [4, 5]}    |  4 | {"parent": 2, "children": []}
 2 | {"parent": 1, "children": [4, 5]}    |  5 | {"parent": 2, "children": []}
 5 | {"parent": 2, "children": []}        |    | 
 4 | {"parent": 2, "children": []}        |    | 
 3 | {"parent": 1, "children": []}        |    | 

如何根據孩子加入（使用LEFT JOINor WHERE IN）？我試過了：

SELECT data->>'children' FROM test;
?column? 
----------
[2, 3]
[4, 5]
[]
[]
[]

SELECT json_array_elements((data->>'children')::TEXT) FROM t...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT json_array_elements((data->>'children')::JSONB) FROM ...
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

SELECT json_to_record((data->>'children')::JSON) FROM test;
ERROR:  function returning record called in context that cannot accept type record
HINT:  Try calling the function in the FROM clause using a column definition list.

SELECT * FROM json_to_record((test.data->>'children')::JSON);
ERROR:  missing FROM-clause entry for table "test"
LINE 1: SELECT * FROM json_to_record((test.data->>'children')::JSON)...

這會更有效率：

在pg 9.4+jsonb中jsonb_array_elements_text()

EXPLAIN 
SELECT p.id AS p_id, p.data
    , c.id AS c_id, c.data
FROM   test p
**LEFT JOIN LATERAL jsonb_array_elements_text(p.data->'children') pc(child) ON TRUE**
LEFT   JOIN test c ON c.id = pc.child::int;

db<>在這裡擺弄

關於jsonb_array_elements_text()：

• 如何將 JSON 數組轉換為 Postgres 數組？

使用->運算符而不是->>在對 的引用中children。按照您的方式，您首先將json/jsonb 轉換為text然後返回到json.

呼叫集合返回函式的簡潔方法是LEFT [OUTER] JOIN LATERAL. 這包括沒有孩子的行。要排除這些，請更改為[INNER] JOIN LATERALor CROSS JOIN- 或帶有逗號的速記語法：

, json_array_elements(p.data->'children') pc(child)

避免結果中出現重複的列名。

在第 9.3 頁json中json_array_elements()

SELECT p.id AS p_id, p.data AS p_data
    , c.id AS c_id, c.data AS c_data
FROM   test p
LEFT   JOIN LATERAL json_array_elements(p.data->'children') pc(child) ON TRUE
LEFT   JOIN test c ON c.id = pc.child::text::int;

老sqlfiddle

旁白：具有基本數據類型的規範化數據庫設計會更有效。

引用自：https://dba.stackexchange.com/questions/83932