SQL Server - parse json with multiple objects

Question

I'm parsing a JSON dataset within SQL Server and it works great with a single object, but falls down when multiple data objects are presented. I assume it's because of the CROSS APPLY statements.

Within the JSON dataset, there is only 4 records, but my current sql is returning 16 (4 duplicate sets, as there are 4 cross apply statements), but I'm not sure how to get around this?

json

{
    "type": "test",
    "user": {
        "last_update": "2022-06-19T14:13:07.707502+00:00",
        "user_id": "12345"
    },
    "data": [
        {
            "metadata": {
                "start_time": "2022-06-19T00:00:00+01:00",
                "end_time": "2022-06-20T00:00:00+01:00"
            },
            "distance_data": {
                "steps": 9299,
                "distance_meters": 7704.0
            }
        },
        {
            "metadata": {
                "start_time": "2022-06-17T00:00:00+01:00",
                "end_time": "2022-06-18T00:00:00+01:00"
            },
            "distance_data": {
                "steps": 2546,
                "distance_meters": 2143.0
            }
        },
        {
            "metadata": {
                "start_time": "2022-06-16T00:00:00+01:00",
                "end_time": "2022-06-17T00:00:00+01:00"
            },
            "distance_data": {
                "steps": 4969,
                "distance_meters": 4192.0
            }
        },
        {
            "metadata": {
                "start_time": "2022-06-18T00:00:00+01:00",
                "end_time": "2022-06-19T00:00:00+01:00"
            },
            "distance_data": {
                "steps": 6769,
                "distance_meters": 5698.0
            }
        }
    ]
}

SQL statement

    SELECT
    distance_meters, steps, cast(left(start_time,10) as date) startDate
    FROM  
     OPENJSON ( @json )  
WITH (          
            jType nvarchar(50) N'$.type',
            jUser char(36) N'$.user.user_id',       
            data nvarchar(max) as JSON
        ) as a
        CROSS APPLY
            OPENJSON(a.data)
            WITH
            (
                distance_data nvarchar(max) as json
            ) as b
        CROSS APPLY
            OPENJSON (b.distance_data)
            WITH 
            (
            distance_meters float,
            steps int
            ) as c
        CROSS APPLY
            OPENJSON (a.data)
            WITH
            (
                metadata nvarchar(max) as json
            ) as d
        CROSS APPLY
            OPENJSON (d.metadata)
            WITH 
            (
                start_time nvarchar(25),    
                end_time nvarchar(25)
            ) as e
        ORDER BY startDate ASC;

Your JSON isn't valid; it has a extra comma (,) in the user array. — Thom A
– Thom A ♦, Commented Jun 20, 2022 at 11:45
thanks for noticing, my error when I pasted the code in - updated — BigIWT
– BigIWT, Commented Jun 20, 2022 at 12:00

Zhorov · Accepted Answer · 2022-06-20 12:00:10Z

2

I think you need a single APPLY operator:

SELECT j1.jType, j1.jUser, j2.*
FROM OPENJSON(@json) WITH (          
   jType nvarchar(50) N'$.type',
   jUser char(36) N'$.user.user_id',       
   data nvarchar(max) as JSON
) AS j1
CROSS APPLY OPENJSON(j1.data) WITH (
   start_time nvarchar(25) '$.metadata.start_time',
   end_time nvarchar(25) '$.metadata.end_time',
   steps numeric(10, 0) '$.distance_data.steps',
   distance_meters numeric(10, 1) '$.distance_data.distance_meters'
) j2

Result:

jType	jUser	start_time	end_time	steps	distance_meters
test	12345	2022-06-19T00:00:00+01:00	2022-06-20T00:00:00+01:00	9299	7704.0
test	12345	2022-06-17T00:00:00+01:00	2022-06-18T00:00:00+01:00	2546	2143.0
test	12345	2022-06-16T00:00:00+01:00	2022-06-17T00:00:00+01:00	4969	4192.0
test	12345	2022-06-18T00:00:00+01:00	2022-06-19T00:00:00+01:00	6769	5698.0

edited Jun 20, 2022 at 12:00

answered Jun 20, 2022 at 11:46

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BigIWT Over a year ago

Totally perfect Zhorov - thank you so much !

Jeroen Mostert Over a year ago

The data type for the times should probably be DATETIMEOFFSET, which can then be further converted as necessary (without using string manipulation, as the OP did).

Collectives™ on Stack Overflow

SQL Server - parse json with multiple objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related