能否通过SQL查询提取并对比数据库嵌套JSON字符串列内的元素？

完全可以实现，不同数据库都内置了对应的JSON处理函数，可直接对存储为text类型的JSON字符串做解析、字段提取和比对，具体方法如下：

先将request body列存储的JSON格式字符串解析为数据库可识别的JSON结构化对象，再按JSON的层级路径提取出存储的unique-id值，和你持有的标识做等值匹配即可筛选出目标记录。

假设你要匹配的unique-id在JSON的最外层，键名为unique-id，如果是嵌套结构调整对应路径即可。

MySQL 5.7及以上版本 #

可使用JSON_EXTRACT函数或->>运算符提取值：

-- 完整写法
SELECT * FROM 你的表名
WHERE JSON_UNQUOTE(JSON_EXTRACT(`request body`, '$."unique-id"')) = '你持有的unique-id值';

-- 简化写法，->> 等价于JSON_UNQUOTE + JSON_EXTRACT
SELECT * FROM 你的表名
WHERE `request body`->>'$."unique-id"' = '你持有的unique-id值';

如果是嵌套结构，比如JSON格式为{"data": {"user": {"unique-id": "xxx"}}}，路径改为'$.data.user."unique-id"'即可。

PostgreSQL #

需要先将text类型转为JSON/JSONB类型，再用运算符提取值：

SELECT * FROM 你的表名
WHERE ("request body"::json)->>'unique-id' = '你持有的unique-id值';

嵌套结构写法示例：("request body"::json)->'data'->'user'->>'unique-id'。

SQL Server 2016及以上版本 #

使用内置的JSON_VALUE函数提取值：

SELECT * FROM 你的表名
WHERE JSON_VALUE([request body], '$.\"unique-id\"') = '你持有的unique-id值';

• 若request body列存在不合法的JSON格式内容，会导致解析报错，查询前建议先校验列内容的JSON合法性
• 数据量较大时全表解析JSON的查询效率较低，可根据业务需求选择以下方案优化：
  • 将高频查询的unique-id字段单独提取为独立列，加索引提升查询速度
  • 将request body列的存储类型改为对应数据库的原生JSON/JSONB类型，给要查询的JSON路径添加专门的JSON索引
• 如果JSON的键名包含横杠、空格等特殊字符，需要给键名加双引号包裹避免解析错误

内容的提问来源于stack exchange，提问作者coco-candy