如何在SQL查询中将JSON字段转换为表格格式

你现有表的结构假设表名为your_table，包含三个字段：ID（表主键）、Content（JSON类型字段）、Date（日期字段），JSON字段结构和你给出的示例一致，不同数据库的具体实现语法如下：

1. MySQL 实现（8.0及以上版本） #

• 提取JSON顶级字段用JSON_EXTRACT或者->运算符，数组展开用JSON_TABLE函数

SELECT 
  t.ID,
  t.Date,
  JSON_UNQUOTE(JSON_EXTRACT(t.Content, '$.Id')) AS json_id,
  JSON_UNQUOTE(JSON_EXTRACT(t.Content, '$.Name')) AS json_name,
  sc.subcontent1,
  sc.subcontent2
FROM your_table t
-- 展开SubContent数组
JOIN JSON_TABLE(
  t.Content,
  '$.SubContent[*]' COLUMNS (
    subcontent1 VARCHAR(255) PATH '$.subcontent1',
    subcontent2 VARCHAR(255) PATH '$.subcontent2'
  )
) AS sc;

• 如果你使用的是不支持JSON_TABLE的MySQL 5.x版本，可以用JSON_EXTRACT配合数组下标查询，仅适合数组长度固定的场景。

2. PostgreSQL 实现 #

• 顶级字段直接用->>运算符获取文本值，数组展开用jsonb_array_elements（如果Content是json类型就替换为json_array_elements）

SELECT
  t.ID,
  t.Date,
  t.Content->>'Id' AS json_id,
  t.Content->>'Name' AS json_name,
  jsonb_extract_path_text(sc.elem, 'subcontent1') AS subcontent1,
  jsonb_extract_path_text(sc.elem, 'subcontent2') AS subcontent2
FROM your_table t
-- 展开SubContent数组
CROSS JOIN jsonb_array_elements(t.Content->'SubContent') AS sc(elem);

3. SQL Server 实现 #

• 用OPENJSON函数解析JSON，配合WITH子句定义返回字段

SELECT
  t.ID,
  t.Date,
  json_values.json_id,
  json_values.json_name,
  sc.subcontent1,
  sc.subcontent2
FROM your_table t
-- 先提取顶级字段
CROSS APPLY OPENJSON(t.Content)
WITH (
  json_id VARCHAR(255) '$.Id',
  json_name VARCHAR(255) '$.Name',
  SubContent NVARCHAR(MAX) '$.SubContent' AS JSON
) AS json_values
-- 再展开数组
CROSS APPLY OPENJSON(json_values.SubContent)
WITH (
  subcontent1 VARCHAR(255) '$.subcontent1',
  subcontent2 VARCHAR(255) '$.subcontent2'
) AS sc;

4. Oracle 实现（12c及以上版本） #

• 用JSON_VALUE提取顶级字段，JSON_TABLE展开数组

SELECT
  t.ID,
  t."Date",
  JSON_VALUE(t.Content, '$.Id') AS json_id,
  JSON_VALUE(t.Content, '$.Name') AS json_name,
  sc.subcontent1,
  sc.subcontent2
FROM your_table t,
JSON_TABLE(t.Content, '$.SubContent[*]'
  COLUMNS (
    subcontent1 VARCHAR2(255) PATH '$.subcontent1',
    subcontent2 VARCHAR2(255) PATH '$.subcontent2'
  )
) sc;

通用注意事项 #

• 可以根据实际业务需要调整字段的返回类型，比如数值类型可以替换为INT等
• 如果JSON数组可能为空，把JOIN换成LEFT JOIN即可，不同数据库左连接JSON表函数的语法略有差异，可以根据你使用的数据库对应调整。

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