OPENJSON and incremental value

NicolasSouquet
June 5, 2023
260 views
0 votes
2 Answers

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 ?

Tags: json sql-server

Answers

- Charlieface
- June 5, 2023 at 6:02 pm
- 0 votes
0
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
Login or Signup to reply.

- ThomA
- June 5, 2023 at 6:03 pm
- 0 votes
0
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:
Login or Signup to reply.