How to split json field into multi-rows?

user_5833704 0
Viewed 2331

I have a JSON field in a table in which the data might be stored as one or more elements (i.e. "[{a: 1, b:2}]" or "[{a: 1, b:2},{b: 1, c:2}]").

How can I generate MariaDB query to result in the following:

A          B          C
===========================
1          2          NULL
NULL       1          2

Of course the "NULL" value can be altered but the idea is that a JSON array will be shown as row for each element.

I know that MySQL have JSON_TABLE function with the new versions, i'm looking for MariaDB equivalent.

mariadb
1 Answers

After some research I noticed a lot of people use a table with predefined numbers (0 till who_knows). For example a table called numbers with one field (n) from 1 to 200 (depending on how many splits might happen).

The query that makes it work is as follows:

Assuming rawData is a JSON string that have "events" as the json array that you want to split into rows.

SELECT
    logEvent, JSON_EXTRACT(logEvent, "$.author_name"), JSON_EXTRACT(logEvent, "$.text")
FROM
    (
    SELECT
        JSON_EXTRACT(
            rawData,
            CONCAT('$.events[', num.n, ']')
        ) AS logEvent
    FROM
        numbers num
    INNER JOIN logsRaw 
) AS a
user_58337040
Related