JSON data structure reconstruction

cyberman — Sat, 15 Feb 2025 03:06:16 GMT

Hi Everyone,

I am working on an Azure Cosmos DB migration where I need to transform the structure of documents in the "Notes" container. While I have identified most of the migration steps, I am currently facing a challenge with structural conversion and parsing of a JSON array field inside Azure Data Factory (ADF) Data Flow.

Problem Context:
I need to convert the "NoteDetails" array field from the original document format to a new normalized structure. The transformation involves flattening the array and restructuring it into a key-value format within a new field called "NormalizedNoteDetails".

Original Document (Current Cosmos DB Structure):

{ "id": "123", "ownerId" : "1234", "noteCategory" : "somecategory", "NoteDetails": [ { "Key": "PropertyNameKey1", "Value": ["prop1of1", "prop2of1"] }, { "Key": "PropertyNameKey2", "Value": ["prop1of2", "prop2of2"] }] }

Expected Transformed Structure (Target Format in Cosmos DB):

{ "id": "123", "ownerId" : "1234", "noteCategory" : "somecategory", "normalizedNoteDetails": { "PropertyNameKey1": ["prop1of1", "prop2of1"], "PropertyNameKey2": ["prop1of2", "prop2of2"] } }

Specific Question:
How can I efficiently parse and transform the "Notes" JSON array field inside an ADF Data Flow activity to achieve the new structure? I have tried flatten and pivot with no success but I am newbie to the ADF. A concrete example would help me tremendously.

Thanks.

Re: JSON data structure reconstruction

miguel — Sat, 15 Feb 2025 13:22:19 GMT

I'd recommend posting this question in the Azure Data Factory community forum:

https://techcommunity.microsoft.com/category/azuredatabases/discussions/azuredatafactory

Mapping Dataflows are specific to Azure Data Factory. The community where this is posted is specific to Fabric Data Factory where Mapping Dataflows are not available.

topic Re: JSON data structure reconstruction in Dataflow

JSON data structure reconstruction

Re: JSON data structure reconstruction