SQL query OpenJson with deep nested arrays loop

Question

I know there a lot of similar questions and answers here. I've read most of them but I'm unable to query nested arrays in a JSON structure. I'm lost in the CROSS APPLY's.

I'm actually querying a web API but for the sake of my question I've put it in a variable. I'm trying to insert the ID (3519) and all the info of "worker_contracts" into a table.

declare @json nvarchar(max)
set @json = '

{
    "data": [
        {
            "id": "3519",
            "type": "affiliate_workers",
            "attributes": {
                "title": "mr",
                "first_name": "John",
                "last_name": "Doe",
                "telephone": "+32 471 12 34 56",
                "worker_contracts": [
                    {
                        "start_date": "2020-01-06",
                        "end_date": null,
                        "social_secretary_specific_data": {
                            "affiliate_id": 54,
                            "worker_details_id": 3378,
                            "start_date": "2020-01-06",
                            "end_date": null,
                            "affiliate_worker_id": 3519,

                        },
                        "work_hours_per_week": 25.0,
                        "comment": "",
                        "roster_week": [
                            {
                                "start_date": "2020-01-13",
                                "number_hours": 25
                            },
                            {
                                "start_date": "2020-01-06",
                                "number_hours": 25
                            }
                        ],
                        "social_secretary_identifier": "123456"
                    },
                    {
                        "start_date": "2019-09-23",
                        "end_date": "2020-01-05",
                        "social_secretary_specific_data": {
                            "affiliate_id": 54,
                            "worker_details_id": 3378,
                            "start_date": "2019-09-23",
                            "end_date": "2020-01-05",
                            "affiliate_worker_id": 3519,
                        },
                        "work_hours_per_week": 21.0,
                        "comment": "",
                        "roster_week": [
                            {
                                "start_date": "2019-09-30",
                                "number_hours": 21
                            },
                            {
                                "start_date": "2019-09-23",
                                "number_hours": 21
                            }
                        ],
                        "social_secretary_identifier": "123456"
                    }
                ],
                "sodexo_reference": "56789",
                "region": "Vlaanderen",
                "identity_card_number": "A1122334455",
                "can_work_with_animals": false
            }
        }

    ]
}

My SQL query only selects the start and end date values. I don't understand how I can add the other nested data.

insert into AffiliateWorkersContract_WRK
select WA.id, wc.*
from OpenJson((CAST(@json as nvarchar(max))),'$.data')


WITH (
id int,
worker_contracts nvarchar(max) as json
)
as WA

cross apply openjson (WA.worker_contracts)

with
(
start_date date '$.start_date',
end_date date '$.end_date',


) as WC

Thank you all for your help in advance.

score 0 · Answer 1 · answered Jan 14 '20 at 15:39

Found the solution myself. I just had to keep going with the cross apply


insert into AffiliateWorkersContract_WRK
select WA.id, wawc.start_date, wawc.end_date, wawc.signing_date, wawc.contract_variability,
wawc.vehicle_type, wawc.addendum, wawc.work_hours_per_week,wawc.comment, wawc.social_secretary_identifier,
wssd.affiliate_id, wssd.worker_details_id, wssd.created_at, wssd.updated_at, wssd.affiliate_worker_id, wssd.comment, wssd.author_id,
wssd.parent_id, wssd.operating_headquarter_id, wssd.reference_period, wssd.varvar_ref_period_avg_minutes_per_week,
wssd.varvar_min_work_minutes_per_week, wssd.varvar_max_work_minutes_per_week

from OpenJson((CAST(@pootsy_AWWC_request as nvarchar(max))),'$.data')


WITH (
id int,
attributes nvarchar(max) as json
) as WA

cross apply openjson ((CAST(WA.attributes as nvarchar(max))))

WITH (
worker_contracts nvarchar(max) as json
) as WC

cross apply OpenJson ((CAST(WC.worker_contracts as nvarchar(max))))

WITH (
start_date date,
end_date date,
signing_date date,
contract_variability varchar(20),
vehicle_type varchar(20),
addendum varchar(10),
work_hours_per_week decimal(4,2),
comment nvarchar(max),
social_secretary_identifier int,
social_secretary_specific_data nvarchar(max) as json,
roster_week nvarchar(max) as json
) AS WAWC

cross apply OpenJson (WAWC.social_secretary_specific_data)

WITH (
affiliate_id int,
worker_details_id int,
created_at varchar(40),
updated_at varchar(40),
affiliate_worker_id int,
comment nvarchar(max),
author_id int,
parent_id int,
operating_headquarter_id int,
reference_period varchar(10),
varvar_ref_period_avg_minutes_per_week decimal(4,2),
varvar_min_work_minutes_per_week decimal(4,2),
varvar_max_work_minutes_per_week decimal(4,2)
) AS WSSD

SQL query OpenJson with deep nested arrays loop

1 Answers1