none
Copy whole json object to single table column in Azure Data Factory

    Question

  • I would like to Copy the whole JSON object to a single column into a table by Azure Data Factory (currently using v2, I suppose that required jsonpath will be the same as within v1).

    This is the example input:

    [
    {
        "id": "123", 
        "time": "2015-04-29T07:12:20.9100000Z",
        "callingimsi": "466920403025604",
        "callingnum1": "678948008",
        "callingnum2": "567834760",
        "switch1": "China",
        "switch2": "Germany"
    },
    {
        "id": "456", 
        "time": "2015-04-29T07:13:21.0220000Z",
        "callingimsi": "466922202613463",
        "callingnum1": "123436380",
        "callingnum2": "789037573",
        "switch1": "US",
        "switch2": "UK"
    },
    {
        "id": "789", 
        "time": "2015-04-29T07:13:21.4370000Z",
        "callingimsi": "466923101048691",
        "callingnum1": "678901578",
        "callingnum2": "345626404",
        "switch1": "Germany",
        "switch2": "UK"
    }
    ]

    desired table output should look like this:

    Id|JsonData
    
    123|{"id": "123","time": "2015-04-29T07:12:20.9100000Z","callingimsi": "466920403025604","callingnum1": "678948008","callingnum2": "567834760","switch1": "China","switch2": "Germany"}
    456|{"id": "456","time": "2015-04-29T07:13:21.0220000Z","callingimsi": "466922202613463","callingnum1": "123436380","callingnum2": "789037573","switch1": "US","switch2": "UK"}
    789|{"id": "789","time": "2015-04-29T07:13:21.4370000Z","callingimsi": "466923101048691","callingnum1": "678901578","callingnum2": "345626404","switch1": "Germany","switch2": "UK"}

    How to get the Id value is pretty clear, however I do struggle to read the full Json object in a separate column (only few columns will be extracted in DF)

    Thx for any help.

    Tuesday, July 31, 2018 12:35 PM

All replies

  • Yes. V1 and V2 are similar.

    To achieve that, you can try with jsonPath of the targeted column in JsonPathDefinition as $

    https://docs.microsoft.com/en-us/azure/data-factory/supported-file-formats-and-compression-codecs#json-format


    Wednesday, August 1, 2018 1:07 AM