how to measure length of array inside array in json file using SQL OPENJSON function with cross APPLY

Question

    "table":  [
                   {
                       "name":  "Emergency",
                       "columns":  [
                                       {
                                           "name":  "ab",
                                           "type":  "long"
                                       },
                                       {
                                           "name":  "cd",
                                           "type":  "long"
                                       },
                                       {
                                           "name":  "ef",
                                           "type":  "long"
                                       },
                                       {
                                           "name":  "gh",
                                           "type":  "long"
                                       },
                                       
                                   ],
                       "rows":  [
                                    [                                                                             
                                        0.55865,
                                        2.0966,
                                        0.4280,
                                        1.4389
                                    ],
                                    [
                                        
                                        0.42490,
                                        1.5723,
                                        0.3601,
                                        0.8031
                                    ]
                                ]
                   }
               ]
}

so in this json, array object inside rows can be multiple(more than 2). so how to count object of array sinde 'rows' using OPENJSON SQL Server Function.

what I have done is, store this json into temporary table #TempAb and then

 DECLARE @ab VARCHAR(MAX);
 DECLARE @cd VARCHAR(MAX);
 DECLARE @ef VARCHAR(MAX);
 DECLARE @gh VARCHAR(MAX);
SELECT @ab=ab,@cd=cd,@ef=ef,@gh=gh from #TempAb as ab CROSS APPLY
                            OPENJSON(ab.RawData, '$.table') WITH
                            (
                            ab Varchar(MAX) '$.rows[0][0],
                            cd Varchar(Max) '$.rows[0][1],
                            ef Varchar(Max) '$.rows[0][2],
                            gh Varchar(Max) '$.rows[0][3]
);

so this query only returns data of the first object of rows, but I want data of all objects of rows. so I know the count then iterate this inside while loop

Solution:


SET @cnt_total = (SELECT  COUNT(O.[key]) FROM (VALUES(@jsonvalue))V(J)
                                CROSS APPLY OPENJSON(V.J)
                                WITH([Object] nvarchar(MAX) '$.table[0].rows' AS JSON) M
                                CROSS APPLY OPENJSON(M.[Object]) O)

score 0 · Answer 1 · answered Jan 05 '21 at 23:52

First you have to start with valid JSON, then something like this:

declare @json nvarchar(max) = N'
{
    "table":  [
                   {
                       "name":  "Emergency",
                       "columns":  [
                                       {
                                           "name":  "ab",
                                           "type":  "long"
                                       },
                                       {
                                           "name":  "cd",
                                           "type":  "long"
                                       },
                                       {
                                           "name":  "ef",
                                           "type":  "long"
                                       },
                                       {
                                           "name":  "gh",
                                           "type":  "long"
                                       }
                                       
                                   ],
                       "rows":  [
                                    [                                                                             
                                        0.55865,
                                        2.0966,
                                        0.4280,
                                        1.4389
                                    ],
                                    [
                                        
                                        0.42490,
                                        1.5723,
                                        0.3601,
                                        0.8031
                                    ]
                                ]
                   }
               ]
}
'


select count(*) from 
OPENJSON(@json, '$.table[0].rows') d

how to measure length of array inside array in json file using SQL OPENJSON function with cross APPLY

1 Answers1