SQL中如何将存储在单列的字典格式数据拆分到多列

前提假设 #

我们统一约定：

• 存储JSON数据的表名为merchant_settings
• 存储JSON字典的列名为feature_config
• 行号No.通过数据库内置序号函数生成

PostgreSQL 实现 #

PostgreSQL对JSON类型支持友好，直接使用->>操作符提取键值即可，键不存在时自动返回NULL：

SELECT
  ROW_NUMBER() OVER () AS "No.",
  feature_config ->> 'messaging' AS messaging,
  feature_config ->> 'newsletters' AS newsletters,
  feature_config ->> 'ranking_coach' AS ranking_coach,
  feature_config ->> 'sms_reminders' AS sms_reminders,
  -- 按需补充其他所有键即可
  feature_config ->> 'square' AS square,
  feature_config ->> 'reporting' AS reporting
FROM merchant_settings;

如果你的列是jsonb类型，性能会更优，语法完全一致。

MySQL 实现 #

MySQL 5.7及以上版本支持JSON操作，使用->>（等价于JSON_UNQUOTE(JSON_EXTRACT())）提取值：

SELECT
  ROW_NUMBER() OVER () AS `No.`,
  feature_config ->> '$.messaging' AS messaging,
  feature_config ->> '$.newsletters' AS newsletters,
  feature_config ->> '$.ranking_coach' AS ranking_coach,
  feature_config ->> '$.sms_reminders' AS sms_reminders,
  -- 补充其他键
  feature_config ->> '$.square' AS square
FROM merchant_settings;

SQL Server 实现 #

使用JSON_VALUE函数提取对应路径的值：

SELECT
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [No.],
  JSON_VALUE(feature_config, '$.messaging') AS messaging,
  JSON_VALUE(feature_config, '$.newsletters') AS newsletters,
  JSON_VALUE(feature_config, '$.ranking_coach') AS ranking_coach,
  JSON_VALUE(feature_config, '$.sms_reminders') AS sms_reminders
FROM merchant_settings;

批量生成查询语句（键较多时使用） #

如果JSON包含的键数量很多，手动写所有列效率太低，可以先查询所有唯一的JSON键，动态拼接查询语句，以下是PostgreSQL的示例：

-- 第一步：查询所有唯一的JSON键
SELECT string_agg('feature_config ->> ''' || key || ''' AS ' || quote_ident(key), ', ')
FROM (
  SELECT DISTINCT jsonb_object_keys(feature_config) AS key
  FROM merchant_settings
) t;

将查询得到的字段列表替换到上面的SELECT语句中即可，无需手动整理所有键。

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