Transform nested JSON in data factory to sql

Question

New to data factory. I have a json file that needs to manipulate but I can't figure out how to go about it. The file has a generic "name" property but it should have the value as the key name. How can I get it so that I can get the value as key? So far been getting Complex JSON errors. This json is coming from file store.

[
    {
        "Version": "1.1",
        "Documents": [
            {
                "DocumentState": "Correct",
                "DocumentData": {
                    "Name": "Name1",
                    "$type": "Document",
                    "Fields": [
                        {
                            "Name": "Form",
                            "$type": "Text",
                            "Value": "Birthday Form"
                        },
                        {
                            "Name": "Date",
                            "$type": "Text",
                            "Value": "12/1/1999"
                        },
                        {
                            "Name": "FirstName",
                            "$type": "Text",
                            "Value": "John"
                        },
                        {
                            "Name": "FirstName",
                            "$type": "Text",
                            "Value": "Smith"
                        }
                    ]
                }
            }
        ]
    },
    {
        "Version": "1.1",
        "Documents": [
            {
                "DocumentState": "Correct",
                "DocumentData": {
                    "Name": "Name2",
                    "$type": "Document",
                    "Fields": [
                        {
                            "Name": "Form",
                            "$type": "Text",
                            "Value": "Entry Form"
                        },
                        {
                            "Name": "Date",
                            "$type": "Text",
                            "Value": "4/3/2010"
                        },
                        {
                            "Name": "FirstName",
                            "$type": "Text",
                            "Value": "Jane"
                        },
                        {
                            "Name": "LastName",
                            "$type": "Text",
                            "Value": "Doe"
                        }
                    ]
                }
            }
        ]
    }
]

Expected output

DocumentData: [
{
  "Form":"Birthday Form",
  "Date": "12/1/1999",
  "FirstName": "John",
  "LastName": "Smith"
},
{
  "Form":"Entry Form",
  "Date": "4/3/2010",
  "FirstName": "Jane",
  "LastName": "Doe"
}
]

Please share expected output.

Abhishek Khandave
– Abhishek Khandave

2021-09-17 12:20:15 +00:00
Commented Sep 17, 2021 at 12:20 — Abhishek Khandave
– Abhishek Khandave, Commented Sep 17, 2021 at 12:20
@AbhishekKhandave-MT please see the updated output.

hammies
– hammies

2021-09-17 14:21:23 +00:00
Commented Sep 17, 2021 at 14:21 — hammies
– hammies, Commented Sep 17, 2021 at 14:21

Satya V · Accepted Answer · 2021-09-29 09:29:33Z

@jaimers,

I was able to achieve it by making use of the Data Flow Activity

The below is the complete DataFlow

1) Source1

This step involves getting the data from source. You will have to configure the Source dataset.

The only change I had done in the source was to Convert Fields.Name,Field.Type,Field.Value as string[] (From string).

This was required to make/create key value pair of the fields in the Subsequent steps.

Flatten1 I had made use of Flatten at the Document level.

And got the values of DocumentData.DocumentName and DocumentData.Fields

Note : If you don't want DocumentData.DocumentName - You can safely ignore it.

4) DerivedColumn1

This is actual step where I convert name:key1 key:value1 to key1:value1.

To do that I had made use of the below expression :

keyValues(Fields.Name,Fields.Value)

Note: Keyvalues() function expects 2 array arguments. Hence, in the first step we had changed the type of Fields.Name and Fields.Value to array.

4) Select

Just to select the columns that need to be sent as an output

Output

wBob · Accepted Answer · 2021-09-30 10:04:21Z

You mentioned SQL in your title so if you have access to a SQL database, eg Azure SQL DB, then it is quite capable with manipulating JSON, eg using the OPENJSON and FOR JSON PATH methods. A simple example:

DECLARE @json VARCHAR(MAX) = '[
    {
        "Version": "1.1",
        "Documents": [
            {
                "DocumentState": "Correct",
                "DocumentData": {
                    "Name": "Name1",
                    "$type": "Document",
                    "Fields": [
                        {
                            "Name": "Form",
                            "$type": "Text",
                            "Value": "Birthday Form"
                        },
                        {
                            "Name": "Date",
                            "$type": "Text",
                            "Value": "12/1/1999"
                        },
                        {
                            "Name": "FirstName",
                            "$type": "Text",
                            "Value": "John"
                        },
                        {
                            "Name": "FirstName",
                            "$type": "Text",
                            "Value": "Smith"
                        }
                    ]
                }
            }
        ]
    },
    {
        "Version": "1.1",
        "Documents": [
            {
                "DocumentState": "Correct",
                "DocumentData": {
                    "Name": "Name2",
                    "$type": "Document",
                    "Fields": [
                        {
                            "Name": "Form",
                            "$type": "Text",
                            "Value": "Entry Form"
                        },
                        {
                            "Name": "Date",
                            "$type": "Text",
                            "Value": "4/3/2010"
                        },
                        {
                            "Name": "FirstName",
                            "$type": "Text",
                            "Value": "Jane"
                        },
                        {
                            "Name": "LastName",
                            "$type": "Text",
                            "Value": "Doe"
                        }
                    ]
                }
            }
        ]
    }
]'

-- Restructure the JSON and add a root
SELECT *
FROM OPENJSON ( @json )
WITH
(
    Form        VARCHAR(50) '$.Documents[0].DocumentData.Fields[0].Value',
    [Date]      DATE '$.Documents[0].DocumentData.Fields[1].Value',
    FirstName   VARCHAR(50) '$.Documents[0].DocumentData.Fields[2].Value',
    LastName    VARCHAR(50) '$.Documents[0].DocumentData.Fields[3].Value'
)
FOR JSON PATH, ROOT('DocumentData');

My results:

NB I've used the ROOT clause to add a root to the JSON document. You could make the @json a stored proc parameter and use a Stored Proc task from the pipeline.

Collectives™ on Stack Overflow

Transform nested JSON in data factory to sql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related