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

%3CLINGO-SUB%20id%3D%22lingo-sub-1434942%22%20slang%3D%22en-US%22%3EConvert%20Arrays%20or%20Complex%20Types%20(JSON)%20to%20string%20in%20Data%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1434942%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20current%20scenarios%20allow%20you%20to%20convert%20Arrays%20or%20Complex%20Types%20(JSON)%20into%20single%20columns%20or%20convert%20columns%20into%20JSON.%26nbsp%3B%20However%2C%20is%20there%20a%20way%20to%20convert%20the%20JSON%20into%20a%20string%20representation%20for%20storage%20into%20a%20SQL%20Column%3F%3C%2FP%3E%3CP%3EExample%3A%26nbsp%3B%20I%20have%20a%20JSON%20file%20that%20contains%20a%20section%20of%20known%20data%20values%20which%20will%20map%20directly%20to%20columns.%26nbsp%3B%20The%20file%20contains%20%22additional%20data%22%20that%20is%20not%20necessarily%20in%20a%20known%20structure%2C%20so%20I%20simply%20want%20to%20stuff%20that%20data%20in%20a%20column%20for%20later%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESource%20File%3A%3C%2FP%3E%3CPRE%3E%7B%0A%26nbsp%3B%20%26nbsp%3B%20%22id%22%3A%201234%2C%0A%26nbsp%3B%20%26nbsp%3B%20%22data%22%3A%20%7B%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22name%22%3A%20%22Acme%2C%20Inc.%22%2C%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22address%22%3A%20%7B%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22city%22%3A%20%22Seattle%22%2C%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22state%22%3A%20%22WA%22%2C%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22zip%22%3A%20%2298195%22%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%7D%0A%26nbsp%3B%20%26nbsp%3B%20%7D%2C%0A%26nbsp%3B%20%26nbsp%3B%20%22additionalData%22%3A%20%7B%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22color%22%3A%20%22red%22%2C%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22shape%22%3A%20%22square%22%2C%0A%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%22number%22%3A%204%0A%26nbsp%3B%20%26nbsp%3B%20%7D%0A%7D%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20case%2C%20I%20don't%20want%20to%20store%20color%2C%20shape%2C%20and%20number%20as%20distinct%20columns%2C%20but%20as%20text%3A%20%7B%20%22color%22%3A%20%22red%22%2C%20%22shape%22%3A%20%22square%22%2C%20%22number%22%3A%204%20%7D.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%20currently%2C%20or%20something%20coming%20in%20the%20future%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1434942%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Data%20Factory%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMapping%20Data%20Flows%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1507396%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Arrays%20or%20Complex%20Types%20(JSON)%20to%20string%20in%20Data%20Flow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1507396%22%20slang%3D%22en-US%22%3E%3CP%3EEssentially%2C%20I%20want%20something%20similar%20to%20this%3A%26nbsp%3BJsonConvert.SerializeObject(additionalData).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%2C%20I%20am%20working%20around%20this%20by%20calling%20an%20Azure%20Function%20that%20adds%20a%20new%20property%20%22serializedAdditionalData%22%20that%20contains%20the%20results%20of%26nbsp%3BJsonConvert.SerializeObject(myJson.additionalData).%26nbsp%3B%20Since%20I%20was%20already%20making%20the%20func%20call%2C%20it%20isn't%20%3CEM%3Eterrible%3C%2FEM%3E%2C%20but%20I%20would%20prefer%20to%20do%20this%20within%20the%20data%20flow.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.