MariaDB从JSON数组提取值如何去掉结果的括号与引号

问题原因 #

原查询使用通配符$[*].Name匹配JSON数组中所有元素的Name字段，返回结果为JSON数组类型，因此自带方括号包裹，字符串值自带双引号。

解决方案 #

方案1：已知Name字段所在数组下标（推荐，性能最优） #

你提供的JSON数组中Name字段位于下标为2的元素（JSON数组下标从0开始计数），可直接指定路径查询，搭配JSON_UNQUOTE去掉引号即可：

SELECT JSON_UNQUOTE(JSON_EXTRACT(jsonParam, '$[2].Name')) FROM tblName WHERE biPK=1;

MySQL 5.7及以上版本支持更简洁的->>运算符，等价于JSON_UNQUOTE(JSON_EXTRACT())：

SELECT jsonParam->>'$[2].Name' FROM tblName WHERE biPK=1;

方案2：不确定Name字段所在数组位置 #

如果Name可能出现在数组任意位置，且只会返回一个匹配值，可通过字符串截取清理结果：

SELECT TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_EXTRACT(jsonParam, '$[*].Name'), '[', -1), ']', 1)) FROM tblName WHERE biPK=1;

MySQL 8.0及以上版本可使用JSON_TABLE将JSON数组转为临时表查询，逻辑更规范：

SELECT j.name 
FROM tblName t,
JSON_TABLE(
  t.jsonParam,
  '$[*]' COLUMNS (
    name VARCHAR(255) PATH '$.Name'
  )
) j
WHERE t.biPK=1 AND j.name IS NOT NULL;

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