MySQL поле с типом JSON как искать по значению внутри многомерного массива и сделать для него индекс?

Question

[Kerm]

Есть json:

{
  "outNum": "23123",
  "userId": 657,
  "outDate": "2017-11-17",
  "reasonNum": "123-рчс-17-0084",
  "FiasAoguid": [
    "da051ec8-da2e-4a66-b542-473b8d221ab4",
    "29251dcf-00a1-4e34-98d4-5c47484a36d4"
  ],
  "reasonDate": "2017-07-16"
}

Если для обычного значения json я могу сделать отдельную виртуальную колонку с ключом и фильтровать по ней:

ALTER TABLE `default` ADD COLUMN `json_outDate_virtual` date GENERATED ALWAYS AS (`properties` ->> '$."outDate"') COMMENT 'Дата';

То, по массивам я не представляю как сделать такой индекс или вообще как фильтровать его, запрос типа этого, не работает:

select * from default where JSON_EXTRACT(`properties`, "$.FiasAoguid") LIKE "da051ec8-da2e-4a66%";
select * from default where JSON_EXTRACT(`properties`, "$.FiasAoguid[*]") LIKE "bd8e6511-e4b9-4841-90de%";
select * from default where `properties`->"$.FiasAoguid[*]" LIKE "bd8e6511-e4b9-4841-90de%";

Может можно конкатенацию как то сделать в стринговое поле занчений масива, скажем через запятую и лайкать по ним?

Вот так я могу получить значение конкретного ключа:

select `properties`->"$.FiasAoguid[0]" as code from claims where `properties`->"$.FiasAoguid" is not null;

Не пойму как сделать поиск по всем ключам [*] не помогает

Comments

[Adamos]

Хочешь работать с данными в SQL - используй нормальные формы.
Не хочешь работать с данными в SQL - можешь использовать JSON.

[Adamos]

Такие "массивы", например, стандартно ложатся в связанную один-ко-многим таблицу и делают всю вторую половину этого вопроса бессмысленной.

[Александр]

Если не планируешь работать с данными SQL, то как вариант, чтобы найти все строки, содержащие значение "da051ec8-da2e-4a66" в массиве "FiasAoguid"

SELECT * FROM default WHERE JSON_CONTAINS(properties, '["da051ec8-da2e-4a66"]', '$.FiasAoguid');

Так же создать виртуальную колонку для значения массива, используя функцию JSON_EXTRACT() для извлечения и функцию JSON_ARRAY() для преобразования его в массив JSON. И создать индекс на этой виртуальной колонке

ALTER TABLE `default` ADD COLUMN `FiasAoguid_array` json GENERATED ALWAYS AS (`properties` -> '$.FiasAoguid'),
ADD INDEX `FiasAoguid_index` (`FiasAoguid_array`);

После создания индекса запрос будет выполняться более эффективно

SELECT * FROM default WHERE JSON_CONTAINS(FiasAoguid_array, '["da051ec8-da2e-4a66"]')

Конкатенация значений массива в строку и использование LIKE приведет к ошибкам если значения массива будет содержать запятые или другие символы, используемые в разделителях.Так что я бы не советовал.

[Akina]

запрос типа этого, не работает

Пока ты будешь работать с JSON как с текстом, у тебя ничего не будет нормально работать. Следует использовать JSON-функции.

Впрочем, на выходе всё одно будет фуллскан.

по массивам я не представляю как сделать такой индекс

Для целых можно было бы построить multivalued index. А с GUID этот номер не пройдёт.

[Kerm]

Александр, а нет ли там какой ошибки в этом:

ALTER TABLE `default` ADD COLUMN `FiasAoguid_array` json GENERATED ALWAYS AS (`properties` -> '$.FiasAoguid'),
ADD INDEX `FiasAoguid_index` (`FiasAoguid_array`);

не дает создать индекс для колонки с типом json

[Александр]

Kerm,
Да, возможно такое, попробуй

ALTER TABLE `default` ADD COLUMN `FiasAoguid_array` JSON GENERATED ALWAYS AS (`properties` -> '$.FiasAoguid'),
ADD INDEX `FiasAoguid_index` USING BTREE (`FiasAoguid_array`(1));

Не за компьютером сейчас, отпишись тогда, я позже посмотрю.

[Kerm]

Александр, не, не верный синтаксис

[Erik Mironov]

Для MySQL возможность индексирования JSON-массивов напрямую не предоставляется. Вы можете, конечно, создать виртуальную колонку и индексировать ее, но это подходит для простых полей JSON, не для массивов. Поиск по массивам, как правило, медленный, потому что необходимо производить полный скан таблицы.

Есть несколько способов обойти это:

1. Денормализация данных
Перенести важные для поиска элементы массива в отдельные поля/таблицы, которые можно будет индексировать.

2. Поиск в каждом элементе массива
Сделать это в MySQL можно с помощью JSON_UNQUOTE и JSON_EXTRACT функций:

SELECT *
FROM `default`
WHERE JSON_UNQUOTE(JSON_EXTRACT(`properties`, '$.FiasAoguid[0]')) = 'искомое_значение'
   OR JSON_UNQUOTE(JSON_EXTRACT(`properties`, '$.FiasAoguid[1]')) = 'искомое_значение'
-- и так далее

3. Поиск среди конкатенированных значений
Вы можете попытаться создать виртуальную колонку, где все значения массива будут конкатенированы в строку. Это можно попробовать сделать средствами MySQL, но может быть нетривиально.

4. Использование другой СУБД
Другие СУБД, такие как PostgreSQL, предоставляют более широкие возможности для работы с JSON и JSONB, включая индексацию.

5. Приложение-посредник
Сделать поиск на уровне приложения, загрузив данные и проведя фильтрацию уже там.
Стоит заметить, что хотя можно использовать LIKE или REGEXP для выполнения частичного поиска в строке JSON, такие операции будут очень медленными на больших объемах данных. В зависимости от ваших требований один из перечисленных методов может быть более подходящим.