CODEWITHSUNDEEP
mysqldatabasemysql-json
krisgiyan
krisgiyan

Reputation: 17

Using the key from JSON in query

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.

Upvotes: 0

Views: 47

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562631

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.

Upvotes: 1

Related Questions