SQLite中json_extract()提取含特殊字符JSON键值的语法问题

我在名为diff的列中存储了合法的JSON对象，示例如下：

{
  "values_changed": {
    "root['item_status_code']": {
      "new_value": "(",
      "old_value": "-"
    }
  }
}

该JSON的键名root['item_status_code']设计不够简洁，但无法修改底层数据。尝试提取new_value时，原SQL存在语法错误：

SELECT json_extract(diff, '$.values_changed.root['item_status_code'].new_value')
FROM items_diffs
WHERE json_extract(diff, '$.values_changed') IS NOT NULL;

正确写法 #

有两种可行方案解决引号冲突问题：

1. 转义路径中的单引号
   在SQL中，使用两个连续单引号表示一个转义的单引号，修改后的SQL如下：

SELECT json_extract(diff, '$.values_changed.root[''item_status_code''].new_value')
FROM items_diffs
WHERE json_extract(diff, '$.values_changed') IS NOT NULL;

2. 用双引号包裹JSON路径字符串
   如果你的数据库支持（如SQLite、MySQL等），可以用双引号包裹整个JSON路径，内部单引号无需转义：

SELECT json_extract(diff, "$.values_changed.root['item_status_code'].new_value")
FROM items_diffs
WHERE json_extract(diff, '$.values_changed') IS NOT NULL;

错误原因 #

原SQL的语法错误源于JSON路径字符串用单引号包裹时，路径里的'item_status_code'单引号会提前闭合外层的单引号，导致SQL解析器无法识别完整的路径。上述两种方法都能化解引号冲突，正常提取目标值。

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