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

Question

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

sqlruby-on-railspostgresqlrails-activerecordjsonb

8

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

我尝试了此处和此处建议的查询。

这是我的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

- brg

=>是hstore的有效语法，但不适用于json（或jsonb）。一个有效的JSON字面量：'{"kind":"person"}'。请修复您的示例并添加您期望的结果。 "所有的JSON"有点模糊。还有一个表行，还有数据类型json，但是什么是“json行”？您是指JSON数组元素吗？ - Erwin Brandstetter

非常感谢您的帮助。是的，我的意思是JSON数组元素。我已经重新表述了问题，希望更清楚明白了。 - brg

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

假设有以下数据表定义：

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

你想要返回JSON数组中包含键/值对"kind":"person"（不是嵌套的JSON对象{"kind":"person"}）的元素 - 并计算数组元素和表格行数（每行可能有多个匹配的数组元素）。

解决方案

要获取包含符合条件的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"}]';

返回：

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

返回结果（对于一个具有更多条目的表）：

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

但是一个更通用的索引，能够服务于更多不同的查询可能是更好的选择。