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;

CodePudding user response：

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: