如何拆分逗号分隔字符串并从JSON列custom_fields提取指定字段？

问题说明 #

我有一个逗号分隔的字符串"First_Name, Last_Name, Age"，其中的内容是需要从表my_table的JSON列custom_fields中提取的字段名。需要拆分该字符串，提取对应JSON字段并得到格式化输出。

示例JSON列数据 #

custom_fields
{
"First_Name": "Vaibhav",
"Last_Name": "Terli",
"Age": "24"
}

期望输出 #

First_Name | Last_Name | Age
---------------------------
Vaibhav   | Terli     | 24

解决方案 #

不同数据库的JSON处理语法不同，以下是主流数据库的实现方式：

1. PostgreSQL #

静态提取（字段名固定时）

直接通过->>操作符提取指定字段：

SELECT
  custom_fields->>'First_Name' AS "First_Name",
  custom_fields->>'Last_Name' AS "Last_Name",
  custom_fields->>'Age' AS "Age"
FROM my_table;

动态提取（基于传入的逗号分隔字符串）

通过PL/pgSQL函数实现动态生成查询：

CREATE OR REPLACE FUNCTION extract_json_fields(field_list text)
RETURNS TABLE (result record) AS $$
DECLARE
  fields text[];
  query text;
BEGIN
  -- 拆分并清理字段列表
  fields := string_to_array(trim(both '"' from field_list), ', ');
  -- 拼接查询语句
  query := 'SELECT ' || string_agg('custom_fields->>' || quote_literal(f) || ' AS ' || quote_ident(f), ', ') || ' FROM my_table';
  RETURN QUERY EXECUTE query;
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT * FROM extract_json_fields('"First_Name, Last_Name, Age"');

2. MySQL #

静态提取

使用->>操作符提取JSON路径对应的字段：

SELECT
  custom_fields->>'$.First_Name' AS First_Name,
  custom_fields->>'$.Last_Name' AS Last_Name,
  custom_fields->>'$.Age' AS Age
FROM my_table;

动态提取

通过预处理语句动态生成查询：

SET @field_list = 'First_Name, Last_Name, Age';
-- 拼接字段提取语句
SET @fields = CONCAT(REPLACE(@field_list, ', ', '->>\'$.\', '), '->>\'$.\'');
SET @query = CONCAT('SELECT ', @fields, ' FROM my_table');
-- 执行动态查询
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. SQL Server #

静态提取

用JSON_VALUE函数提取指定路径的字段值：

SELECT
  JSON_VALUE(custom_fields, '$.First_Name') AS First_Name,
  JSON_VALUE(custom_fields, '$.Last_Name') AS Last_Name,
  JSON_VALUE(custom_fields, '$.Age') AS Age
FROM my_table;

动态提取

通过动态SQL结合STRING_SPLIT实现：

DECLARE @field_list NVARCHAR(MAX) = 'First_Name, Last_Name, Age';
DECLARE @fields NVARCHAR(MAX);

-- 拆分字段列表并拼接提取语句
SELECT @fields = STRING_AGG('JSON_VALUE(custom_fields, ''$.' + TRIM(value) + ''') AS ' + QUOTENAME(TRIM(value)), ', ')
FROM STRING_SPLIT(@field_list, ',');

-- 执行动态查询
DECLARE @query NVARCHAR(MAX) = 'SELECT ' + @fields + ' FROM my_table';
EXEC sp_executesql @query;

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