MySQL JSON_TABLE: извлечь вложенный массив в строки

JSON_TABLE() разворачивает вложенные JSON-массивы в строки, чтобы их можно было соединять и фильтровать обычным SQL. Результат можно сортировать и связывать с другими таблицами без отдельного разбора JSON в приложении.

Развернуть заказы и позиции

Пусть в events.payload лежит объект с массивом orders, а каждый заказ содержит массив items:

SELECT
    e.id AS event_id,
    jt.order_pos,
    jt.order_id,
    jt.item_pos,
    jt.sku,
    jt.qty
FROM events AS e
CROSS JOIN JSON_TABLE(
    e.payload,
    '$.orders[*]' COLUMNS (
        order_pos FOR ORDINALITY,
        order_id BIGINT PATH '$.id',
        NESTED PATH '$.items[*]' COLUMNS (
            item_pos FOR ORDINALITY,
            sku VARCHAR(64) PATH '$.sku' NULL ON EMPTY,
            qty INT PATH '$.qty' DEFAULT '1' ON EMPTY
        )
    )
) AS jt
ORDER BY e.id, jt.order_pos, jt.item_pos;

FOR ORDINALITY нумерует элементы с 1. NESTED PATH создаёт строки для items; если вложенный массив пуст или отсутствует, его столбцы будут NULL. Чтобы оставить только найденные позиции, добавьте WHERE jt.item_pos IS NOT NULL.

Функция доступна в MySQL 8.0.4+. Значения JSON приводятся к указанным SQL-типам, поэтому подберите типы по своей схеме и отдельно решите, считать ли некорректные значения ошибкой. См. MySQL JSON Table Functions.