Azure Data Factory - SQL to nested JSON

Question

I have a SQL database with tables for Staff and Appointments (1 staff : many appointments). I'd like to use Azure Data Factory to output this to nested JSON in a Blob store in a format similar to the below:

[
   {
      "staffid":"101",
      "firstname":"Donald",
      "lastname":"Duck",
      "appointments":[
         {
            "appointmentid":"201",
            "startdate":"2020-02-01T00:00:00",
            "enddate":"2020-04-29T23:00:00"
         },
         {
            "appointmentid":"202",
            "startdate":"2020-01-01T00:00:00",
            "enddate":"2020-01-31T00:00:00"
         }
      ]
   },
   {
      "staffid":"102",
      "firstname":"Mickey",
      "lastname":"Mouse",
      "appointments":[
         {
            "appointmentid":"203",
            "startdate":"2020-02-01T00:00:00",
            "enddate":"2020-04-29T23:00:00"
         },
         {
            "appointmentid":"204",
            "startdate":"2020-01-01T00:00:00",
            "enddate":"2020-01-31T00:00:00"
         }
      ]
   }
]

I've tried using the Copy activity but this produces flat JSON structures rather than the nested structure described above. Has anyone got a way to do this please?

Please provide more information about what you need help with. Is it how to setup a connection between SQL Server and Azure Data factory? Is it how to setup the job in DF to pull data and generate json? This question is very broad and by narrowing it down to specifics you are more likely to get better answers — Ed Boykin
@EdBoykin thanks for getting back to me - I'm specifically trying to find a way to produce nested JSON whereby a Staff object contains an array of Appointments. I've got ADF connected to SQL and generating flat JSON records, but want a way to output nested ones. — Chris

Jay Gong Jay Gong · Accepted Answer · 2020-03-31T07:01:03

More scenarios for JSON data in ADF is flattening. However,according to your description,your need producing JSON contains Json array group by some columns.Something like merge appointment things by same staff.

If my understanding is right,then you could get some clues from my previous case:How to split into Sub Documents with Nesting separator?. Please refer to my test:

Simulate your sample data:

Use sql in sql db source dataset:

select app.staffid,app.firstname,app.lastname,
'appointments' = (
            SELECT
                appointmentid AS 'appointmentid',startdate as 'startdate',enddate as 'enddate'
            FROM
                dbo.appoint as app2
            where app2.staffid = app.staffid and
            app2.firstname = app.firstname and
            app2.lastname = app.lastname
            FOR JSON PATH)
from dbo.appoint as app
group by app.staffid,app.firstname,app.lastname
FOR JSON Path;

Output in blob storage:

I try to verify the json format and it is correct.

Azure Data Factory - SQL to nested JSON

1 Answers