Is there a way to flatten a nested JSON in Azure Data Factory without using Dataflows?

Question

I'm using Azure Data Factory copy statements to grab the output of an API as a JSON file and drop the data into a synapse data warehouse. The JSON has a layout similar to the JSON added below, though the 'values' field can have a length of over 10,000 characters. I've added a table below that has what I hope the output should look like as well.

I do have one limitation in that I am unable to use dataflows. I understand this is the usual method for working through this issue, but it is not a solution I am specifically capable of using, which is why I am asking here for any alternative solution.

Any and all help is appreciated.

JSON

{
     "records": [
          {
               "id": 1,
               "name": "name1",
               "values": [
                    {
                    "id":20987,
                    "value": "value1"
                    },
                    {
                    "id":94756,
                    "value": "value2"
                    },
                    {
                    "id":63009,
                    "value": "value3"
                    }
               ]
          },
          {
               "id": 2,
               "name": "name2",
               "values": [
                    {
                    "id":227272727272,
                    "value": "value7",
                    "user": "Greg"
                    },
                    {
                    "id":456787654664,
                    "value": "value8", 
                    "user": "Tony"
                    },
                    {
                    "id":283749467394,
                    "value": "value9", 
                    "user": "Maria"
                    }
               ]
          }
     ]
}

SQL

id	name	values.id	values.value	values.user
1	name1	20987	value1	NULL
1	name1	94756	value2	NULL
1	name1	63009	value3	NULL
2	name2	227272727272	value7	Greg
2	name2	456787654664	value8	Tony
2	name2	283749467394	value9	Maria

Answer 1

In order to flatten Json without using dataflow, you can use SQL query script. Below is the approach.

• Take the lookup activity and take the Json dataset in that activity.

• Take the copy activity and connect it sequentially with lookup activity. In source , give the below query.

DECLARE @json NVARCHAR(max) = N'@{activity('Lookup1').output.value[0].records}';
SELECT
   r.id,r.name,v.id AS [values.id],
    v.value AS [values.value]
FROM
    OPENJSON(@json) WITH (
        id INT,
        name VARCHAR(50),
   [values] NVARCHAR(MAX) AS JSON
  
    ) AS r
 CROSS APPLY OPENJSON(r.[values]) WITH (
        id VARchar(100),
        value VARCHAR(50)
    ) AS v;

• In sink, give the azure sql table as sink dataset.

This will make sure the Json is flattened and copied to the SQL table. Note: your data is not having relational data. user field is missing for id=1; We can only flatten the relational data here. Make sure the data structure is same across all the lineitems.