1

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) ?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Martin AJ
  • 6,261
  • 8
  • 53
  • 111
  • 1
    While it is possible to query within json data, you'll find it's awkward and possibly the singled slowest thing you can do in your database. You'll do **SO MUCH BETTER** if you update your schema and extract the relevant parts of the json data into dedicated columns as part of the INSERT/UPDATE process. – Joel Coehoorn Oct 19 '22 at 19:09
  • Yeah right @JoelCoehoorn thakns. I just wanted to know the syntax – Martin AJ Oct 19 '22 at 19:17
  • 1
    If you need to work with JSON in MySQL, I recommend you read the documentation and examples in the manual: [JSON data type](https://dev.mysql.com/doc/refman/8.0/en/json.html) and [JSON functions](https://dev.mysql.com/doc/refman/8.0/en/json-functions.html), and practice them. That will answer most of your questions more fully and more quickly than asking on Stack Overflow. – Bill Karwin Oct 19 '22 at 19:30

1 Answers1

1

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.

griv
  • 2,098
  • 2
  • 12
  • 15