OPENJSON and incremental value

Question

I'm trying to parse the following JSON document :

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

However it raises the following exception :

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

I tried several ways using the second parameter of the OPENJSON function, but none has been working. Does someone have an idea ?

Also, the JSON in your question isn't valid. You have multiple trailing commas on the final element in your objects, you have a errant double quote (`"`) at the end, and you don't close your outer object. If you want to be able to parse JSON, it first needs to be *valid* JSON; fix your JSON data first, then worry about the SQL to consume it. — Thom A, Jun 05 '23 at 15:56
Also `1685959230887` is a Unix time, which cannot be directly converted to `datetime2`. But `timestamp` could be a `time` type — Charlieface, Jun 05 '23 at 15:58

score 1 · Answer 1 · answered Jun 05 '23 at 16:02

After fixing up your invalid JSON, it looks like you want this


SELECT
  j1.[key],
  j2.*
FROM OPENJSON(@doc) j1
CROSS APPLY OPENJSON(j1.value)
  WITH (
        start_time bigint
        , [timestamp] time
        , running_time varchar(128)
  ) AS j2;

Use OPENJSON without a schema to get a series of key, value pairs, then APPLY OPENJSON again with a schema to break out each object into properties.

Note also the changes to the data types.

db<>fiddle

Thom A · Answer 2 · 2023-06-05T16:08:15.440

Your JSON isn't valid, which is a problem. For starters, you have a trailing comma after the last key/value pair in each of your inner arrays; for example "running_time": "1.2s",}. You also have a stray double quote (") at the end of your JSON and you don't close the outer object.

For the purposes of this answer, I'm going to "assume" you have valid JSON. If you don't, then you need to fix whatever process is creating the above JSON; it is flawed and creating broken JSON meaning that SQL Server cannot consume it.

This takes a guess of the results you want, but presumable you want one row per object, with a column that includes the value of the name of that object.

One method would be to use 2 calls to OPENJSON; the first puts the "identity" value into a key column and the inner object into another column. Then you can consume that inner object with another call with OPENJSON. This results in the following:

SELECT d.[key],
       v.start_time,
       v.timestamp,
       v.running_time
FROM OPENJSON(@doc) d
     CROSS APPLY OPENJSON(d.[value])
     WITH (start_time bigint,
           timestamp time(0),
           running_time varchar(10)) v;

This produces the following results (with valid JSON):

key	start_time	timestamp	running_time
0	1685959230501	10:00:30	1.2s
1	1685959230502	10:00:30	1.2s
2	1685959230886	10:00:30	889.3ms
3	1685959230887	10:00:30	883.9ms

If you need the column start_time to be an actual date and time data type (datetime2(0)?) there are plenty of duplicates, such as:

OPENJSON and incremental value

2 Answers2