如何将含嵌套数组的三维JSON结构转换为SQL Server中的列？

嘿，这个需求我之前处理过，用PostgreSQL的JSON数组拆分函数就能完美解决。假设你的JSON数据存在一个表的jsonb字段里（比如表叫data_table，字段是json_data），直接用下面的SQL语句就能得到你想要的表格结构：

SELECT
  x.value AS "X",
  y.value AS "Y",
  z_inner.value AS "Z"
FROM
  data_table,
  -- 拆分X数组，同时记录每个元素的位置索引
  jsonb_array_elements(json_data->'X') WITH ORDINALITY AS x(value, x_idx),
  -- 拆分Y数组，同样记录索引
  jsonb_array_elements(json_data->'Y') WITH ORDINALITY AS y(value, y_idx),
  -- 先拆Z的外层数组（每一行对应X的一个元素），记录X方向的索引
  jsonb_array_elements(json_data->'Z') WITH ORDINALITY AS z_outer(value, z_x_idx),
  -- 再拆Z的内层数组（每个元素对应Y的一个元素），记录Y方向的索引
  jsonb_array_elements(z_outer.value) WITH ORDINALITY AS z_inner(value, z_y_idx)
WHERE
  -- 关联X元素和Z外层数组的位置
  x.x_idx = z_x_idx
  -- 关联Y元素和Z内层数组的位置
  AND y.y_idx = z_y_idx;

为啥这么写？给你拆解下逻辑： #

• jsonb_array_elements 是PostgreSQL里用来把JSON数组拆成单行记录的工具，加上WITH ORDINALITY就能拿到每个元素在数组里的位置（索引从1开始，这点要注意）。
• 因为你的Z是二维数组，所以得拆两次：先拆外层对应X的每个元素，再拆内层对应Y的每个元素。
• 最后用索引把X、Y、Z的对应元素绑定起来，就能保证每个X-Y组合都对应正确的Z值啦。

直接测试你的示例JSON？没问题： #

如果你想直接用你给的示例数据测试，不用建表也可以，用临时构造的JSON数据就行：

SELECT
  x.value AS "X",
  y.value AS "Y",
  z_inner.value AS "Z"
FROM
  (SELECT jsonb_build_object(
    'X', '["x1","x2","x3"]'::jsonb,
    'Y', '["y1","y2","y3"]'::jsonb,
    'Z', '[["x1y1","x1y2","x1y3"],["x2y1","x2y2","x2y3"],["x3y1","x3y2","x3y3"],["x4y1","x4y2","x4y3"],["x5y1","x5y2","x5y3"]]'::jsonb
  ) AS json_data) AS temp_table,
  jsonb_array_elements(json_data->'X') WITH ORDINALITY AS x(value, x_idx),
  jsonb_array_elements(json_data->'Y') WITH ORDINALITY AS y(value, y_idx),
  jsonb_array_elements(json_data->'Z') WITH ORDINALITY AS z_outer(value, z_x_idx),
  jsonb_array_elements(z_outer.value) WITH ORDINALITY AS z_inner(value, z_y_idx)
WHERE
  x.x_idx = z_x_idx
  AND y.y_idx = z_y_idx;

执行这个查询后，就会得到你想要的表格。注意你示例里Z有5行，但X只有3个元素，所以后面两行（x4、x5对应的Z数据）因为找不到匹配的X元素，会被自动过滤掉，只保留x1到x3的对应行。

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