JSON data structure reconstruction

[cyberman]

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.

[miguel · Accepted solution]

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.