Convert Arrays or Complex Types (JSON) to string in Data Flow

Occasional Contributor

The current scenarios allow you to convert Arrays or Complex Types (JSON) into single columns or convert columns into JSON.  However, is there a way to convert the JSON into a string representation for storage into a SQL Column?

Example:  I have a JSON file that contains a section of known data values which will map directly to columns.  The file contains "additional data" that is not necessarily in a known structure, so I simply want to stuff that data in a column for later use.

 

Source File:

{
    "id": 1234,
    "data": {
        "name": "Acme, Inc.",
        "address": {
            "city": "Seattle",
            "state": "WA",
            "zip": "98195"
        }
    },
    "additionalData": {
        "color": "red",
        "shape": "square",
        "number": 4
    }
}

 

In this case, I don't want to store color, shape, and number as distinct columns, but as text: { "color": "red", "shape": "square", "number": 4 }.

 

Is this possible currently, or something coming in the future?

1 Reply

Essentially, I want something similar to this: JsonConvert.SerializeObject(additionalData).

 

Currently, I am working around this by calling an Azure Function that adds a new property "serializedAdditionalData" that contains the results of JsonConvert.SerializeObject(myJson.additionalData).  Since I was already making the func call, it isn't terrible, but I would prefer to do this within the data flow.