SQL Query to get the count of Json Array

Question

I have the below Json object. How do I get the count of Object Array.

{
  "Model": [
    {
  "ModelName": "Test Model",    
  "Object": [
     {
       "ID": 1,
       "Name": "ABC"           

     },
     {
       "ID": 11,
       "Name": "ABCD"          
    },         
 ]  
 }]}

I tried the below query but seems JSON_Length was not available.

SELECT ModelName,
       JSON_LENGTH(JsonData, '$.Model[0].Object')
    FROM TabA

The expected output should be

 ModelName      COUNT
 Test Model      2

The JSON you've provided isn't valid; unless you have valid JSON, you won't be able to use any of the JSON functions. — Thom A
– Thom A ♦, Commented May 14, 2020 at 9:48

Thom A · Accepted Answer · 2020-05-14 09:53:06Z

3

If you have valid JSON (at the moment you have a trailing comma (,_ after one of your closing braces (})), then you could use OPENJSON and COUNT:

DECLARE @JSON nvarchar(MAX) = N'{
  "Model": [
    {
  "ModelName": "Test Model",    
  "Object": [
     {
       "ID": 1,
       "Name": "ABC"           

     },
     {
       "ID": 11,
       "Name": "ABCD"          
    }         
 ]  
 }]}';

SELECT M.ModelName,
       COUNT(O.[key]) AS [Count]
FROM (VALUES(@JSON))V(J)
     CROSS APPLY OPENJSON(V.J)
                 WITH(ModelName varchar(20) '$.Model[0].ModelName',
                      [Object] nvarchar(MAX) '$.Model[0].Object' AS JSON) M
     CROSS APPLY OPENJSON(M.[Object]) O
GROUP BY M.ModelName;

answered May 14, 2020 at 9:53

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bas · Accepted Answer · 2024-01-05 09:48:25Z

0

A simplified version of @Thom A's answer use OPENJSON and COUNT:, by eliminating the CROSS APPLYs:

DECLARE @JSON nvarchar(MAX) = N'{
  "Model": [
    {
  "ModelName": "Test Model",    
  "Object": [
     {
       "ID": 1,
       "Name": "ABC"           

     },
     {
       "ID": 11,
       "Name": "ABCD"          
    }         
 ]  
 }]}';

select [ModelName] = JSON_value(@JSON, N'$.Model[0].ModelName')
     , [COUNT]     = count(*) 
  from openJSON(@JSON, N'$.Model[0].Object');

result:

ModelName	COUNT
Test Model	2

answered Jan 5, 2024 at 9:48

Bas

356 bronze badges

Collectives™ on Stack Overflow

SQL Query to get the count of Json Array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related