时间戳

首页 > 解决方案 > 将 Json 嵌套对象数组转换为表行

sql - 将 Json 嵌套对象数组转换为表行

问题描述

我有一个像这样的json：

[
  {
    "Id": "1234",
    "stockDetail": [
      {
        "Number": "10022_1",
        "Code": "500"
      },
      {
        "Number": "10022_1",
        "Code": "600"
      }
    ]
  },
  {
    "Id": "1235",
    "stockDetail": [
      {
        "Number": "10023_1",
        "Code": "100"
      },
      {
        "Number": "10023_1",
        "Code": "100"
      }
    ]
  }
]

如何在下面的sql表中转换它：

+------+---------+------+
|  Id  | Number  | Code |
+------+---------+------+
| 1234 | 10022_1 |  500 |
| 1234 | 10022_1 |  600 |
| 1235 | 10023_1 |  100 |
| 1235 | 10023_1 |  100 |
+------+---------+------+

标签： sqljsonsql-servertsql

解决方案

在您的情况下，您可以尝试将 JSON 子节点与父节点交叉应用：

DECLARE @json nvarchar(max)
SET @json = N'
[
  {
    "Id": "1234",
    "stockDetail": [
      {
        "Number": "10022_1",
        "Code": "500"
      },
      {
        "Number": "10022_1",
        "Code": "600"
      }
    ]
  },
  {
    "Id": "1235",
    "stockDetail": [
      {
        "Number": "10023_1",
        "Code": "100"
      },
      {
        "Number": "10023_1",
        "Code": "100"
      }
    ]
  }
]'

SELECT
    JSON_Value (i.value, '$.Id') as ID, 
    JSON_Value (d.value, '$.Number') as [Number], 
    JSON_Value (d.value, '$.Code') as [Code]
FROM OPENJSON (@json, '$') as i
CROSS APPLY OPENJSON (i.value, '$.stockDetail') as d

输出：

ID      Number  Code
1234    10022_1 500
1234    10022_1 600
1235    10023_1 100
1235    10023_1 100

推荐阅读