mysql查询在包含json数组的列内搜索

I've a column in MySQL database in this format:

{
"monday":[["11:00","19:30"]],
"tuesday":[["11:00","19:30"]],
"wednesday":[["11:00","19:30"]],
"thursday":[["11:00","19:30"]],
"friday":[["11:00","20:00"]],
"saturday":[["11:00","20:00"]],
"sunday":[["11:00","20:00"]]
}

Let's suppose that I would like to know if the restaurant is open now that is friday and is 14:00

how can I make this query? Is there a way?

I tried this query

SELECT id FROM table_name WHERE field_name REGEXP '"key_name":"([^"]*)key_word([^"]*)"';

But I would like to search first for day and then between hours. I'm not able to figure out how to solve it.

Unfortunately there is no way search that type of data with a select query. It would be much faster to restructure your database using proper column types. If you are insistent about keeping your current database layout, you will need to write a a sql function so you can correctly parse the data and return what you want.

It is far from efficient and I discourage you to use this snippet, but here is something that do what you want.

select json
, openday
, start
, end
, opening
, startopen
, endopen
from (
    select json
    , openday
    , start
    , end
    , opening
    , left(opening, locate(',',opening) - 1) as startopen
    , right(opening, locate(',',opening) - 1) as endopen
    from (
        select json
        , openday
        , start
        , end
        , replace(substring(json, start, end - start), '"','') as opening
        from (
            select json
            , openday
            , locate('[[', json, openday) + 2 as start
            , locate(']]', json, openday) as end 
            from (
                select json
                , locate('friday', json) as openday
                from `test-regexp`
            ) as a
        ) as b
    ) as c
) as d
where '14:00' between startopen and endopen

I strongly advise using something else to query that kind of data. A lot of languages already have functions to parse JSON natively.

Note to @Karl: how do you use REGEXP to do that. I can't see how. Thanks