Search code examples
javamysqljsonapache-camelcamel-sql

Apache Camel: How to transform hierarchical data from database into pojo

I am new to stackoverflow and also to Apache Camel. I try to write a understandable description of my problems.

My goal is to read hierarchical data from database (mysql) which is composed by 1 entry in a parent table and several rows in a child table and transform this data into a pojo. Sub-goal: Not to write much custom code and use blueprint xml.

Since I could not find fitting EIP for this issue, I list here my approaches so far:

1. Select data by a joined query

select * from parentTable join childTable on childTable.parentId=parentTable.id

This would mean to write a custom processor to transform result into pojo, because the select result will get for each result row every time the parent properties. Since I try to avoid writing a custom processor, I tried the following:

2. Select query returns JSON with correct structure to transform to pojo

select json_object(
      'parentProperty1', parentProperty1
    , 'parentProperty2', parentProperty2
    , 'children', (select CAST(CONCAT('[',
                GROUP_CONCAT(
                  JSON_OBJECT(  
                          'childProperty1', childProperty1
                        , 'childProperty2', childProperty2
                        )),
                ']')
         AS JSON) 
        from childTable c
        where p.messageId=c.messageId
        )
    ))  
from parentTable p
;

Executing the query on mysql shell returns expected JSON:

{
    "parentProperty1": "value1",
    "parentProperty1": "value2",
    "children": [
        {
            "childProperty1": "value3",
            "childProperty2": "value4"
        },
        {
            "childProperty1": "value5",
            "childProperty2": "value6"
        }
    ]
}

Running the query inside camel, I encountered a problem, for which I could not find an explanation nor solution yet.

The body has after the execution of the query the JSON, but it is surrounded by fragments of the initial query:

[{json_object('parentProperty1', parentProperty1 , 'parentProperty2', parentProperty2 , 'children', (select CAST(CONCAT('[',
={"parentProperty1": "value1", "parentProperty2": "value2", "children": [{"childProperty1": "value3", "childProperty2": "value4"}, {"childProperty1": "value5", "childProperty2": "value6"}]}}]

Questions:

  1. Is there an existing EIP to solve my problem?
  2. Why there is no correct JSON as result in my 2. approach?

Thanks in advance

Solution

  • What you are actually getting is a key-value pair. Since no alias name was given to json_obect in the query, mysql generates a default column name. This is what you are seeing as query snippet in the result.

    Add an alias name to the json_obect in the query as shown below:

        select json_object(
      'parentProperty1', parentProperty1
    , 'parentProperty2', parentProperty2
    , 'children', (select CAST(CONCAT('[',
                GROUP_CONCAT(
                  JSON_OBJECT(  
                          'childProperty1', childProperty1
                        , 'childProperty2', childProperty2
                        )),
                ']')
         AS JSON) 
        from childTable c
        where p.messageId=c.messageId
        )
    ) as result  
from parentTable p;

    This would return something like this:

    {result={"children": [{"childProperty1": "value3", "childProperty2": "value4"}, {"childProperty1": "value5", "childProperty2": "value6"}], "parentProperty1": "value1", "parentProperty2": "value2"}}

    Hope this solves you issue