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

Question

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 to 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 what 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?

score 2 · Accepted Answer · answered Apr 25 '23 at 19:19

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.

Man, its awesome!! I've been trying to solve this all afternoon and you do it in a few minutes!! Thanks really a lot @bill-karwin — José Ari Junior, Apr 25 '23 at 19:54
An expert is a person who has made every mistake in a narrow field. :-) — Bill Karwin, Apr 25 '23 at 19:59

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

1 Answers1