Как использовать Knex для поиска всех записей в JSON, если хотя бы одно условие совпадает?

Question

Рафаил

postgresql , node.js

Как использовать Knex для поиска всех записей в JSON, если хотя бы одно условие совпадает?

Как сделать фильтрацию так, чтобы при наличии хотя бы одного статуса из массива filters.statuses.ids в поле c.leads->'statuses'->'ids' возвращалась запись? Я рассматривал использование оператора ?| вместо @> для этого случая, но не уверен, что это правильно. БД: PostgreSQL. Формат данных: c.leads - JSON, filters.statuses.ids - массив чисел (в другом похожем случае - строк).

15 января 2024 г. 17:28

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

Похожие вопросы

Answer 1

Для выполнения запросов к JSON полям в PostgreSQL, Knex.js предлагает свои методы. Если мы хотим проверить, содержит ли JSON-массив в базе данных PostgreSQL хотя бы один элемент из заданного массива, мы можем воспользоваться оператором '?|', как вы правильно предположили.

Давайте рассмотрим конкретный пример. Предположим, у нас есть таблица `clients`, в которой есть JSON-колонка `leads` с подобъектом `statuses` и JSON-массивом `ids`. Также у нас есть массив идентификаторов статусов, и нам нужно найти все записи, где `c.leads->'statuses'->'ids'` содержит хотя бы один из идентификаторов из нашего массива `filters.statuses.ids`.

Вот пример кода, который может быть использован для этой цели с Knex.js:

```javascript
const knex = require('knex')({ /* ... конфигурация ... */ });

// Предположим, что filters.statuses.ids это ваш массив идентификаторов
const filters = {
  statuses: {
    ids: [1, 2, 3] // пример массива статусов
  }
};

knex('clients')
  .select('*')
  .whereRaw(`"leads"->'statuses'->'ids' ?| array[${filters.statuses.ids.map(knex.raw.bind(knex, '?')).join(',')}]`, filters.statuses.ids)
  .then((rows) => {
    console.log(rows);
  })
  .catch((error) => {
    console.error(error);
  });
```

Обратите внимание на использование `whereRaw` для составления сырого SQL-запроса, поскольку сравнение с использованием оператора `?|` может не быть непосредственно доступно через стандартные методы Knex. 

В данном коде мы передаём параметры запроса в `whereRaw` как массив значений, соответствующих плейсхолдерам `?` в SQL запросе. Таким образом, Knex будет автоматически экранировать значения, предотвращая SQL-инъекции.

Не забудьте убедиться, что формат JSON в базе данных и структура соответствуют заданному запросу, и что колонка `leads` содержит действительный JSON объект с необходимыми полями `statuses` и `ids`.