SQL解析含特殊引号的JSON字段报错问题求助

问题说明 #

原始metadata数据格式如下：

metadata 
-----------
{'a': 'jay', 'b': '100', 'c': ""ANAND'S STORE"", 'd': '200'}

尝试使用json_extract_path_text(metadata, 'c') as store_name解析时，出现错误：Error parsing JSON: more than one document in the input，期望解析后得到如下格式结果：

a       b          c             d
----------------------------------------
jay | 100   |  ANAND'S STORE |  200

问题根源：原始数据不是标准JSON格式——标准JSON要求用双引号包裹键和字符串，而这里用了单引号，且字段c的字符串嵌套了多余双引号，导致JSON解析器无法正常识别。

MySQL解决方案 #

先通过字符串替换将原始数据转换为标准JSON，再用JSON函数解析：

方法1：使用JSON_EXTRACT + JSON_UNQUOTE

SELECT
  JSON_UNQUOTE(JSON_EXTRACT(cleaned_metadata, '$.a')) AS a,
  JSON_UNQUOTE(JSON_EXTRACT(cleaned_metadata, '$.b')) AS b,
  JSON_UNQUOTE(JSON_EXTRACT(cleaned_metadata, '$.c')) AS c,
  JSON_UNQUOTE(JSON_EXTRACT(cleaned_metadata, '$.d')) AS d
FROM (
  -- 替换单引号为双引号，同时去除字段c的多余双引号
  SELECT REPLACE(REPLACE(metadata, '''', '"'), '""', '"') AS cleaned_metadata
  FROM your_table
) t;

方法2：使用JSON_VALUE（MySQL 8.0及以上版本支持）

SELECT
  JSON_VALUE(cleaned_metadata, '$.a') AS a,
  JSON_VALUE(cleaned_metadata, '$.b') AS b,
  JSON_VALUE(cleaned_metadata, '$.c') AS c,
  JSON_VALUE(cleaned_metadata, '$.d') AS d
FROM (
  SELECT REPLACE(REPLACE(metadata, '''', '"'), '""', '"') AS cleaned_metadata
  FROM your_table
) t;

PostgreSQL解决方案 #

先修正格式为标准JSON，再用PostgreSQL的JSON类型解析：

方法1：使用->>运算符（推荐）

SELECT
  cleaned_metadata ->> 'a' AS a,
  cleaned_metadata ->> 'b' AS b,
  cleaned_metadata ->> 'c' AS c,
  cleaned_metadata ->> 'd' AS d
FROM (
  -- 修正格式并转换为jsonb类型
  SELECT REPLACE(REPLACE(metadata, '''', '"'), '""', '"')::jsonb AS cleaned_metadata
  FROM your_table
) t;

方法2：使用json_extract_path_text

SELECT
  json_extract_path_text(cleaned_metadata, 'a') AS a,
  json_extract_path_text(cleaned_metadata, 'b') AS b,
  json_extract_path_text(cleaned_metadata, 'c') AS c,
  json_extract_path_text(cleaned_metadata, 'd') AS d
FROM (
  SELECT REPLACE(REPLACE(metadata, '''', '"'), '""', '"')::json AS cleaned_metadata
  FROM your_table
) t;

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