Azure Data Factory Copy Activity dynamic schema mapping issue

Copper Contributor

I am trying to do a dynamic schema mapping in Azure Data Factory but with little luck.

I am having trouble to cast or convert when sinking. the JSON_Query incapsulates the json as a string and is also escaping the double quotes. I need a clean JSON object to work with.

The Source SQL statement

SELECT 
      [table]
      ,JSON_query(data_context) as data_context
      ,[operation]
      ,[updated_at] FROM [BIS_ChangedDataOnEventStage]
where [id] <= 5000

the result

[
    {
        "table": "pnt",
        "data_context": "{\"pnt_name\":\"P000_KLAUS22\",\"pnt_no\":136,\"}",
        "operation": "DEL",
        "updated_at": "2023-08-17T07:51:42.447"
    }
]

What I would like is the following result

[{
    "table": "pnt",
    "data_context": {
        "pnt_name": "P000_KLAUS22",
        "pnt_no": 136
    },
    "operation": "DEL",
    "updated_at": "2023-08-17T07:51:42.447"
}]

I have been trying al sorts of things with the translator object. My best guess is to explain ADF how to read the object therafter declaring sink properties.

{
        "type": "TabularTranslator",
        "mappings": [
            {
                "source": {
                    "path": "context.$['pnt_name']",
                    "type": "Object"
                },
                "sink": {
                    "path": "pnt_name",
                    "type": "Object"
                }
            }
        ]
    }

Thanks in advance

 
0 Replies