通过sql查询读取嵌套Json

发布于07月27日

目前我有这段代码

DECLARE @json NVARCHAR(MAX) 
SET @json = 
N'[
   {
     "objOrg": {
       "EmpIds": [
         {
           "Id": 101
         },
         {
           "Id": 102
         },
         {
           "Id": 103
         }
       ]
     }
   }
 ]'

如何返回以数据透视的EmpId值，例如

Id1	Id2	Id3
101	102	103

推荐答案

您可以使用OPENJSON()和ROW_NUMBER()窗口功能，例如

DECLARE 
    @json   AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);
        
SET @json = 
N'[
   {
     "objOrg": {
       "EmpIds": [
         {
           "Id": 101
         },
         {
           "Id": 102
         },
         {
           "Id": 103
         }
       ]
     }
   }
 ]';
 
 SELECT j.*, ROW_NUMBER() OVER (ORDER BY j.Id) AS rn
   INTO t_json
     FROM OPENJSON(@json)
              WITH (
                    JS NVARCHAR(MAX) '$.objOrg.EmpIds' AS JSON 
                  ) AS j0
    CROSS APPLY OPENJSON (j0.JS) 
                WITH (
                      Id   INT '$.Id' 
                    ) AS j; 
                    
SET @query = CONCAT('SELECT',
              STUFF(
                   (SELECT CONCAT(', MAX(CASE WHEN rn=' , CAST(rn AS VARCHAR) , ' THEN Id END) AS Id', CAST(rn AS VARCHAR))
                      FROM t_json
                     ORDER BY rn 
                       FOR XML PATH(''), type).value('.', 'NVARCHAR(MAX)'),
                   1,1,''
                  ),' FROM t_json');

EXECUTE(@query)

Demo