参考:查询包含 JSON 对象数组的 jsonb 列
begin;
CREATE TEMP TABLE segments (segments_id serial PRIMARY KEY, payload jsonb);
INSERT INTO segments (payload)
VALUES ('[{"kind": "person", "limit": "1"}, {"kind": "B", "filter_term": "fin"}]');
INSERT INTO segments (payload)
VALUES ('[{"kind": "person", "limit": "3"}, {"kind": "A", "filter_term": "abc"}]');
INSERT INTO segments (payload)
VALUES ('[{"kind": "person", "limit": "2"}, {"kind": "C", "filter_term": "def"}]');
commit;
CTE查询:
with a as (select jsonb_array_elements(s.payload) j from segments s)
SELECT json_agg(a.j) AS filtered_payload from a
where j @> '{"kind":"person"}';
返回: [{"kind": "person", "limit": "1"}, {"kind": "person", "limit": "3"}, {"kind": "person", "limit": "2"}]
此查询A:
SELECT a.filtered_payload,
a.ct_elem_row
, sum(ct_elem_row) OVER () AS ct_elem_total
, count(*) OVER () AS ct_rows
FROM segments s
JOIN LATERAL (
SELECT json_agg(j.elem) AS filtered_payload, count(*) AS ct_elem_row
FROM jsonb_array_elements(s.payload) j(elem)
WHERE j.elem @> '{"kind":"person"}'
) a ON ct_elem_row > 0
WHERE s.payload @> '[{"kind":"person"}]';
return :
- 在QueryA中,结构如下:
select ... from segments s join lateral filtered_payload....
segments
是 3 行横向连接与一行 (filtered_payload)。filtered_payload
根据 CTE 查询仅返回行,作为合并 JSON 数组。所以总的来说我很困惑json_agg
在查询A中。
2021-10-05 16:36 +5:30 编辑:
即使下面的代码,a.filtered_payload
返回 3 个 jsonb 数组,而不是 1 个 arrgregate json 数组。我不知道什么时候已经聚合的 jsonb 数组(使用 json_agg 函数)取消嵌套到多个 jsonb 数组。
SELECT a.filtered_payload, s.*
FROM segments s
cross JOIN LATERAL (
SELECT json_agg(j.elem) AS filtered_payload
FROM jsonb_array_elements(s.payload) j(elem)
WHERE j.elem @> '{"kind":"person"}') a;