Вопрос или проблема
Получение данных из массива в массив в колонке JSON MySQL
Столбец JSON в MYSQL содержит значения массивов, встроенные в массив. Я хочу извлечь индексы, содержащие определенные значения из этого массива с помощью команд mysql.
JSON столбец; id=1 // В таблице только одна строка
[
[57,0,0,0,0,53,"Сентябрь - 2024",0,0,[0,0,1500,""]],
[56,7,0,17267904,0,53,"Сентябрь - 2024","0","1",[9,1262,238,"1~0003"]],
[55,7,0,17267904,0,53,"Сентябрь - 2024","0","1",[9,1426,74,1]],
[54,6,0,17267904,0,53,"Сентябрь - 2024","0","8",[9,1426,74,1]],
[53,4,0,17267904,0,53,"Сентябрь - 2024","0","6",[9,1426,74,1]],
[52,7,0,17267904,0,53,"Сентябрь - 2024","0","1",[9,1426,74,1]],
[51,7,0,17267904,0,53,"Сентябрь - 2024","0","5",[9,1426,74,1]]
.....
]
9-й элемент массивов в строках — это номера строк людей в записи. Например, я хочу взять все элементы массивов, у которых 9-й элемент равен 1, и создать новый массив для использования.
[52,7,0,17267904,0,53,"Сентябрь - 2024","0","/*
Ищем человека =*/ 1",[9,1426,74,1]],
пример результата ;
[
[56,7,0,17267904,0,53,"Сентябрь - 2024","0","1",[9,1262,238,"1~0003"]],
[55,7,0,17267904,0,53,"Сентябрь - 2024","0","1",[9,1426,74,1]],
[52,7,0,17267904,0,53,"Сентябрь - 2024","0","1",[9,1426,74,1]]
]
То, что я написал, похоже на это;
$myQuery= $conn->query("
SELECT JSON_EXTRACT(vt_st_cari_haraket_json, '$[*][0]')
FROM vt_tb_cari_haraket_2024
WHERE JSON_CONTAINS(JSON_EXTRACT(vt_st_cari_haraket_json, '$[*][9][0][0]'), '1', '$')
");
Однако, несмотря на то, что я пробовал различные похожие вещи, я не смог получить желаемый результат.
Вкратце; если 9-й элемент в подмассивах является искомым значением, я хочу получить весь массив.
Я хочу выбрать и извлечь массивы внутри массива в столбце JSON MYSLQ
select json_arrayagg(j.a) as _result
from vt_tb_cari_haraket_2024
cross join json_table(vt_st_cari_haraket_json, '$[*]' columns(a json path '$')) as j
where j.a->>'$[8]' = '1'
group by id;
Результат:
[
[56, 7, 0, 17267904, 0, 53, "Сентябрь - 2024", "0", "1", [9, 1262, 238, "1~0003"]],
[55, 7, 0, 17267904, 0, 53, "Сентябрь - 2024", "0", "1", [9, 1426, 74, 1]],
[52, 7, 0, 17267904, 0, 53, "Сентябрь - 2024", "0", "1", [9, 1426, 74, 1]]
]
Демо:
https://www.db-fiddle.com/f/fhMr8VtNjNY5B6QPyhS9JN/0
Обратите внимание, что функция JSON_TABLE() требуется MySQL 8.0 или новее.
Ответ или решение
Чтобы извлечь подмассивы из JSON-колонки в MySQL, где мы ищем массивы с определённым значением на 9-й позиции (индекс 8), можно использовать функцию JSON_TABLE(), доступную в MySQL 8.0 и выше. Давайте рассмотрим, как это сделать.
Шаг 1: Подготовка таблицы
Предположим, у вас есть таблица vt_tb_cari_haraket_2024 с колонкой типа JSON, содержащей массивы. Нам нужно извлечь все подмассивы, у которых 9-й элемент равен ‘1’.
Шаг 2: Структура запроса
Используйте следующий SQL-запрос для извлечения нужных данных:
SELECT JSON_ARRAYAGG(j.a) AS _result
FROM vt_tb_cari_haraket_2024
CROSS JOIN JSON_TABLE(vt_st_cari_haraket_json, '$[*]' COLUMNS(a JSON PATH '$')) AS j
WHERE j.a->>'$[8]' = '1'
GROUP BY id;Пояснение запроса
-
JSON_TABLE(): Эта функция преобразует значения JSON в табличный формат, что позволяет нам ссылаться на элементы JSON как на обычные строки. В данной функции используем путь
'$[*]', чтобы получить каждый массив на верхнем уровне. -
CROSS JOIN: Мы используем перекрёстное соединение с результирующим набором, чтобы связать каждую строку таблицы с массивами из JSON.
-
WHERE: Условие
WHERE j.a->>'$[8]' = '1'проверяет, соответствуют ли 9-е значение подмассива искомому значению ‘1’. - JSON_ARRAYAGG(): Эта агрегирующая функция объединяет результаты в один JSON-массив, что позволяет нам получить итоговый результат в форме JSON.
Шаг 3: Результат
После выполнения запроса вы получите результаты в следующем формате:
[
[56, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1262, 238, "1~0003"]],
[55, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1426, 74, 1]],
[52, 7, 0, 17267904, 0, 53, "Eylül - 2024", "0", "1", [9, 1426, 74, 1]]
]Как видно, были успешно извлечены все подмассивы, 9-е значения которых равны ‘1’.
Примечание
Для работы с данной функцией JSON_TABLE() ваша версия MySQL должна быть 8.0 или выше. Если у вас более старая версия, вам, возможно, придется использовать другие подходы, такие как парсинг и манипуляция данными на стороне приложения.
Если у вас остались дополнительные вопросы или нужны разъяснения, не стесняйтесь спрашивать!