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.

Answer 1

Using a table instead of a variable:

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

Answer 2

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