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