mysql query to search inside column containing json array

Question

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.

You can use MySQL's REGEXP to search the data: dev.mysql.com/doc/refman/5.1/en/regexp.html — Karl
– Karl, Commented Jan 9, 2015 at 14:12
what do you mean John? I got this data from an external source. — Luca Coppola
– Luca Coppola, Commented Jan 9, 2015 at 14:12
Thank You Karl.. I know this.. but I'm not able to create a query — Luca Coppola
– Luca Coppola, Commented Jan 9, 2015 at 14:12
If you know this, then surely you've TRIED something, what have you tried? — Karl
– Karl, Commented Jan 9, 2015 at 14:12

geggleto · Accepted Answer · 2015-01-09 14:35:07Z

0

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.

answered Jan 9, 2015 at 14:35

geggleto

2,6251 gold badge17 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karl Over a year ago

WRONG: You can use REGEXP to do this.

A.D. · Accepted Answer · 2015-01-09 15:13:56Z

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

Collectives™ on Stack Overflow

mysql query to search inside column containing json array

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related