Counting size of an array inside another JSON array: SQL SERVER

Question

The JSON object structure is:

{
  "store": {
    "storeId":123,
    "operations": {
      "seasons": [
        {
          "storeHours": [{},{},{}]  // consider it has 3 objects
        },
        {
          "storeHours": [{},{}]  //consider it has 2 objects
        }
      ]
    }
  }
}

I want to count the size of "storeHours". I tried with:

DECLARE @count INT = 0;
DECLARE @destination NVARCHAR = (N'$.store.operations.seasons[' +  @count +  N'].storeHours');

Also tried with:

DECLARE @destination NVARCHAR = CONCAT(N'$.store.operations.seasons[',  @count, N'].storeHours');

DECLARE @storeHoursCount INT = (
   select count(A.[key]) as [count] 
   from (VALUES(@json)) V(J)
   CROSS APPLY OPENJSON(V.J) WITH(
      [storeHours] nvarchar(MAX) @destination  AS JSON) S
   CROSS APPLY OPENJSON(S.storeHours) A
);

I get an error:

Incorrect syntax near '@destination'

This works:

DECLARE @storeHoursCount INT = (
   select count(A.[key]) as [count] 
   from (VALUES(@json)) V(J)
   CROSS APPLY OPENJSON(V.J) WITH (
      [storeHours] nvarchar(MAX) '$.store.operations.seasons[0].storeHours' AS JSON
   ) S
   CROSS APPLY OPENJSON(S.storeHours) A
);

But I want it to be dynamic. Is there something that I am missing? Also what can be the reason for CONCAT() not working?

EDIT: @Zhorov solution works really good when we want over all count of storeHours present. i.e Sum of all storeHours present in all seasons.

My requirement is to get count of storeHours based upon index season (eg: season[0]). How can this be achieved?

seasons is also a JSON array, so you have nested JSON arrays. Which count do you want? — Zhorov
– Zhorov, Commented Apr 1, 2021 at 6:38
Why are you using JSON like that instead of proper tables? Shop working hours is a pretty mundane, static schema. Using JSON only adds delays, not flexibility — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 1, 2021 at 6:46
I am sending a JSON from Mule 4. It is quite flexible to work with JSON on that platform. — Satyam Pisal
– Satyam Pisal, Commented Apr 1, 2021 at 7:42

Zhorov · Accepted Answer · 2021-04-29 21:11:07Z

2

Answer:

If I understand you correctly and you want to count all items in all nested $.storeHours arrays, the following approach is an option:

JSON:

DECLARE @count INT = 0;
DECLARE @destination nvarchar(max) = N'
{
  "store": {
    "storeId":123,
    "operations": {
      "seasons": [
        {
          "storeHours": []
        }
      ]
    }
  }
}'

Statement:

SELECT @count = COUNT(*)
FROM OPENJSON(@destination, '$.store.operations.seasons') j1
CROSS APPLY OPENJSON(j1.[value], '$.storeHours') j2

SELECT @count

As an additional note, always specify the length of an nvarchar variable. When the length is not specified in a variable declaration statement, the default length is 1. In your case, the actual value of the @destination variable is simply $.

Update:

If you want to ...to get count of storeHours based upon index season ..., simply use the appropriate WHERE clause:

DECLARE @count INT = 0;
DECLARE @destination nvarchar(max) = N'
{
  "store": {
    "storeId":123,
    "operations": {
      "seasons": [
        {
          "storeHours": [{},{},{}]
        },
        {
          "storeHours": [{},{}] 
        }
      ]
    }
  }
}'

SELECT @count = COUNT(*)
FROM OPENJSON(@destination, '$.store.operations.seasons') j1
CROSS APPLY OPENJSON(j1.[value], '$.storeHours') j2
WHERE CONVERT(int, j1.[key]) = 0

edited Apr 29, 2021 at 21:11

answered Apr 1, 2021 at 6:46

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

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

3 Comments

Satyam Pisal Over a year ago

Thanks, It helps. Also for the info related to nvarchar

Satyam Pisal Over a year ago

Hi @Zhorov, can you please go through the edited part and help me?

Zhorov Over a year ago

@SatyamPisal Check the updated answer. Thanks,

Collectives™ on Stack Overflow

Counting size of an array inside another JSON array: SQL SERVER

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related