Select a particular value of JSON string using MySQL

Question

I have a table that looks like this

+------+------------------------------------+
|  id  |              details               |
+------+------------------------------------+
|   1  | {"price":"24.99","currency":"USD"} |
+------+------------------------------------+

Is it possible to, with a single MySQL select statement, obtain the value of price 24.99?

Possible duplicate of How to search JSON data in mysql?

pes502
– pes502

2016-03-10 12:52:20 +00:00
Commented Mar 10, 2016 at 12:52 — pes502
– pes502, Commented Mar 10, 2016 at 12:52
Possible duplicate of How to retrieve JSON data from mysql?

rh16
– rh16

2019-04-11 21:45:05 +00:00
Commented Apr 11, 2019 at 21:45 — rh16
– rh16, Commented Apr 11, 2019 at 21:45

Alexander Trakhimenok · Accepted Answer · 2016-03-10 12:56:58Z

1

Yes, you can using JSON_EXTRACT

It probably should be like:

SELECT JSON_EXTRACT(details, "$.price")
FROM table_name

or another form:

SELECT details->"$.price"
FROM table_name

(I don't have MySql to test it)

Note that the price in your JSON stored as a string, not a number and you probably would want to cast it to a DECIMAL.

answered Mar 10, 2016 at 12:56

Alexander Trakhimenok

6,3282 gold badges31 silver badges53 bronze badges

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

1 Comment

Mike Feng Over a year ago

Ahh MySQL 5.7...now to wait for Google Cloud to support it :)

Collectives™ on Stack Overflow

Select a particular value of JSON string using MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related