SQL 2016 OPENJSON函数使用

 DECLARE @jsonCN NVARCHAR(MAX)='{"branchCode":"769DB","cpCode":"123789","waybillInfoList":[{"additionalServiceList":null,"bizIdList":["39563209437985575"],"consigneeAddress":{"areaName":"南山区","cityName":"深圳市","detailAddress":"广东省 深圳市 南山区 XXX","provinceName":"广东省","townName":null},"consigneeMobile":null,"consigneeName":"小强","consigneePhone":"15888888888","createTime":"2017-07-26 10:17:19","daTouBi":"0752","isAliOrder":"Y","itemList":[{"count":"7","name":"快递袋"}],"msgVersion":"0","orderChannel":"TB","parentWaybillCode":null,"productType":"STANDARD_EXPRESS","routeInfoList":null,"segmentCode":"NORMAL","sellerId":"123456","sendAddress":{"areaName":null,"cityName":"东莞市","detailAddress":"XXX","provinceName":"广东省","townName":null},"senderMobile":"15999999999","senderName":"XX","senderPhone":"020-29177669","status":2,"volume":"0","waybillCode":"12345678910","weight":"5130"}]}'
 --SELECT TOP 1 @jsonCN=data FROM TB_TMSSheetInformation_Push


SELECT [Key],[Value] INTO #KV FROM OPENJSON(@jsonCN)
        INSERT INTO #KV SELECT [Key],[Value] FROM OPENJSON(@jsonCN,'$.waybillInfoList[0]')
        INSERT INTO #KV SELECT [Key],[Value] FROM OPENJSON(@jsonCN,'$.waybillInfoList[0].additionalServiceList[0]')
        INSERT INTO #KV SELECT 'serviceAttributeList_'+[Key] AS 'Key',[Value] FROM OPENJSON(@jsonCN,'$.waybillInfoList[0].additionalServiceList[0].serviceAttributeList[0]')
        INSERT INTO #KV SELECT 'bizIdList_value' AS 'key',[Value] FROM OPENJSON(@jsonCN,'$.waybillInfoList[0].bizIdList')
        INSERT INTO #KV SELECT 'consigneeAddress_'+[key] AS 'key',[Value] FROM OPENJSON(@jsonCN,'$.waybillInfoList[0].consigneeAddress')
        INSERT INTO #KV SELECT 'itemList_'+[Key] AS 'Key',[Value] FROM OPENJSON(@jsonCN,'$.waybillInfoList[0].itemList[0]')
        INSERT INTO #KV SELECT 'routeInfoList_'+[key] AS 'key',[Value] FROM OPENJSON(@jsonCN,'$.waybillInfoList[0].routeInfoList[0]')
        INSERT INTO #KV SELECT 'sendAddress_'+[Key] AS 'key',[Value] FROM OPENJSON(@jsonCN,'$.waybillInfoList[0].sendAddress')

        SELECT * FROM #KV PIVOT (MAX(Value) FOR [Key] IN([branchCode], [cpCode], [waybillInfoList], [additionalServiceList], [bizIdList], [consigneeAddress], [consigneeMobile], [consigneeName], [consigneePhone], [createTime], [daTouBi], [isAliOrder], [itemList], [msgVersion], [orderChannel], [parentWaybillCode], [productType], [routeInfoList], [segmentCode], [sellerId], [sendAddress], [senderMobile], [senderName], [senderPhone], [status], [volume], [waybillCode], [weight], [code], [serviceAttributeList], [serviceAttributeList_name], [serviceAttributeList_value], [bizIdList_value], [consigneeAddress_areaName], [consigneeAddress_cityName], [consigneeAddress_detailAddress], [consigneeAddress_provinceName], [consigneeAddress_townName], [itemList_count], [itemList_name], [routeInfoList_code], [routeInfoList_value], [sendAddress_areaName], [sendAddress_cityName], [sendAddress_detailAddress], [sendAddress_provinceName], [sendAddress_townName])) b
mayb
