Forum Discussion

kkhan-ja's avatar
kkhan-ja
Copper Contributor
Feb 16, 2025

Json data structure reconstruction from array.

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.

No RepliesBe the first to reply

Resources