How to deal with not existing values using JSON_EXTRACT?

I have a list ob objects. Each object contains several properties. Now I want to make a SELECT statement that gives me a list of a single property values. The simplified list look like this:

[
    [
        {
            "day": "2021-10-01",
            "entries": [
                {
                    "name": "Start of competition",
                    "startTimeDelta": "08:30:00"
                }
            ]
        },
        {
            "day": "2021-10-02",
            "entries": [
                {
                    "name": "Start of competition",
                    "startTimeDelta": "03:30:00"
                }
            ]
        },
        {
            "day": "2021-10-03",
            "entries": [
                {
                    "name": "Start of competition"
                }
            ]
        }
    ]
]

The working SELECT is now

SELECT
    JSON_EXTRACT(column, '$.days[*].entries[0].startTimeDelta') AS list
FROM table

The returned result is

[
    "08:30:00",
    "03:30:00"
]

But what I want to get (and also have expected) is

[
    "08:30:00",
    "03:30:00",
    null
]

What can I do or how can I change the SELECT statement so that I also get NULL values in the list?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

SELECT startTimeDelta
FROM test
CROSS JOIN JSON_TABLE(val,
                      '$[*][*].entries[*]' COLUMNS (startTimeDelta TIME PATH '$.startTimeDelta')) jsontable

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=491f0f978d200a8a8522e3200509460e

Do you also have a working idea for MySQL< 8? – Lars

What is max amount of objects in the array on the 2nd level? – Akina

Well it’s usually less than 10 – Lars

SELECT JSON_EXTRACT(val, CONCAT('$[0][', num, '].entries[0].startTimeDelta')) startTimeDelta
FROM test
-- up to 4 - increase if needed
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) nums
WHERE JSON_EXTRACT(val, CONCAT('$[0][', num, '].entries[0]')) IS NOT NULL;

https://www.db-fiddle.com/f/xnCCSTGQXevcpfPH1GAbUo/0

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0