JSON object to array of nested JSON

SARASWAT CHETAN 91

I have a JSON object in cosmos DB(source) which I need to transform into an array of nested JSON objects(similar to source objects)in Cosmos DB(sink). Using the derived column I am facing an error during typecast of complex json to array. Could I get an assistance into this.

SARASWAT CHETAN 91 Reputation points

2020-11-06T12:04:51.477+00:00

Additional details:
my source looks like this
{
id:
experience:
{
title:
amount:
}
and I'm trying to use "derived column" to update the experience object to an array which would hold many of these experience objects like below
{
id:
experience:
[
{
title:
amount:
},
{
title:
amount:
},
{
title:
amount:
}
]
getting below error
structype not able to convert as an array for complex type{}
HimanshuSinha-msft 19,476 Reputation points Microsoft Employee

2020-11-07T00:05:08.46+00:00

Hello @SARASWAT CHETAN ,

Thanks for the ask here .

I thnk I understand that what you want to achieve , but if you can share some dummy data of the JSON source that will help us a lot to help you .

I will wait to hear bnack from you on this .

Thanks
Himanshu
SARASWAT CHETAN 91 Reputation points

2020-11-08T14:22:24.027+00:00

my source looks like this
{
id:
experience:
{
title:
amount:
}
}
and I'm trying to use "derived column" to update the experience object to an array which would hold many of these experience objects like below
{
id:
experience:
[
{
title:
amount:
},
{
title:
amount:
},
{
title:
amount:
}
]
getting below error
structype not able to convert as an array for complex type{}
HimanshuSinha-msft 19,476 Reputation points Microsoft Employee

2020-11-16T22:31:50.907+00:00

Hello @SARASWAT CHETAN ,

Thanks for sharing the details , unfortunately its not helping much . For a deeper investigation and immediate assistance on this issue,
if you have a support plan you may file a support ticket, else could you please send an email to AzCommunity@microsoft.com with the below details, so that we can create a one-time-free support ticket for you to work closely on this matter.
Thread URL:https://learn.microsoft.com/en-us/answers/questions/153749/index.html
Subscription ID:
Subject:Attention Himanshu
Please let me know once you have done the same

Thanks Himanshu
SARASWAT CHETAN 91 Reputation points

2020-11-18T05:14:33.177+00:00

Thanks Himanshu for your attention. My issue is now resolved. I used hierarchy to create an array out of my jSON inputs which resolved the issue.

Accepted answer

MarkKromer-MSFT 5,211 Reputation points Microsoft Employee

2020-11-18T07:36:59.573+00:00

To generate arrays in your data, you will use an Aggregate transformation with the collect() function.

We have an example video of this here: https://www.youtube.com/watch?v=zneE18EHJSE
Please sign in to rate this answer.
SARASWAT CHETAN 91 Reputation points

2020-11-18T07:42:02.683+00:00

Thanks Mark. Yes I've used the above resource only and issue is now resolved.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

JSON object to array of nested JSON

0 additional answers

Your answer