Inserting a JSON list in Cosmos DB

Question

Inserting a JSON list in Cosmos DB

Devashish Parmar (BLR GSS) 86

I am using ADF to load documents to Cosmos DB. The challenge is I want to have a sub JSON format inside a list i.e. [ ].

Below is a sample format.

{
"paymentFrequency": "SP",
"address": [
{
"street1": "GA04230-US",
"street2": 0,
"street3": "359668276007920",
}
],
"partitionKey": "partitionKey1",
}

Note the values inside 'address' is a list.

Can you help me achieve it using Azure Data FActory (ADF)?

Answer accepted by question author

0 additional answers

Your answer

Answer 1

ShaikMaheer-MSFT 38,556 Microsoft Employee Moderator

Hi @Devashish Parmar (BLR GSS) ,

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

You can consider using flatten transformation to flatten the array(list). Click here to know more about flatten transformation.

You can consider checking below video as well about flatten transformation.
Flatten Transformation in Mapping Data Flow in Azure Data Factory

Kindly check below where I tried same
Source File and Source Transformation in dataflow.

Flatten Transformation:

After loading data to sink storage using Sink transformation

Hope this helps. Please let me know if any further queries.

----------------

Please consider hitting Accept Answer button. Accepted answers help community as well.

Devashish Parmar (BLR GSS) 86 Reputation points

2022-12-21T07:00:30.813+00:00

Thanks Maheer,

Will this work if my source is a SQL database? Sorry for not being specific in my question. Below is the scenario.

My source is SQL Server table and I am using below query on my ADF pipeline as a souce.

SELECT
OrderId AS OrderId,
Sku AS 'parent.Sku',
RetailAmount AS 'parent.RetailAmount',
ComissionAmount
FROM <SourceTable>

Below is the o/p I am getting in my Target Cosmos DB
{
"OrderId": "22a55a38-312c-4a85-b7f1-ee099e143b70",
"parent": {
"Sku": "20000109",
"RetailAmount": 5.23
},
"CommissionAmount": -2.67,
"id": "id Value",
}

======================================================

However, the desired o/p should be in the below format with array list. (Note: the [ ] brackets under 'parent' attribute

{
"OrderId": "22a55a38-312c-4a85-b7f1-ee099e143b70",
"parent": [
{ "Sku": "20000109", "RetailAmount": 5.23 } ],
"CommissionAmount": -2.67,
"id": "id Value",
}
ShaikMaheer-MSFT 38,556 Reputation points Microsoft Employee Moderator

2022-12-21T08:41:57.747+00:00

Hi @Devashish Parmar (BLR GSS) ,

Thank you for sharing details. You can achieve this using dataflows in Azure data factory.

You need to use Aggregate transformation and build expression to get list.

Kindly check below example, which I tried.
Source Transformation:

Aggregate Transformation: expression used here is collect(@(name={emp.name},gender={emp.gender}))

Finally load this to your Sink location as json. Hope this helps.

---------------------

Please consider hitting Accept Answer button. Accepted Answers help community as well.
ShaikMaheer-MSFT 38,556 Reputation points Microsoft Employee Moderator

2022-12-22T09:32:58.797+00:00

Hi @Devashish Parmar (BLR GSS) ,

Just checking in to see if the above answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
Devashish Parmar (BLR GSS) 86 Reputation points

2022-12-27T04:07:48.067+00:00

Unfortunately Dataflow activity doesn't work with Onprem SQL DBs .. but thanks for your answer.

I used a different approach to implement the same i.e. Load SQL table to .json files (blob storage) and then to Cosmos DB.
Reference Link: https://arsenvlad.medium.com/denormalizing-via-embedding-when-copying-data-from-sql-to-cosmos-db-649a649ae0fb

Share via

Inserting a JSON list in Cosmos DB

0 additional answers

Your answer