MySQL提取JSON数组中food对应price_f值如何去除结果外层引号？

问题根因 #

你使用$**.price_f递归通配符执行JSON路径查询时，MySQL会将所有匹配结果统一封装为JSON数组返回，哪怕仅命中1条结果。->>和JSON_UNQUOTE仅能对JSON标量值做转义解引用，无法直接提取数组内部的元素值，因此会返回带括号的数组字符串格式。

解决方案 #

方案1：固定单匹配场景直接取数组首元素

如果你确认每行数据的products数组中仅有1个item为food的元素，可以直接取返回数组的第0位元素，语句如下：

-- 如需数值类型可在外层加 CAST(... AS UNSIGNED) 转换
SELECT `column`->>'$**.price_f[0]' as Price FROM `table`
WHERE JSON_CONTAINS(`column`, '{"item":"food"}', '$.products');

执行后返回结果为纯文本的100、200。

方案2：多匹配场景用JSON_TABLE拆分查询

如果存在一行数据中有多个food类商品的场景，使用JSON_TABLE将JSON数组拆分为关系型行数据再过滤，兼容性更强：

SELECT j.price_f as Price 
FROM `table`,
JSON_TABLE(
  `column`->'$.products',
  '$[*]' COLUMNS (
    item VARCHAR(20) PATH '$.item',
    price_f VARCHAR(10) PATH '$.price_f'
  )
) j
WHERE j.item = 'food';

该方法会将每个商品单独拆分为一行，过滤后直接拿到对应的price_f原始值，无需额外处理JSON格式。

内容的提问来源于stack exchange，提问作者Ashonko