MySQL JSON : How to select MIN() value

Question

我有一个 MySQL 表:组合列是 JSON 数据类型

id | combo
1  | {"qty": "2", "variations": [{"name": "Cover", "value": "Paperback"}], "price": "14.00"}
2  | {"qty": "1", "variations": [{"name": "Cover", "value": "Hardback"}], "price": "7.00"}
3  | {"qty": "1", "variations": [{"name": "Cover", "value": "Paperback"}], "price": "15.00"}

我正在尝试获取 MIN()价格7.00但由于它们是字符串，它返回 14.00 .

这可以做到吗？这是我尝试过的:

SELECT           
JSON_UNQUOTE(MIN(combo->'$.price')) AS min_price
FROM itemListings
GROUP BY id

我还尝试删除存储价格周围的报价，但结果相同。

Answer 1

您的代码为您提供了字典最小值；对字符串进行排序时，"1" 位于 "7" 之前，尽管字符串是 "14.00" 和 "7:00 "，就像 "apple" 出现在 "bat" 之前，尽管 "apple" 比 "bat 长“。

您想要数字最小值，因此将该值转换为十进制数:

SELECT
    id, -- you probably want the select the grouped by value too
    MIN(CAST(combo->'$.price' AS DECIMAL(10,2))) AS min_price
FROM itemListings
GROUP BY id