Azure data factory json data conversion null value

Question

I have a json feed in the below format. I need to update the data in NoSQL collection having a different schema as shown below. Using Azure data factory how can I transform input json schema to target schema?

Since the currentValue can be of different data type(array, number, complex type, string etc) for each record, Azure Data flow task is giving null value for 'Derived Column' schema modifier as well as 'Flatten' formatter.

Input Json

[
    {
        "type": "UPDATE",
        "key": { "id": "112710876" },
        "doc": [
            {
                "property": "org.numberOfEmployees",
                "currentValue": [
                    {
                        "value": 2256,
                        "scope": "Consolidated"
                    },
                    {
                        "value": 516,
                        "scope": "Individual"
                    }
                ]
            }
        ]
    },
    {
        "type": "UPDATE",
        "key": { "id": "081243215" },
        "doc": [
            {
                "property": "org.startDate",
                "currentValue": "1979-09-14T06:08:51Z"                
            }
        ]
    },
    {
        "type": "UPDATE",
        "key": { "id": "081243216" },
        "doc": [
            {
                "property": "org.employeeCount",
                "currentValue": "20000"
            }
        ]
    },
    {
        "type": "UPDATE",
        "key": { "id": "081243216" },
        "doc": [
            {
                "property": "org.headOffice",
                "currentValue": {
                    "city": "NY",
                    "country": "US" 
                }
            }
        ]
    }
]

Target Schema

{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "type": "object",
    "properties": {
        "id": {
            "type": "integer"
        },
        "startDate": {
            "type": "string"
        },
        "numberOfEmployees": {
            "type": "array",
            "items": [
                {
                    "type": "object",
                    "properties": {
                        "value": {
                            "type": "integer"
                        },
                        "scope": {
                            "type": "string"
                        }
                    }
                }
            ]
        },
        "employeeCount": {
            "type": "integer"
        },
        "headOffice": {
            "type": "object",
            "properties": {
                "city": {
                    "type": "string"
                },
                "country": {
                    "type": "string"
                }

            }
        }
    }
}

Is there any way I can stringify currentValue in data flow task, if there is no direct way to transform the input data to target schema?

Any help would be appreciated.

Answer 1

You can stringify it in a derived column using "toString()" or you can wait for our new Stringify transformation in October :)