Блог

Главная - Вопросы по программированию - Объединение MySQL в массив JSON

Объединение MySQL в массив JSON

#mysql #json

#mysql #json

Вопрос:

У меня есть эти строки в таблице mysql

 id  categoryNameSp    categoryNameEn
1   Comida            Food
2   Fruta             Fruit
3   Fruta Seca        Dried fruit

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

 pid  path
1    ["1", "2", "3"]

Я хочу вернуть путь, но вместо чисел я хочу вернуть categoryNameEn, поэтому возврат будет:

   pid  path
  1    ["Food","Fruit", "Dried fruit"]

Комментарии:

1. Из любопытства, почему вы решили сохранить пути в массиве JSON, если вы хотели выполнять подобные запросы, которые обрабатывают элементы массива как дискретные значения?

2. Извините за поздний ответ, цель состояла в том, чтобы разрешить поиск по категориям, который рассматривал бы все выбранные категории точно так же, как система папок Windows. Поэтому на стороне клиента я сохраняю все выбранные категории, а затем возвращаю результат с помощью функции mysql json_contains() .

3. Но почему бы не сохранить каждое значение пути в отдельной строке? Тогда было бы намного проще выполнить соединение с таблицей категорий без использования сложных функций JSON.

4. Поступая таким образом, я был бы вынужден определить количество столбцов, и поэтому мне пришлось бы определить максимальное количество подкатегорий, но с помощью json я управляю всем на стороне клиента, и я могу управлять этим, если обнаружу, что путь завершен

Ответ №1:

Для этого потребуется использовать функциональность JSON_TABLE, а также функцию агрегирования JSON_ARRAYAGG.

Воссоздаем вашу ситуацию, используя следующие DML и DDL:

 CREATE TABLE categories (id INT, name VARCHAR(30));
INSERT INTO categories VALUES (1, 'Food'), (2, 'Fruit'), (3, 'Dried Fruit');

CREATE TABLE stuff (pid INT, path JSON);
INSERT INTO stuff VALUES (1, '["1", "2", "3"]');

Затем мы можем выполнить следующий (необычный) запрос:

 SELECT pid, JSON_ARRAYAGG(c.name) FROM stuff AS s
    JOIN JSON_TABLE(s.path, '$[*]' COLUMNS (category INT PATH '

Идея состоит в том, чтобы создать таблицу из данных JSON, найденных в  stuff  таблице. Затем мы объединяем его с таблицами категорий и объединяем результат в массив JSON.

Результат:


  ------ ---------------------------------- 
| pid  | JSON_ARRAYAGG(c.name)            |
 ------ ---------------------------------- 
|    1 | ["Food", "Fruit", "Dried Fruit"] |
 ------ ----------------------------------


)) AS cats
JOIN categories AS c ON cats.category = c.id
GROUP BY pid;
Идея состоит в том, чтобы создать таблицу из данных JSON, найденных в stuff таблице. Затем мы объединяем его с таблицами категорий и объединяем результат в массив JSON.

Результат: