Dataweave - How to transform array of items into parent child relation in Dataweave

Question

I want to convert the below input into parent-child JSON output using dataweave transform. Please let me know if anybody worked on this before. I've added a sample input and output json below.

Updated post with additional nodes: I want multiple parent nodes with location level=1 with respective hierarchies.

Input json format:

[
    {
        "ENTITY_ID": 1,
        "PARENT_EID": 1,
        "LOCATION_LEVEL": 1,
        "LOCATION_CODE": "123"
    },
    {
        "ENTITY_ID": 2,
        "PARENT_EID": 1,
        "LOCATION_LEVEL": 2,
        "LOCATION_CODE": "234"
    },
    {
        "ENTITY_ID": 3,
        "PARENT_EID": 2,
        "LOCATION_LEVEL": 3,
        "LOCATION_CODE": "345"
    },
    {
        "ENTITY_ID": 4,
        "PARENT_EID": 1,
        "LOCATION_LEVEL": 2,
        "LOCATION_CODE": "567"
    },
    {
        "ENTITY_ID": 5,
        "PARENT_EID": 5,
        "LOCATION_LEVEL": 1,
        "LOCATION_CODE": "012"
    },
    {
        "ENTITY_ID": 6,
        "PARENT_EID": 5,
        "LOCATION_LEVEL": 1,
        "LOCATION_CODE": "023"
    }
]

Output json format:

[
    {
        "ENTITY_ID": 1,
        "PARENT_EID": 1,
        "LOCATION_LEVEL": 1,
        "LOCATION_CODE": "123",
        "CHILDRENS":[
                {
                    "ENTITY_ID": 2,
                    "PARENT_EID": 1,
                    "LOCATION_LEVEL": 2,
                    "LOCATION_CODE": "234"
                    "CHILDRENS":[{
                            "ENTITY_ID": 3,
                            "PARENT_EID": 2,
                            "LOCATION_LEVEL": 3,
                            "LOCATION_CODE": "345"                          
                    }]
                },
                {
                    "ENTITY_ID": 4,
                    "PARENT_EID": 1,
                    "LOCATION_LEVEL": 2,
                    "LOCATION_CODE": "567"
                }
        ]
        
    },
    {
        "ENTITY_ID": 5,
        "PARENT_EID": 5,
        "LOCATION_LEVEL": 1,
        "LOCATION_CODE": "012",
        "CHILDRENS":[{
                  "ENTITY_ID": 6,
                  "PARENT_EID": 5,
                  "LOCATION_LEVEL": 2,
                  "LOCATION_CODE": "023"
              }]
    }
]

George George · Accepted Answer · 2020-12-25T22:54:48

Give this a try:

%dw 2.0
output application/json

var data = [
    {
        "ENTITY_ID": 1,
        "PARENT_EID": 1,
        "LOCATION_LEVEL": 1,
        "LOCATION_CODE": "123"
    },
    {
        "ENTITY_ID": 2,
        "PARENT_EID": 1,
        "LOCATION_LEVEL": 2,
        "LOCATION_CODE": "234"
    },
    {
        "ENTITY_ID": 3,
        "PARENT_EID": 2,
        "LOCATION_LEVEL": 3,
        "LOCATION_CODE": "345"
    },
    {
        "ENTITY_ID": 4,
        "PARENT_EID": 1,
        "LOCATION_LEVEL": 2,
        "LOCATION_CODE": "567"
    },
    {
        "ENTITY_ID": 5,
        "PARENT_EID": 5,
        "LOCATION_LEVEL": 1,
        "LOCATION_CODE": "012"
    },
    {
        "ENTITY_ID": 6,
        "PARENT_EID": 5,
        "LOCATION_LEVEL": 2,
        "LOCATION_CODE": "023"
    }
]

var top = data filter ($.LOCATION_LEVEL == 1)

fun insert(t,e) = t match {
    case {} -> e
    case tree if (tree.ENTITY_ID == e.PARENT_EID) -> do {
        var node = tree - "CHILDREN"
        var children = if (tree.CHILDREN?) (tree.CHILDREN + e) else [e]
        ---
        {(node),CHILDREN: children}
    }
    case tree if (tree.ENTITY_ID != e.PARENT_EID) -> do {
        var node = tree - "CHILDREN"
        var children = tree.CHILDREN map ($ insert e)
        ---
        {(node), (CHILDREN: children) if (not isEmpty(children))}
    }
    else -> $
}


---
top map (topnode) -> (
    (data -- top) orderBy $.ENTITY_ID reduce ((e, acc=topnode) -> acc insert e)
)

Test it with more inputs and you may need to adjust the code if some other test cases fail.

You (or maybe someone else) may want to rewrite this using the update operator as compared building the node and the children variables like I do.

Dataweave - How to transform array of items into parent child relation in Dataweave

1 Answers