使用OPENJSON解析带增量键的JSON时路径格式错误求助

原执行代码如下：

DECLARE @doc nvarchar(max) = '
{
    "0": {
        "start_time": "1685959230501",
        "timestamp": "10:00:30",
        "running_time": "1.2s"
    },
    "1": {
        "start_time": "1685959230502",
        "timestamp": "10:00:30",
        "running_time": "1.2s"
    },
    "2": {
        "start_time": "1685959230886",
        "timestamp": "10:00:30",
        "running_time": "889.3ms"
    },
    "3": {
        "start_time": "1685959230887",
        "timestamp": "10:00:30",
        "running_time": "883.9ms"
    }'

SELECT  *
FROM    OPENJSON(@doc, '$.sql:identity()')
WITH    (
        start_time datetime2(3) N'$.start_time'
        , [timestamp] varchar(128) N'$.timestamp'
        , running_time varchar(128) N'$.running_time'
    ) AS i

注意：原JSON末尾多了一个多余的双引号，属于语法错误，需先删除否则会导致解析失败。

执行时抛出异常：

Msg 13607, Level 16, State 4, Line 24
JSON path is not properly formatted. Unexpected character ':' is found at position 5.

错误原因 #

sql:identity()是OPENJSON的WITH子句中用于获取数组元素索引或对象键名的内置函数，不能作为OPENJSON的第二个路径参数传入，你错误的使用位置导致JSON路径解析失败。

解决方案 #

方案1：解析原对象结构的JSON #

保留原JSON的对象结构（键为0、1、2、3），去掉OPENJSON的第二个路径参数，在WITH子句中通过sql:identity()或$.key获取子对象对应的键名：

DECLARE @doc nvarchar(max) = '
{
    "0": {
        "start_time": "1685959230501",
        "timestamp": "10:00:30",
        "running_time": "1.2s"
    },
    "1": {
        "start_time": "1685959230502",
        "timestamp": "10:00:30",
        "running_time": "1.2s"
    },
    "2": {
        "start_time": "1685959230886",
        "timestamp": "10:00:30",
        "running_time": "889.3ms"
    },
    "3": {
        "start_time": "1685959230887",
        "timestamp": "10:00:30",
        "running_time": "883.9ms"
    }'

SELECT  *
FROM    OPENJSON(@doc)
WITH    (
        id int sql:identity()
        , start_time datetime2(3) N'$.value.start_time'
        , [timestamp] varchar(128) N'$.value.timestamp'
        , running_time varchar(128) N'$.value.running_time'
    ) AS i

也可以用'$.key'替代sql:identity()获取键名，效果一致。

方案2：改为数组结构解析（可选） #

如果可以修改JSON格式为数组，可直接指定路径$，并在WITH中用sql:identity()获取数组索引：

DECLARE @doc nvarchar(max) = '
[
    {
        "start_time": "1685959230501",
        "timestamp": "10:00:30",
        "running_time": "1.2s"
    },
    {
        "start_time": "1685959230502",
        "timestamp": "10:00:30",
        "running_time": "1.2s"
    },
    {
        "start_time": "1685959230886",
        "timestamp": "10:00:30",
        "running_time": "889.3ms"
    },
    {
        "start_time": "1685959230887",
        "timestamp": "10:00:30",
        "running_time": "883.9ms"
    }
]'

SELECT  *
FROM    OPENJSON(@doc)
WITH    (
        index int sql:identity()
        , start_time datetime2(3) N'$.start_time'
        , [timestamp] varchar(128) N'$.timestamp'
        , running_time varchar(128) N'$.running_time'
    ) AS i

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