Postgresql
從 jsonb 列中提取並組合多個值
我希望從 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<>在這裡擺弄
實際上,“代理交叉連接”。看: