Почему один запрос работает, а другой нет?

Question

[uRoot]

Этот SQL-запрос работает:

select * from sms_campaigns as c where c.leads->'statuses'->'ids' ?| array['обработка', 'выкуплено'];

Но это нет (я ничего не вижу):

select * from sms_campaigns as c where c.leads->'niches'->'ids' ?| array['7', '5'];

Как исправить сломанный запрос?

Answer 1

[Сергей Соловьев]

Значит SELECT ничего не вернул - нет таких записей, которые удовлетворяют условию.
Как исправить - добавить записи, у которых есть json у которого поле 'niches'->'ids' имеет значение 7 или 5.

Comments

[uRoot]

Хм, просто в БД такие записи есть:

[Akina]

uRoot,

просто в БД такие записи есть

Нет. Число 5 и строка '5' - не одно и то же

[Сергей Соловьев]

Akina, вот так попробуй

select * from sms_campaigns as c where c.leads->'niches'->'ids' ?| array[7, 5];

вместо строк в массиве - числа

[uRoot]

Сергей Соловьев,

Ошибка:

[2024-01-17 17:44:52] [42883] ERROR: operator does not exist: jsonb ?| integer[]
[2024-01-17 17:44:52] Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

[uRoot]

Akina, я попробовал сделать запрос, изменив тип данных в запросе, ошибка выше.

[Сергей Соловьев]

uRoot, покажи полный json

[uRoot]

Сергей Соловьев,

{
"niches": {"ids": [13, 7], "inverted": false},
"statuses": {"ids": ["оплачено"], "inverted": false}
}

[Сергей Соловьев]

uRoot,

select * from sms_campaigns as c where c.leads->'niches'->'ids'::text[] ?| array['7', '5'];

вот так попробуй

[uRoot]

Сергей Соловьев,

[2024-01-17 18:09:07] [22P02] ERROR: malformed array literal: "ids"
[2024-01-17 18:09:07] Detail: Array value must start with "{" or dimension information.

[Сергей Соловьев]

uRoot, тогда это

select * from sms_campaigns as c where c.leads->'niches'->'ids' @> '7'::jsonb OR c.leads->'niches'->'ids' @> '5'::jsonb;

[uRoot]

Сергей Соловьев, так работает. А если у меня массив уже будет на 5 и 7, а 20 таких значений? Это нормальное решение?

[Сергей Соловьев]

uRoot, можно найти оператор/функцию, которая конвертирует jsonb массив чисел в массив строк, а потом проверяет на вхождение с массивом строк. Типа такого, но не уверен

select * from sms_campaigns as c where c.leads->'niches'->'ids'::text::text[] ?| array['7', '5'];

[uRoot]

Сергей Соловьев, Так не работает, если что

Answer 2

[Akina]

Оператор ?| в PostgreSQL выполняет поиск строковых значений в списке элементов массива или ключей (имён атрибутов) объекта:

jsonb ?| text[] → boolean

Do any of the strings in the text array exist as top-level keys or array elements?

'{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'd'] → t

https://www.postgresql.org/docs/current/functions-...

В PostgreSQL нет встроенных функции или оператора для указанной задачи или для проверки на пересечение двух числовых JSON массивов. Возможное решение - разобрать JSON-массив на элементы, собрать в обычный массив и использовать для двух массивов оператор &&

Comments

[uRoot]

В PostgreSQL нет встроенных функции или оператора для указанной задачи или для проверки на пересечение двух числовых JSON массивов.

Хм, а как тогда сделать выборку? Видел решение с "@>", но это же абсолютно противоположное, что мне нужно, если я все правильно понимаю.

[uRoot]

Отак работает:

select * from sms_campaigns as c where c.leads->'niches'->'ids' @> '7'::jsonb OR c.leads->'niches'->'ids' @> '5'::jsonb;

Но насколько это хорошее решение?

[Akina]

uRoot,

Видел решение с "@>",

так работает

Это всё проверки на ОДНО значение. А не на пересечение с набором значений.

насколько это хорошее решение?

Нинасколько. Это два парсинга вместо одного.

[Akina]

uRoot, Выложите в вопросе: Структуру таблицы (CREATE TABLE), пример данных (INSERT INTO, 5-7 записей), заданные критерии отбора и требуемый для всего этого результат.

Можно создать online fiddle (скажем, на https://sqlize.online/ или на https://dbfiddle.uk/) и дать ссылку.

[uRoot]

Akina,

А не на пересечение с набором значений.

Ну да, то что нужно, Чем больше в массив чисел передал (array[7' 5]), тем больше записей нашлось.

Возможное решение - разобрать JSON-массив на элементы, собрать в обычный массив и использовать для двух массивов оператор &&

Сложно, когда ти не шаришь в PG))

[uRoot]

Akina,

Выложите в вопросе: Структуру таблицы (CREATE TABLE), пример данных (INSERT INTO, 5-7 записей)

Вроде оно:
https://pastebin.com/60ESAYqL

заданные критерии отбора и требуемый для всего этого результат

Ну, по сути, все, что делает оператор ?|, но только для чисел. Т.е в базе есть 10 записей, которые содержат ID 5 (внутри JSON), также 20, которые содержат ID 7 (внутри JSON). Передал array['7', '5'] - выбрались все 30 записей, передал array['7'] - только 20. Понятно, что в колонке, в JSON, может быть сразу два или больше ID, по которым мы ищем.

В проекте используется Knex.JS, то оно там выглядит так:

.where(function () {
      if (filters.niches && filters.niches.ids.length) {
        const niches = filters.niches.ids

        this.whereRaw(`c.leads->'niches'->'ids' \\?| array[${niches.map(_ => '?').join(',')}]`, niches)
      }
    })

[Akina]

Вроде оно

Вот ВСЁ оно зачем? Достаточно было оставить поле ID и поле с JSON-значением, а все остальные поля и значения для них удалить - ибо оно нафиг не нужно для решения проблемы.

[uRoot]

Akina, было очень поздно, решил все сделать быстро))