如何解析存储为Varchar类型的键值对列并提取数据

只要列内存储的字符串符合标准JSON语法，不需要修改字段类型，直接通过数据库内置的JSON处理函数即可完成取值，支持嵌套键提取，具体写法按你使用的数据库对应选择即可：

• 提前校验：取值前建议先排查脏数据，筛选出不符合JSON格式的记录提前处理，避免解析函数报错，可先通过WHERE 列名 NOT LIKE '{%}'初步筛掉格式明显异常的值。

各数据库具体写法 #

MySQL 5.7+ #

支持将varchar类型值显式转为JSON类型后解析，也可使用->>简写语法直接返回无转义的文本值：

-- 表名: employees  存储类JSON的varchar列名: emp_attr
-- 提取顶层键值，对应示例数据{ "Employee":"John", "EmployeeID":"1", "Role":"Marketing" }
SELECT
  JSON_EXTRACT(CAST(emp_attr AS JSON), '$.Employee') AS employee_name,
  emp_attr->>'$.EmployeeID' AS emp_id, -- 简写写法，自动去除值包裹的双引号
  emp_attr->>'$.Role' AS role
FROM employees;

-- 提取嵌套键值 示例: 存储内容含 {"DeptInfo":{"DeptName":"Digital","Level":3}}
SELECT emp_attr->>'$.DeptInfo.DeptName' AS dept_name FROM employees;

PostgreSQL #

通过::jsonb语法做类型转换，用->>操作符提取文本值：

-- 提取顶层键值
SELECT
  (emp_attr::jsonb)->>'Employee' AS employee_name,
  (emp_attr::jsonb)->>'EmployeeID' AS emp_id,
  (emp_attr::jsonb)->>'Role' AS role
FROM employees;

-- 提取嵌套键值 示例: 存储内容含 {"Contact":{"Email":"john@demo.com","Phone":"123"}}
SELECT (emp_attr::jsonb)->'Contact'->>'Email' AS contact_email FROM employees;

SQL Server #

直接使用JSON_VALUE函数解析合法JSON格式的字符串，无需显式类型转换：

-- 提取顶层键值
SELECT
  JSON_VALUE(emp_attr, '$.Employee') AS employee_name,
  JSON_VALUE(emp_attr, '$.EmployeeID') AS emp_id,
  JSON_VALUE(emp_attr, '$.Role') AS role
FROM employees;

-- 提取嵌套键值 示例: 存储内容含 {"Address":{"City":"Shanghai","District":"Pudong"}}
SELECT JSON_VALUE(emp_attr, '$.Address.City') AS city FROM employees;

兜底应急方案（不推荐） #

如果使用的是无内置JSON解析函数的老旧数据库版本，可通过字符串函数截取匹配取值，该方法容错率极低，键值顺序变化、值内包含引号都会导致结果错误，仅作临时应急使用：

-- MySQL 老版本取Employee字段值示例
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(emp_attr, '"Employee":"', -1), '"', 1) AS employee_name
FROM employees;

优化建议：如果该类JSON键值查询是业务高频操作，建议将该varchar字段修改为对应数据库的原生JSON/JSONB类型，一方面可以对常用查询键建索引提升查询性能，另一方面数据库会自动校验写入内容的JSON格式合法性，避免脏数据导致的解析报错。

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