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

我已经尝试了建议here和here的查询.

这就是我的jsonb数据：

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

我想要一个sql查询返回：

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

和另一个返回数组的查询,以便我可以在我的应用程序中调用count并在其上循环以创建表单：

data
----------------------------------------------------------------------
 '[{"kind":"person","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

解决方法

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",... }

为了得到所有：

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",... },{"kind": "person",... }] | 2           | 4             | 3

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

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

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

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

有关：

> Index for finding an element in a JSON array
> Query for array elements inside JSON type
> Best way to get result count before LIMIT was applied

术语

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!