Over a million developers have joined DZone.

Transform a Database 1:n Relation in a JSON Object With DataWeave

This tutorial will guide you through how you can use DataWeave to quickly transform your related data into XML while preserving the relationship.

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Coucbase.

Recently, I ran into a requirement where I had to read from a database a list with a 1:n and turn it into a JSON object in which this relationship was maintained

In Input, I had a typical 1:n relationship: Author-Books.

Table author:

idnamesurnameage
1IsaacAsimov57
2AgathaChristie58


Table Book:

idauthor_idtitleyear
11

Foundation and Earth

1986
21

The Caves of Steel

1954
32

And Then There Were None

1939


And this simple query... 

select a.id, a.name, a.surname, a.age, b.title, b.year from author a 
left join book b on a.id = b.author_id


...responds in this way:

id

namesurnameagetitleyear
1

Isaac

Asimov

57

Foundation and Earth

1986
2

Isaac

Asimov

57


The Caves of Steel

1954
3

Agatha

Christie

58

And Then There Were None

1939


I want to transform this relation in an array JSON like this:

[
    {
        "id":1,
        "name":"Isaac",
        "surname":"Asimov",
        "age":57,
        "books":[
            {
                "title":"Foundation and Earth",
                "year":1986
            },
            {
                "title":"The Caves of Steel",
                "year":1954
            }
        ]
    },
    {
        "id":1,
        "name":"Agatha",
        "surname":"Christie",
        "age":58,
        "books":[
            {
                "title":"And Then There Were None",
                "year":1939
            }
        ]
    }
]


I want the books grouped in one array in the "books" fields for authors.

I was surprised that no one seems to have resolved the problem on the Web or in the Mule documentation.

So, I tried to do this transformation and finally resolved with the DataWeave in this way:

<sub-flow name="selectTOJSON">
    <db:select config-ref="Generic_Database_Configuration" doc:name="Database">
    <db:parameterized-query><![CDATA[select a.id, a.nome, a.cognome, a.age, b.title, b.year from author a 
    left join book b on a.id = b.author_id]]></db:parameterized-query>
</db:select>
<dw:transform-message doc:name="Transform Message">
    <dw:set-payload><![CDATA[
            %dw 1.0
            %output application/json
            ---
            payload groupBy $.id pluck {
                id: $$,
                name: $.name distinctBy $ reduce $$ + $,
                surname: $.surname distinctBy $ reduce $$ + $,
                age: $.age distinctBy $ reduce $$ + $,
                books: $ map ((payload01 , indexOfPayload01) -> {
                    title: payload01.title,
                    year: payload01.year
                })
            } ]]>
        </dw:set-payload>
    </dw:transform-message>
</sub-flow>    


The payload input to DataWeave is a List<Map> from a database query.

All it takes is a brief comment transformation code. In DataWeave, I grouped by the author_id and extracted the author table entries with distinctBy and reduced the result to a single item (otherwise, it would be an array of identical elements). For the "books" fields, I remapped 1:1 what I get in the payload. The result is the JSON above.

There are probably more elegant solutions to solve this problem, and I'm open to any comments/improvements.

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
mule esb ,integration ,muleesb ,database

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}