How to select a nested item of json datatype?

October 19, 2022

I have a MySQL table named computers with a column named details that is json data type formatted. I’ve inserted a value like this in that column:

'{ "name": "Chrome", "os": "Windows", "resolution": { "x": 1680, "y": 1050 } }'

I can simple get Chrome value by the following query:

SELECT details ->> '$.name' FROM computers

Now I want to know, how can I get 1680 value (which is the value of x) ?

>Solution :

You can alter your query to:

SELECT details ->> '$.resolution.x' AS details FROM computers

Or use JSON_EXTRACT:

SELECT JSON_EXTRACT(details, '$.resolution.x') AS details FROM computers

Result:

| details  |
|----------|
| 1680     |

Fiddle here.

Note: As mentioned above, its not good practice to store information you plan on retrieving in a JSON column, create a dedicated value to store this information.