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

Question

我使用 PostgreSQL 9.5
和Rails5。我想查询jsonb下面显示的包含JSON对象数组的列，以返回包含{"kind":"person"}并执行计数的所有JSON数组元素。我使用
的 SQL 显示在json数据下方。运行查询只会返回一个空数组。

这是我的jsonb数据：

   '[
        {"kind":"person", "filter_term":"56","selected_attr":"customer"},
        {"kind":"email", "filter_term":"marketer","selected_attr":"job_title"}
      ]'

我想要其中一个sql查询返回：

                             data
----------------------------------------------------------------------
 '{"kind":"person", "filter_term":"56","selected_attr":"customer"}'
(1 row)

和另一个查询以返回数组，以便我可以在应用程序中对其调用count，还可以遍历它以创建表单：

 data
----------------------------------------------------------------------
 '[{"kind":"person", "filter_term":"56","selected_attr":"customer"}]'
 (1 row)

我试过这个SQL查询：

 "SELECT * FROM \"segments\" WHERE (payload @> '[{\"kind\":\"person\"}]')"

我也试过这个查询：

  "SELECT payload FROM segments WHERE payload @> '[{\"kind\":\"person\"}]'::jsonb;"

这是第三个查询：

 "SELECT * FROM segments s WHERE s.payload->'\"#{a}\"' @> '[{\"kind\":\"person\"}]';"

该模型：

class Segment < ApplicationRecord
 store_accessor :payload,:kind, :filter_term, :selected_model_name, :selected_attr, :limit, :selected_operator
end

迁移：

create_table "segments", force: :cascade do |t|

  t.jsonb    "payload",    default: "[]", null: false
  t.index ["payload"], name: "index_segments_on_payload", using: :gin

end

Answer 1

假设此表定义：

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

返回值：

元素
-------------------------------------------------- ----------
{“种类”：“人”，“限制”：“ 1”，“过滤条件”：“ 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"}';

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

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

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

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_opsGIN索引：

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

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

术语

我们正在处理一个 包含 JSON数组的JSON对象，该对象保存为Postgresjsonb数据类型-简称为“ JSON数组”，而 不是 “JSON数组”。