Querying a nested JSON field in MySQL

Question

I have a JSON field in MySQL with the following structure:

{
    "menu": {
        "reports": {
            "checked": 1
        }
    },
    "survey": {
        "5": {
            "checked": 1
        }
    },
    "location": {
        "208": {
            "checked": 1,
            "satisfied": 1
        }
    }
}

I want to get records where survey.5.checked=1.
The following works fine:

select email,filters,filters->'$.survey' as survey from users

and this works:

select email,filters,filters->'$.survey.reports' as reports from users

but this fails:

select email,filters,filters->'$.survey.5' as survey from users

and this fails:

select email,filters,filters->'$.survey.5.checked' as survey from users

How can I query the JSON field for records where survey.5.checked = 1 ? Are you not able to use numbers as keys in your JSON structure?

GMB · Accepted Answer · 2020-02-21 14:17:12Z

1

You need to double-quote the all-digit key (similarily, if the key contains spaces, it also needs to be double-quoted).

select email, filters, filters -> '$.survey."5".checked' from users

If you actually want to filter:

select email, filters
from users
where filters -> '$.survey."5".checked' = 1

answered Feb 21, 2020 at 14:17

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Querying a nested JSON field in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related