我使用Postgresql 9.5和Rails 5.我想查询下面显示的jsonb列,它包含一个
JSON对象数组,以返回包含{“kind”：“person”}的所有JSON数组元素,并执行计数.
我使用的sql显示在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","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数组”.
