title: Counting size of an array inside another JSON array: SQL SERVER
body: <p>The JSON object structure is:</p>
  <pre><code>{
    &quot;store&quot;: {
      &quot;storeId&quot;:123,
      &quot;operations&quot;: {
        &quot;seasons&quot;: [
          {
            &quot;storeHours&quot;: [{},{},{}]  // consider it has 3 objects
          },
          {
            &quot;storeHours&quot;: [{},{}]  //consider it has 2 objects
          }
        ]
      }
    }
  }
  </code></pre>
  <p>I want to count the size of <code>&quot;storeHours&quot;</code>. I tried with:</p>
  <pre><code>DECLARE @count INT = 0;
  DECLARE @destination NVARCHAR = (N'$.store.operations.seasons[' +  @count +  N'].storeHours');
  </code></pre>
  <p>Also tried with:</p>
  <pre><code>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
  );
  </code></pre>
  <p>I get an error:</p>
  <blockquote>
  <p>Incorrect syntax near '@destination'</p>
  </blockquote>
  <p>This works:</p>
  <pre><code>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
  );
  </code></pre>
  <p>But I want it to be dynamic. Is there something that I am missing? Also what can be the reason for <code>CONCAT()</code> not working?</p>
  <p><strong>EDIT:</strong>
  @Zhorov solution works really good when we want over all count of storeHours present. i.e Sum of all storeHours present in all seasons.</p>
  <p>My requirement is to get count of storeHours based upon index season (eg: season[0]).
  How can this be achieved?</p>