Using the key from JSON in query

Question

I'm new to MySQL and received a task which requires a complex(for me) query. I read the documentation and a few sources but I still cannot write it myself.

I'm selecting a rows from a table where in one of the cells I have JSON like this one

{ 
    [ 
        {
            "interval" : 2, 
            "start": 03, 
            "end": 07, 
            "day_of_week": 3
        }, {
            "interval" : 8, 
            "start": 22, 
            "end": 23, 
            "day_of_week": 6
        } 
    ] 
}

I want to check if some of the "day_of_week" values is equal to the current day of week and if so to write this value and the values of "start", "end" and "day_of_week" assoiciated with it in a variables to use them in the query.

This is really a PITA using the MySQL JSON functions. – Barmar Oct 06 '21 at 16:05 — Barmar, Oct 06 '21 at 16:05

Bill Karwin · Answer 1 · 2021-10-06T18:33:15.747

That's not valid JSON format, so none of the MySQL JSON functions will work on it regardless. Better just fetch the whole blob of not-JSON into a client application that knows how to parse it, and deal with it there.

Even if it were valid JSON, I would ask this: why would you store data in a format you don't know how to query?

The proper solution is the following:

SELECT start, end, day_of_week
FROM mytable
WHERE day_of_week = DAYOFWEEK(CURDATE());

See how easy that is when you store data in normal rows and columns? You get to use ordinary SQL expressions, instead of wondering how you can trick MySQL into giving up the data buried in your non-JSON blob.

JSON is the worst thing to happen to relational databases.

Re your comment:

If you need to query by day of week, then you could reorganize your JSON to support that type of query:

{ 
    "3":{
        "interval" : 2, 
        "start": 03, 
        "end": 07, 
        "day_of_week": 3
    }, 
    "6": {
        "interval" : 8, 
        "start": 22, 
        "end": 23, 
        "day_of_week": 6
    } 
}

Then it's possible to get results for the current weekday this way:

SELECT data->>'$.start' AS `start`, 
       data->>'$.end' AS `end`, 
       data->>'$.day_of_week' AS `day_of_week`
FROM (
    SELECT JSON_EXTRACT(data, CONCAT('$."', DAYOFWEEK(CURDATE()), '"')) AS data
    FROM mytable 
) AS d;

In general, when you store data in a non-relational manner, the way to optimize it is to organize the data to support a specific query.

Thank you for the advice. The only condition is to store the data in that cell. I have the freedom to write the format and I chose JSON because I thought it suits my needs. Been not valid JSON format is my mistake. I'm open to suggestions on what's best and how to implement it. — krisgiyan, Oct 06 '21 at 17:30

Using the key from JSON in query

1 Answers1