我有一个像这样的 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 |
+------+---------+------+
请您参考如下方法:
如果您需要定义类型化列,您可以使用带有 WITH
子句的 OPENJSON
:
DECLARE @j nvarchar(max) = 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 father.Id, child.Number, child.Code
from openjson (@j)
with (
Id int,
stockDetail nvarchar(max) as json
) as father
cross apply openjson (father.stockDetail)
with (
Number nvarchar(100),
Code nvarchar(100)
) as child
结果: