jsonb 查询_sql – 查询包含JSON对象数组的jsonb列

假设这个表定义：

CREATE TABLE segments (segments_id serial PRIMARY KEY, payload jsonb);

使用这样的JSON值：

INSERT INTO segments (payload)

VALUES ('[

{

"kind": "person",

"limit": "1",

"filter_term": "56",

"selected_attr": "customer",

"selected_operator": "less_than"

},

{

"kind": "email",

"filter_term": "marketer",

"selected_attr": "job_title",

"selected_operator": "equals"

}

]'

);

>您希望返回包含键/值对“kind”的JSON数组的元素：“person”(不是嵌套的JSON对象{“kind”：“person”}) – 并计算数组元素以及表行(每行可能有多个匹配的数组元素).

解决方案

要获取包含列段中符合条件的jsonb值的行数：

SELECT count(*)

FROM segments s

WHERE s.payload @> '[{"kind":"person"}]';

获取所有符合条件的JSON数组元素(本身就是JSON对象) – 加上元素的总数(可能同时大于上面的数量：

SELECT j.*

FROM segments s

JOIN LATERAL jsonb_array_elements(s.payload) j(elem) ON j.elem @> '{"kind":"person"}'

WHERE s.payload @> '[{"kind":"person"}]';

返回：

elem

------------------------------------------------------------

{"kind": "person", "limit": "1", "filter_term": "56", ... }

为了得到所有：

SELECT j.*, count(*) OVER () AS ct_elem, s.ct_rows

FROM (

SELECT payload, count(*) OVER () AS ct_rows

FROM segments

WHERE payload @> '[{"kind":"person"}]'

) s

JOIN LATERAL jsonb_array_elements(s.payload) j(elem) ON j.elem @> '{"kind":"person"}';

返回(对于包含更多条目的表)：

elem | ct_elem | ct_rows

--------------------------+---------+---------

{"kind": "person", ... } | 4 | 3

{"kind": "person", ... } | 4 | 3

...

但我认为你真的想要这个：

SELECT a.*

, 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"}]';

返回(对于包含更多条目的表)：

filtered_payload | ct_elem_row | ct_elem_total | ct_rows

-----------------------------------------------------+-------------+---------------+---------

[{"kind": "person", ... }] | 1 | 4 | 3

[{"kind": "person", ... }] | 1 | 4 | 3

[{"kind": "person", ... }, {"kind": "person", ... }] | 2 | 4 | 3

这标识了匹配的行,然后选择匹配的数组元素,并且每行只构建一个数组.加上重要.

为了获得最佳性能,您将拥有一个jsonb_path_ops GIN索引,如：

CREATE INDEX segments_path_ops_gin_idx ON segments

USING gin (payload jsonb_path_ops);

(但是,提供更多不同查询的更通用的索引可能是更好的选择.)

有关：

术语

我们正在处理一个包含JSON数组的JSON对象,保存为Postgres jsonb数据类型 – 简称为“JSON数组”,但不是“JSON数组”.