Работа с JSON. Разделение строки на отдельные записи

Метки: ,

Есть массив значений, хранящихся в одной строке, например, через запятую. Задача: преобразовать эту строку значений в отдельные записи. Такие задачи на практике встречаются достаточно часто. Предлагаемые решения, как правило, основываются на использовании регулярных выражений и иерархических запросах. Но есть более нестандартный и быстрый способ получить из одного значения несколько строк.

Исходные данные

Имеется таблица с полем, в котором хранится массив значений с разделителем. Например, числа, через запятую.

Table: course_modules

id     course     modules
-----------------------------
1      151        501,502,505
2      152        705,707

Задача

Выделить каждое значение из поля modules в отдельную запись вместе со значениями остальных полей.

id     course     module
-----------------------------
1      151        501
1      151        502
1      151        505
2      152        705
2      152        707

Решение

Разделить строку значений может простая функция JSON_TABLE.

JSON_TABLE( '[501,502,503]' , '$[*]' COLUMNS (field_name PATH '$') )

Используем эту функцию для разделения значений каждой записи на отдельные строки.

SELECT
    id,
    course,
    module
FROM
    course_modules a,
    JSON_TABLE( '[' || a.modules || ']', '$[*]' COLUMNS (module PATH '$') )

Результатом выборки будет та самая таблица, которую мы хотели получить.

Усложним задачу

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

Table: course_modules

id     course     modules      sections      items     
-----------------------------------------------------
1      151        501,502,505  10,20,50      15,16,17
2      152        705,707      8,9           21,22

Требуется разделить значения таким образом, чтобы в первой строке были первые значения из каждой последовательности, во второй строке – вторые и т.д.

id     course     module     section       item
-------------------------------------------------
1      151        501        10            15
1      151        502        20            16
1      151        505        50            17
2      152        705        8             21
2      152        707        9             22

Решение

Можно было бы добавить описание еще двух полей в уже известную функцию JSON_TABLE. Но тогда мы получим декартово произведение всех трех полей. Поэтому необходимо добавить дополнительное условие. Порядковый номер значения каждого из трех столбцов должен совпадать. Для этого надо получить порядковый номер для каждого элемента из разделяемой строки.

JSON_TABLE( '[501,502,503]' , '$[*]' COLUMNS (field_name2 FOR ORDINALITY) )

Дополнительное поле типа FOR ORDINARITY содержит порядковый номер элемента.

Добавляем в исходный запрос вычисление порядкового номера и проверку равненства номеров элементов.

SELECT
    a.id,
    a.course,
    s1.module,
    s2.section,
    s3.item
FROM
    course_modules a,
    JSON_TABLE( '['||a.modules||']' , '$[*]' COLUMNS (module PATH '$', num FOR ORDINALITY) ) s1,
    JSON_TABLE( '['||a.sections||']' , '$[*]' COLUMNS (section PATH '$', num FOR ORDINALITY) ) s2,
    JSON_TABLE( '['||a.items||']' , '$[*]' COLUMNS (item PATH '$', num FOR ORDINALITY) ) s3
WHERE
    s1.num = s2.num AND
    s2.num = s3.num
Close Menu