在 postgresql 查询中扁平化 jsonb 对象的更好方法是什么?

Question

>我有一个包含 2 列的模拟表，包括一个嵌套的 jsonb 列

CREATE TABLE public.test
(
id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
testcol jsonb
)
INSERT INTO public.test (testcol) VALUES 
('[{"type": {"value": 1, "displayName": "flag1"}, "value": "10"}, {"type": {"value": 2, "displayName": "flag2"}, "value": "20"}, {"type": {"value": 3, "displayName": "flag3"}, "value": "30"}, {"type": {"value": 4, "displayName": "flag4"}}, {"type": {"value": 5, "displayName": "flag5"}}, {"type": {"value": 6, "displayName": "flag6"}, "value": "40"}]')
,('[{"type": {"value": "x", "displayName": "flag1"}, "value": "x10"}, {"type": {"value": "y", "displayName": "flag2"}, "value": "x20"}, {"type": {"value": "z", "displayName": "flag3"}, "value": "x30"}]')
;

所以我尝试返回一个值表，其中包含主表值，以及 jsonb 对象中的值。我可以通过以下查询实现它

SELECT  id ,jo1.obj->'value', jo2.obj->'value', jo3.obj->'value'
FROM    test
CROSS JOIN
LATERAL
(
SELECT  JSON_OBJECT_AGG(jt.key, jt.value) obj
FROM    JSONB_ARRAY_ELEMENTS(testcol) je
CROSS JOIN
LATERAL JSONB_EACH(je.value) jt
WHERE je@>'{ "type": { "displayName": "flag1"} }'
) jo1
CROSS JOIN
LATERAL
(
SELECT  JSON_OBJECT_AGG(jt.key, jt.value) obj
FROM    JSONB_ARRAY_ELEMENTS(testcol) je
CROSS JOIN
LATERAL JSONB_EACH(je.value) jt
WHERE je@>'{ "type": { "displayName": "flag2"} }'
) jo2
CROSS JOIN
LATERAL
(
SELECT  JSON_OBJECT_AGG(jt.key, jt.value) obj
FROM    JSONB_ARRAY_ELEMENTS(testcol) je
CROSS JOIN
LATERAL JSONB_EACH(je.value) jt
WHERE je@>'{ "type": { "displayName": "flag3"} }'
) jo3

我将处理大量数据，所以我想知道是否有更好的方法来获得相同的结果，因为这看起来像是大量内部查询，而且我不太确定这样做会影响性能。

Answer 1

这是枢轴问题的变体，您可以使用条件聚合：

select t.id,
max(j.data ->> 'value') filter (where j.data -> 'type' ->> 'displayName' = 'flag1') as flag1,
max(j.data ->> 'value') filter (where j.data -> 'type' ->> 'displayName' = 'flag2') as flag2,
max(j.data ->> 'value') filter (where j.data -> 'type' ->> 'displayName' = 'flag3') as flag3
from test t
cross join lateral jsonb_array_elements(t.testcol) as j(data)
group by t.id
order by t.id;

---

如果您已经在使用 Postgres 12，使用新的 JSON 路径函数会更容易：

select id,
jsonb_path_query(testcol, '$[*] ? (@.type.displayName == "flag1").value') as flag1,
jsonb_path_query(testcol, '$[*] ? (@.type.displayName == "flag2").value') as flag2,
jsonb_path_query(testcol, '$[*] ? (@.type.displayName == "flag2").value') as flag3
from test;

在线示例