OPENJSON and incremental value

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 ?

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

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):

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:

• Convert unix epoch timestamp to TSQL datetime
• How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
• UNIX_TIMESTAMP in SQL Server
• _{Search: convert unix time to datetime sql server}