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

本文介绍了查询包含JSON对象数组的jsonb列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I use PostgreSQL 9.5 and Rails 5. I want to query the jsonb column shown below that holds an array of JSON objects to return all the JSON array element containing {"kind":"person"} and also perform a count.
The SQL I use is shown below the json data. Running the query just returns an empty array.

我已尝试在此处和此处.

这是我的jsonb数据的样子:

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

我希望其中一个sql查询返回:

I want one of the sql query to return:

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

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

and another query to return array back so that I can call count on it in my app and also loop over it to create forms:

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

我尝试了此SQL查询:

I tried this SQL query:

 "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值:

With JSON values like this:

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数组元素(不是嵌套的JSON对象{"kind":"person"})-并对数组元素以及表行进行计数(可能有多个匹配的数组元素每行).

You want to return elements of a JSON array that contain the key/value pair "kind":"person" (not a nested JSON object {"kind":"person"}) - and count array elements as well as table rows (there may be multiple matching array elements per row).

要获取行数，其中在segments列中包含合格的jsonb值:

To get the count of rows containing a qualifying jsonb value in column segments:

SELECT count(*)
FROM   segments s
WHERE  s.payload @> '[{"kind":"person"}]';

要获取 所有合格的JSON数组元素 (本身就是JSON对象)-加上元素的总数(可能同时大于上述总数:

To get all qualifying JSON array elements (being JSON objects themselves) - plus the total count of elements (may be greater than above count at the same time:

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

要一次全部 :

To get all at once:

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

返回(对于具有更多条目的表):

Returns (for a table with more entries):


elem                      | ct_elem | ct_rows
--------------------------+---------+---------
{"kind": "person",  ... } | 4       | 3
{"kind": "person",  ... } | 4       | 3
...

但是 我认为您真的想要这个 :

But I think you really want this:

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

返回(对于具有更多条目的表):

Returns (for a table with more entries):


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

这将标识匹配的行，然后选择匹配的数组元素，并仅使用这些元素在每行中构建一个数组.加计数.

This identifies matching rows, then select matching array elements and builds an array per row with only those. Plus counts.

为获得最佳性能，您将具有jsonb_path_ops GIN索引，例如:

For best performance you would have a jsonb_path_ops GIN index like:

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

(但是更通用的索引可以服务于更多不同的查询可能是一个更好的选择.)

(But a more generic index to serve more different queries may be a better choice.)