Return all keys with their array items in a SELECT on a JSON field in MYSQL 8

Good guys i have a table in which a field is json in the following format:

{
  "monday": [{"end": "18:00:00", "start": "09:00:00"}, {"end": "21:00:00", "start": "19:00:01"}], 
  "sunday": [{"end": "15:00:00", "start": "14:00:00"}],  
  "saturday": [{"end": "18:00:00", "start": "09:00:00"}]
}

I want know if is it possible mount a SELECT who return all keys with their array items in a SELECT query, like this:

day date_start date_end
monday 09:00:00 18:00:00
monday 19:00:01 21:00:00
sunday 14:00:00 15:00:00
saturday 09:00:00 18:00:00

I tried this way:

SELECT j.* 
  FROM tabela t
INNER JOIN JSON_TABLE(
    t.field_json,
    '$.*[*]'
    COLUMNS(
        `date_start` TIME PATH '$.start',
        `date_end` TIME PATH '$.end',
        `day` VARCHAR(10) PATH '$.*'
    )
) AS j 
WHERE t.id=100

But the result is not who i want:

day date_start date_end
NULL 09:00:00 18:00:00
NULL 19:00:01 21:00:00
NULL 14:00:00 15:00:00
NULL 09:00:00 18:00:00

DB: https://dbfiddle.uk/9rXd_VL2

Any sugestion?

>Solution :

You need to make one JSON_TABLE() for the days as keys of your top-level JSON object. Then another JSON_TABLE() for the respective array under each of those keys.

SELECT id, d.day, j.start, j.end
FROM tablea
CROSS JOIN JSON_TABLE(JSON_KEYS(field_json), '$[*]' 
    COLUMNS (day VARCHAR(10) PATH '$')) AS d
CROSS JOIN JSON_TABLE(JSON_EXTRACT(field_json, CONCAT('$.', d.day)), '$[*]'
    COLUMNS (`start` TIME PATH '$.start', end TIME PATH '$.end')) AS j;

Dbfiddle

Like most uses of JSON in MySQL, this would have been so much easier if you had stored the data in normal rows and columns. JSON makes it easy to store data initially, but it makes writing most SQL queries much harder.

Leave a Reply