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.
Using a table instead of a variable:
SELECT count(priceLineLists.RoomTypeId)
FROM Mytable
CROSS APPLY OPENJSON (JsonDataCol, N'$.BasePriceLineList')
WITH (
RoomTypeId int)
AS priceLineLists