SQL Server 统计多行记录中JSON字段内的数组元素数量

场景说明 #

使用SQL Server 2019时，现有数据表包含id、alias、value三个字段，其中value列存储结构如下的JSON数组：

[
    {
        "num": 1,
        "description": "test 1"
    },
    {
        "num": 2,
        "description": "test 2"
    },
    {
        "num": 3,
        "description": "test 2"
    }
]

需求 #

统计该表每行记录中value字段JSON数组内的num项总数量，期望查询结果格式如下：

id   CountOfNumFromValueColumn
------------------------------
 1             3

可用查询语句 #

经测试，以下SQL语句可适配上述JSON格式，返回符合要求的结果：

SELECT id, count(Num) AS CountOfNumFromValueColumn
FROM MyTable
CROSS APPLY OPENJSON ([value], N'$')
  WITH (
    num int)
WHERE ISJSON([value]) > 0 
GROUP BY id

内容的提问来源于stack exchange，提问作者wingyip