Запрос столбца jsonb, содержащего массив объектов JSON

я используюPostgreSQL 9.5 и Rails 5. Я хочу запроситьjsonb столбец, показанный ниже, который содержит массив объектов JSON для возврата всего элемента массива JSON, содержащего{"kind":"person"} а также выполнить подсчет.
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
 brg01 авг. 2016 г., 03:25
Большое спасибо за помощь. Да, я имел в виду элемент массива JSON. Я перефразировал вопрос, чтобы, надеюсь, прояснить его.
 Erwin Brandstetter01 авг. 2016 г., 02:50
=>действительный синтаксис дляhstore, но не дляjson (или жеjsonb). Действительный литерал json:'{"kind":"person"}' Пожалуйста, исправьте свой пример и добавьте, как будет выглядеть ожидаемый результат."все JSON" немного нечетко И есть строка таблицы, также есть тип данныхjson, но что такое"JSON ROW"? Вы имеете в виду элемент массива JSON?

Ответы на вопрос(1)

Решение Вопроса

Предполагая это определение таблицы:

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);

(Но более общий индекс для обслуживания разных запросов может быть лучшим выбором.)

Связанные с:

Индекс для поиска элемента в массиве JSON

Запрос для элементов массива внутри типа JSON

Лучший способ получить счетчик результатов до применения LIMIT

терминология

Мы имеем дело с объектом JSONсодержащий массив JSON, сохраненный как Postgresjsonb тип данных - «массив JSON» для краткости, ноне «массив JSON».

 brg01 авг. 2016 г., 16:24
Большое спасибо за ваши усилия и время. ура
 Erwin Brandstetter01 авг. 2016 г., 15:50
@brg: я упростил соответственно.
 brg01 авг. 2016 г., 10:10
Большое спасибо за подробное объяснение и за щедрость обмена своими знаниями.
 Erwin Brandstetter01 авг. 2016 г., 22:13
@brg: я тестировал с pg 9.5 и мои запросы работают так, как рекламируется. Колонкаpayload в гисте вы ссылаетесь, чтобы избежать двойных кавычек.
 brg01 авг. 2016 г., 23:17
Хорошо, я удалю и повторно вставлю данные. Спасибо и приятного вам дня.
 brg01 авг. 2016 г., 17:51
Я запускал каждый sql непосредственно в базе данных через psql. Первый запрос на счетчик работает, как и ожидалось, возвращая правильный счет. Но все остальные запросы только что вернулись(0 строк) вместо ожидаемого ответа, показанного в вашем примере. Итак, я побежалSELECT * FROM сегментов; и скопировал то, что возвратила база данных, и вставил его сюда, чтобы вы могли увидеть точную таблицу и ее содержимое:gist.github.com/anonymous/1416f3fe748c792e85047a8d42d8fc3f
 Everett Carney01 мая 2017 г., 17:36
Надеюсь, кто-то все еще смотрит эту ветку! У меня есть столбец с массивом объектов JSONB, я пытался выполнить запрос следующим образом: SELECT id, link, users_in_photo FROM "Posts" p WHERE p.users_in_photo @> '[{"username": "arsloan73" }] 'LIMIT 1; И получил это: «[» должен вводить явно указанные размеры массива. Не уверен, что я делаю не так здесь ... какие-либо предложения?
 brg01 авг. 2016 г., 12:02
Я просто посмотрел на мой вопрос и понял, почему вы думаете, что я вкладываюсь под ключ, как это'{"a": [{}, {}]}' как ты сделал, потому что у меня былоа = прежде чем JSON возражает в моем вопросе. Так что я удалил это из своего вопроса, потому что мои текущие данные в точности такие же, как в первой ссылке, на которую вы мне указали. То есть'[{}, {}]', Я имею в виду ссылку в моем первом комментарии, которая находится над этим комментарием.
 brg01 авг. 2016 г., 11:17
Я рад изменить мои данные в форме в вашем ответе, но просто чтобы уточнить, является ли неправильный дизайн для хранения объектов json, как я это делал изначально, подобно первой ссылке, на которую вы ссылались:stackoverflow.com/questions/18404055/...где вы предложили этот запросSELECT * FROM композиций WHERE исполнители @> '[{"name": "The Dirty Heads"}]'; , Если я адаптирую этот запрос к"SELECT * FROM сегментов, ГДЕ полезная нагрузка @> '[{\" kind \ ": \" person \ "}]';" чтобы проверить с моим оригинальным JSON, который похож на их, он возвращает пустой результат.

Ваш ответ на вопрос