Как отфильтровать строки по значениям вложенного JSON массива

При работе с базами данных часто возникает задача: в колонке хранится JSON-массив объектов, и нужно выбрать только те строки, где хотя бы один элемент массива содержит определённую подстроку в поле name. Стандартный оператор -> не подходит, так как он обращается к верхнему уровню (индексу массива). Разберём эффективные решения для разных СУБД.

Особенности JSON структуры

Предположим, колонка data содержит массив вида:

[  {    "name": "test",    "email": "test@test.com"  },  {    "name": "host",    "email": "host@host.com"  }]

Нам нужно найти строки, где хотя бы один объект имеет name, содержащий подстроку "st". Прямой доступ через data->0->>'name' проверяет только первый элемент, что не подходит при произвольной длине массива.

Решение для MySQL и MariaDB

В MySQL (8.0+) и MariaDB (10.2+) используйте функцию JSON_SEARCH:

SELECT * FROM table_nameWHERE JSON_SEARCH(data, 'one', '%st%', NULL, '$[*].name') IS NOT NULL;

Параметр 'one' означает, что достаточно найти первое совпадение. Шаблон '%st%' работает как стандартный LIKE. Путь '$[*].name' перебирает все элементы массива. Альтернатива - JSON_CONTAINS с LIKE через JSON_TABLE.

Решение для PostgreSQL

В PostgreSQL используйте оператор @> (содержит) с приведением типов или функцию jsonb_path_exists:

SELECT * FROM table_nameWHERE data @> '[{"name": "test"}]'::jsonb;

Но этот способ ищет точное совпадение. Для поиска по подстроке применяйте jsonb_path_exists с выражением like_regex:

SELECT * FROM table_nameWHERE jsonb_path_exists(data, '$[*].name ? (@ like_regex ".*st.*")');

Если колонка типа json (не jsonb), сначала приведите её: data::jsonb.

Решение для ClickHouse

В ClickHouse для фильтрации по элементам массива используйте функцию arrayExists в паре с JSONExtractString:

SELECT * FROM table_nameWHERE arrayExists(x -> JSONExtractString(x, 'name') LIKE '%st%', JSONExtractArrayRaw(data));

Функция JSONExtractArrayRaw превращает массив в массив строк, затем arrayExists проверяет условие для каждого элемента.

Общий подход через JSON_TABLE (SQL стандарт)

Если ваша СУБД поддерживает JSON_TABLE (MySQL 8+, Oracle, SQL Server 2016+), можно развернуть массив в строки и затем отфильтровать:

SELECT DISTINCT t.idFROM table_name t, JSON_TABLE(t.data, '$[*]' COLUMNS (name VARCHAR(100) PATH '$.name')) AS jtWHERE jt.name LIKE '%st%';

Этот метод универсален и позволяет выполнять любые дополнительные проверки.

Советы по производительности

• Для частых запросов создайте генерализованный индекс (MySQL: INDEX idx_name ((CAST(data->'$[*].name' AS CHAR(100) ARRAY)))).
• В PostgreSQL используйте GIN индекс на колонке jsonb: CREATE INDEX idx_gin ON table_name USING GIN (data jsonb_path_ops);.
• Избегайте полного сканирования таблицы - тестируйте запросы на EXPLAIN.

Заключение

Выбор метода зависит от СУБД и версии. Для MySQL подойдёт JSON_SEARCH, для PostgreSQL - jsonb_path_exists, для ClickHouse - arrayExists. Если нужна максимальная гибкость, используйте JSON_TABLE. Всегда проверяйте планы выполнения запросов.