Как выбрать из массива MySQL JSON в виде строк?

Используя MySQL 5.7, как я могу выбрать элементы массива из столбца JSON в виде строк?

Таблица Person

id      data
1       [{"name":"one"},{"name":"two"}]
2       [{"name":"three"},{"name":"four"}]

Я хочу повернуть элементы массива JSON в строки. Неработающий SQL ниже...

SELECT
    p.id AS personId,
    d->'$.name' AS name
FROM
    Person p
    JSON_EXTRACT(p.data) d  # <-- not valid SQL
WHERE
    d->'$.name' <> 'three'

Ожидаемый результат

personId  name
1         one
1         two
2         four

mysql arrays json sql unnest
person Josh M.    schedule 28.10.2020    source источник
comment
Единственный разумный способ сделать это — использовать JSON_TABLE() в MySQL 8.0, поэтому вам следует обновиться. Альтернативой является хранение ваших данных в обычных строках и столбцах. В любом случае я бы рекомендовал избегать JSON в вашем случае, поскольку вам нужно искать определенное значение в поле JSON. Я рекомендую ссылаться на поля JSON только в списке выбора и ни в каком другом предложении вашего запроса.   -  person Bill Karwin    schedule 28.10.2020
comment
Это существующая БД (не такая, как здесь). Мы не используем данные JSON в SQL-запросах, это необходимо для восстановления данных, когда было бы полезно сузить наше исправление на основе некоторых значений в большом двоичном объекте JSON. В остальном БД его не трогает. Да, обновление до MySQL 8 было бы неплохо...   -  person Josh M.    schedule 29.10.2020

Ответы (1)


arrow_upward
1
arrow_downward

Если вы используете MySQL 8.0, вы можете использовать json_table():

select p.id, x.name
from person p
cross join json_table(
    p.data,
    '$[*]' columns (name varchar(50) path '$.name')
) x
where x.name <> 'three'

В более ранних версиях одной из альтернатив было использование производной таблицы чисел для удаления вложенности массива:

select *
from (
    select p.id, json_unquote(json_extract(p.data, concat('$[', n.num, '].name'))) name
    from person p
    inner join (select 0 num union all select 1 union all select 2) n
        on n.num < json_length(p.data)
) t
where name <> 'three'

Подзапрос union all должен содержать как минимум столько элементов, сколько максимальное количество элементов в любом массиве JSON вашей таблицы.

Демонстрации на DB Fiddle:

person GMB    schedule 28.10.2020
comment
Что, если он захочет обработать массив JSON с четырьмя элементами? Вы должны изменить запрос? Простите меня, но это не разумное решение. Что, я думаю, является точкой показа этого. - person Bill Karwin; 28.10.2020
comment
@BillKarwin: идея состоит в том, чтобы иметь таблицу чисел, в которой достаточно элементов, поэтому не нужно беспокоиться об изменении запроса позже. Как вы знаете, существуют относительно простые способы создания таблицы очень больших чисел. Тем не менее, я согласен с вами в том, что данные лучше всего хранить в нормализованной структуре (кстати, почему вы удалили свой ответ? Я считаю, что он очень дополняет мой). - person GMB; 28.10.2020
comment
Я понял, что этот вопрос был дубликатом, и проголосовал за его закрытие. Я чувствовал, что было бы дурным тоном опубликовать ответ самому, а затем проголосовать за закрытие вопроса, не позволяя другим отвечать. Но я добавлю комментарий к тому же эффекту. - person Bill Karwin; 28.10.2020