MySQL如何从嵌套JSON字符串中提取指定的id字段值？

问题根源 #

你之前的写法返回NULL的核心原因是：[]数字索引仅适用于JSON数组类型的节点，而你提供的JSON结构中Elements下的所有子节点均为键值对对象结构，不存在数字索引，因此路径匹配失败。

正确提取写法 #

已知各级键名的场景 #

对于包含特殊字符（反斜杠）、数字开头的键名，需要在JSON路径中用双引号包裹键名：

-- 通用兼容写法，适配所有支持JSON_EXTRACT的数据库
TRIM(BOTH '"' FROM JSON_EXTRACT(info, '$.Elements."New\\Old\\Used\\Sold\\Blank"."123456".id')) AS id

如果使用MySQL 5.7及以上版本，可以用->>运算符直接实现提取+去引号，无需手动trim：

-- MySQL专属简化写法
info->>'$.Elements."New\\Old\\Used\\Sold\\Blank"."123456".id' AS id

键名动态不确定的场景 #

如果Elements下的子键名、下一级的数字键名是动态变化的，可以通过JSON_KEYS先动态获取键名再拼接路径，示例（MySQL环境）：

SELECT 
  info->>CONCAT(
    '$.Elements.',
    JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(info, '$.Elements'), '$[0]')),
    '.',
    JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(info, CONCAT('$.Elements.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(info, '$.Elements'), '$[0]')))), '$[0]')),
    '.id'
  ) AS id
FROM 你的表名

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