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?
是否可以使用单个MySQL select语句获取24.99的价格?
1 个解决方案
#1
1
Yes, you can using JSON_EXTRACT
是的,您可以使用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)
(我没有MySql来测试它)
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.
请注意,JSON中的价格存储为字符串,而不是数字,您可能希望将其转换为DECIMAL。
#1
1
Yes, you can using JSON_EXTRACT
是的,您可以使用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)
(我没有MySql来测试它)
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.
请注意,JSON中的价格存储为字符串,而不是数字,您可能希望将其转换为DECIMAL。