Postgresql

從 jsonb 列中提取並組合多個值

  • March 19, 2020

我希望從 Postgres 中的 jsonb 列中提取多個值,並且遇到了一些值返回 null 的問題。

小提琴

設置架構:

create table jsonb_test (test jsonb);
insert into jsonb_test values('{
"title": "test",
"tags": [
   {"tag": 1},
   {"tag": 2}
]
}'::jsonb);

查詢我正在執行:

select jsonb_path_query(test, '$.title'::jsonpath) as title,
      jsonb_path_query(test, '$.tags.tag'::jsonpath) as tag
from jsonb_test

我正在尋找的結果應該是:

| title | tag |
|-------|-----|
| test  | 1   |
| test  | 2   |

我得到的是:

| title | tag |
|-------|-----|
| test  | 1   |
| null  | 2   |

對於查詢,我已經嘗試了所有我能想到的讓 null 消失的方法,從交叉連接中選擇第二組值而不是同一個表,將其聚合到正常數組中,然後使用 unnest 等,但是我似乎無法讓它工作,更重要的是,我不明白是什麼導致了第二個 null。

獲得我正在尋找的結果的最簡單方法是什麼(性能在這裡不會成為問題)?

jsonb_path_query()是一個集合返回函式。當將多個其中一個放在SELECT列表中時,這是預期的行為。看:

您似乎正在尋找一個CROSS JOIN

SELECT *
FROM       (SELECT jsonb_path_query(test, '$.title'::jsonpath) AS title FROM jsonb_test) a
CROSS JOIN (SELECT jsonb_path_query(test, '$.tags.tag'::jsonpath) AS tag FROM jsonb_test) b

或者,也許更優雅,使用兩個LATERAL連接:

SELECT a.title, b.tag
FROM   jsonb_test j
    , jsonb_path_query(j.test, '$.title'::jsonpath) a(title)
    , jsonb_path_query(j.test, '$.tags.tag'::jsonpath) b(tag);

db<>在這裡擺弄

實際上,“代理交叉連接”。看:

引用自:https://dba.stackexchange.com/questions/262243