Get length of json array in SQL Server 2016

Question

You know about the new JSON_ support in SQL Server 2016 so let's say I have this data in a row

{
  "BaseBoarding": 1,
  "PriceLineStrategy": "PerPersonPerNight",
  "Currency": "EUR",
  "BasePriceLineList": [
    {
      "RoomTypeId": 1,
      "PeriodId": 1,
      "Price": 10.0
    },
    {
      "RoomTypeId": 1,
      "PeriodId": 2,
      "Price": 100.0
    },
    {
      "RoomTypeId": 1,
      "PeriodId": 3,
      "Price": 190.0
    },
    {
      "RoomTypeId": 2,
      "PeriodId": 1,
      "Price": 280.0
    },
    {
      "RoomTypeId": 2,
      "PeriodId": 2,
      "Price": 310.0
    },
    {
      "RoomTypeId": 2,
      "PeriodId": 3,
      "Price": 340.0
    }
  ]
}

How do I get the number of items of "BasePriceLineList" in the most performant way, preferably using the built-in JSON support?

Need to write something like this:

SELECT JSON_ARRLEN(JsonDataCol, '$.BasePriceline') FROM MyTable
WHERE Id = 1

and get 6 as the result.

score 32 · Accepted Answer · answered Apr 18 '17 at 01:49

32

Using a table instead of a variable:

SELECT count(priceLineLists.RoomTypeId)
FROM Mytable
CROSS APPLY OPENJSON (JsonDataCol, N'$.BasePriceLineList')
  WITH (
    RoomTypeId int)
      AS priceLineLists

answered Apr 18 '17 at 01:49

rich kalasky

628
4
8

2

I will mark this as the correct answer because it can be used on multiple rows at once. – Mihail Shishkov May 08 '17 at 13:14

Michael Erickson · Answer 2 · 2020-12-22T02:00:28.933

28

The hypothetical SQL statement:

SELECT JSON_ARRLEN(JsonDataCol, '$.BasePriceline') FROM MyTable

Can be done by the actual statement:

SELECT (SELECT COUNT(*) FROM OPENJSON(JsonDataCol, '$.BasePriceline')) FROM MyTable

edited Dec 22 '20 at 02:00

answered Dec 22 '20 at 01:54

Michael Erickson

4,217
2
23
10

score 12 · Answer 3 · answered Mar 31 '17 at 13:39

You can convert it to a data set and then count the rows:

DECLARE @JSON NVARCHAR(4000) = N'{
  "BaseBoarding": 1,
  "PriceLineStrategy": "PerPersonPerNight",
  "Currency": "EUR",
  "BasePriceLineList": [
    {
      "RoomTypeId": 1,
      "PeriodId": 1,
      "Price": 10.0
    },
    {
      "RoomTypeId": 1,
      "PeriodId": 2,
      "Price": 100.0
    },
    {
      "RoomTypeId": 1,
      "PeriodId": 3,
      "Price": 190.0
    },
    {
      "RoomTypeId": 2,
      "PeriodId": 1,
      "Price": 280.0
    },
    {
      "RoomTypeId": 2,
      "PeriodId": 2,
      "Price": 310.0
    },
    {
      "RoomTypeId": 2,
      "PeriodId": 3,
      "Price": 340.0
    }
  ]
}'

select COUNT(*) 
FROM OPENJSON(@json, N'$.BasePriceLineList')
WITH (RoomTypeID varchar(100) '$.RoomTypeId')

score 5 · Answer 4 · answered Mar 16 '18 at 15:49

This basic need came up for me a few times when doing some ad-hoc data review. So I went ahead and created a tiny scalar function to do this pretty generically. Few assumptions built in, you might need to use an isnull and/or use json_value to pull out a nested array you are trying to examine. Also, I really don't know what the performance impact of this would be at any scale, I've only needed to do this on a few dozen records at a time. Just thought I'd share in case anyone else lands here looking for a fairly generic way of doing this.

NOTE: Used a couple hacky "tricks" in this such as making sure the function throws an error on an invalid JSON array even though throw isn't allowed inside a function. The extra divide by zero is in case anyone has really lax implicit conversion settings and just a pattern I use in other places where a string return would have been valid.

create function array_length (@array nvarchar(max))
returns int as begin
  if (@array is null or isjson(@array) != 1
      or left(@array, 1) + right(@array, 1) <> '[]')
    return 'Invalid JSON array provided to array_length' + (1/0)
  return (select count(*) from openjson(@array))
end

score 1 · Answer 5 · answered Feb 11 '21 at 11:46

1

Lot of digging around and my conclusion is to use this. Remove count if you want to see the queried elements as they were found. Working with SQL Server 2019

SELECT 
COUNT(JSON_QUERY(JsonObject, '$.[0].Region')) AS RegionCount
FROM YourTable

answered Feb 11 '21 at 11:46

Netferret

604
4
15

5

This approach is unreliable. The following returns 1, instead of the expected 0: `select count(json_query('[]', 'strict $'))`. – user2864740 Mar 10 '21 at 22:37

Get length of json array in SQL Server 2016

5 Answers5