MySQL JSON:如何选择 MIN() 值 [英] MySQL JSON: How to select MIN() value

问题描述

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

id |组合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"}

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

这能做到吗?这是我尝试过的:

SELECTJSON_UNQUOTE(MIN(combo->'$.price')) AS min_priceFROM itemListings按 ID 分组

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

解决方案

你的代码给了你字典序的最低要求；当对字符串进行排序时，"1" 出现在 "7" 之前，尽管字符串是 "14.00" 和 "7:00"，就像 "apple" 出现在 "bat" 之前一样，尽管 "apple" 比 "bat 长".

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

SELECTid, -- 您可能也希望选择按值分组MIN(CAST(combo->'$.price' AS DECIMAL(10,2))) AS min_priceFROM itemListings按 ID 分组

I have a MySQL table: The combo column is a JSON datatype

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"}

I'm trying to get the MIN() price of 7.00 but as they're strings, it returns 14.00.

Can this be done? Here's what I tried:

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

I also tried removing the quotes around the stored prices but it gave the same results.

解决方案

Your code is giving you the lexicographical minimum; when sorting strings a "1" comes before a "7", despite the strings being "14.00" and "7:00", just like "apple" comes before "bat", despite "apple" being longer than "bat".

You want the numerical minimum, so cast the value to a decimal number:

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

这篇关于MySQL JSON:如何选择 MIN() 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！