SQL server FOR JSON AUTO result to cosmosdb container using ADF/Synapse or copy data

Question

Is the below json control flow expression available in Data flow transformation?
https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#json

Basically, I have a SQL source in a dataflow

SELECT id, JSON_QUERY(jsonData) AS jsonData
FROM [MyTable]
FOR JSON AUTO

The SQL output of jsonData is a string. Is there a way to convert this into a JSON object from within the dataflow or even from copy data activity. I'm inserting the output to CosmosDB container and this fields is not picked up as a JSON object. I'm pretty sure I'm not the first one, anyone tried this before? Any help is greatly appreciated.

I'm trying to avoid pushing the sql output to a file and then reading the file as JSON. I also don't want to construct the JSON in the expression builder. If the SQL DB is doing the heavy lifting of constructing a JSON, why not persist is directly in the CosmosDB container?

Answer 1

Hi @Saru Thiagarajan ,

Thank you for posting query in Microsoft Q&A Platform.

As I understand your query, You are looking for loading json data which coming as string in to CosmosDB either using copy activity as data flows. Please correct me if my understanding is incorrect.

Pipeline expressions has json() function as you mentioned but mapping data flow don't have such out of box function to convert string to json.

Your requirement can be achieved using Parse transformation in mapping data flows. Click here for parse transformation documentation. Click here for small video on parse transformation to understand its functionality better.

Kindly check below images and details which explains similar implementation.

Step1: Source transformation. Here I am using Adhoc query to get some json data as string.

Step2: Parse Transformation to parse json string to json objects and arrange them as columns and rows.

Step3: Sink transformation use CosmosDB dataset and load data accordingly.

Hope this helps. Please let us know if any further queries. Thank you.

-----------

Please consider hitting Accept Answer. Accepted answers helps community as well.