我有一个像这样的 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 

结果:


评论关闭
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!