查询包含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":"person"

  JSON

  {"kind":"person"}

  数组元素（不是嵌套的JSON对象），并计数数组元素以及表行（每行可能有多个匹配的数组元素）。

解决方案

要获取第column _ 列中 包含合格

jsonb

segments

SELECT count(*)FROM   segments sWHERe  s.payload @> '[{"kind":"person"}]';

要获取 _ 所有合格的JSON数组元素_ （本身就是JSON对象）-加上元素的总数（可能同时大于上述总数）：

SELECt j.*FROM   segments sJOIN   LATERAL jsonb_array_elements(s.payload) j(elem) ON j.elem @> '{"kind":"person"}'WHERe  s.payload @> '[{"kind":"person"}]';

返回值：

元素-------------------------------------------------- ----------{“种类”：“人”，“限制”：“ 1”，“过滤条件”：“ 56”，...}

一次全部 获取：

SELECt j.*, count(*) OVER () AS ct_elem, s.ct_rowsFROM  (   SELECt payload, count(*) OVER () AS ct_rows   FROM   segments   WHERe  payload @> '[{"kind":"person"}]'   ) sJOIN   LATERAL jsonb_array_elements(s.payload) j(elem) ON j.elem @> '{"kind":"person"}';

返回（对于具有更多条目的表）：

元素| 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_rowsFROM   segments sJOIN   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 > 0WHERe  s.payload @> '[{"kind":"person"}]';

返回（对于具有更多条目的表）：

filter_payload | ct_elem_row | ct_elem_total | ct_rows-------------------------------------------------- --- + ------------- + --------------- + ---------[{“ kind”：“ person”，...}] | 1 | 4 | 3[{“ kind”：“ person”，...}] | 1 | 4 | 3[{“ kind”：“人”，...}，{“ kind”：“人”，...}] | 2 | 4 | 3

这将标识匹配的行，然后选择匹配的数组元素，并仅使用那些元素为每行构建一个数组。加计数。

为了获得最佳性能，您将具有如下的

jsonb_path_ops

CREATE INDEX segments_path_ops_gin_idx ON segments USING  gin (payload jsonb_path_ops);

（但是，更通用的索引可以处理更多不同的查询可能是一个更好的选择。）

术语

我们正在处理一个 包含 JSON数组的JSON对象，该对象保存为Postgres

jsonb