Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

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

DZone's Guide to

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
Free Resource

Navigating today's database scaling options can be a nightmare. Explore the compromises involved in both traditional and new architectures.

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:

id name surname age
1 Isaac Asimov 57
2 Agatha Christie 58


Table Book:

id author_id title year
1 1

Foundation and Earth

1986
2 1

The Caves of Steel

1954
3 2

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

name surname age title year
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.

Understand your options for deploying a database across multiple data centers - without the headache.

Topics:
mule esb ,integration ,muleesb ,database

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}