Get length of json array in SQL Server 2016

Using a table instead of a variable:

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

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')

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