我使用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)
和另一个查询以返回数组，以便我可以在我的应用程序中对其进行调用，还可以遍历它以创建表单：
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
假设此表定义：
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中包含合格值的行数：jsonbsegments
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"}]';
返回值：
元素
------------------------------------------------- -----------
{“ 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"}';
返回（对于具有更多条目的表）：
元素| ct_elem | ct_rows
-------------------------- + --------- + ---------
{“种类“：”人“，...} | 4 | 3
{“种类”：“人”，...} | 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
------------------------------------------------- ---- + ------------- + --------------- + ---------
[{“种类”： “人”，...}] | 1 | 4 | 3
[{“ kind”：“ person”，...}] | 1 | 4 | 3
[{“种类”：“人”，...}，{“种类”：“人”，...}] | 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数组”。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句