1

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?

Mike Feng
  • 803
  • 2
  • 9
  • 19
  • Possible duplicate of [How to search JSON data in mysql?](http://stackoverflow.com/questions/30411210/how-to-search-json-data-in-mysql) – pes502 Mar 10 '16 at 12:52
  • 1
    Possible duplicate of [How to retrieve JSON data from mysql?](https://stackoverflow.com/questions/15701579/how-to-retrieve-json-data-from-mysql) – rh16 Apr 11 '19 at 21:45

1 Answers1

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.

Alexander Trakhimenok
  • 6,019
  • 2
  • 27
  • 52