How can I iterate over a nested list inside Camel body?

Question

I am trying to access data in the incoming {body} of my incoming Json I have done the unmarshaling with Jackson and mapped it to a Java Map Class with

  `.unmarshal().json(JsonLibrary.Jackson, java.util.Map.class)
`

My incoming Json data is something like this after unmarshal step above

{ "projectId" : 12345,
"title" : “12345 - Plant 1 Processing",
"partners": [{"partnerName": "partnerJV1", "partnerLocation": "JA"},
{"partnerName": "partnerJV2", "partnerLocation": "FL"},
{"partnerName": "partnerJV3", "partnerLocation": "OH"}
]

The partners field can have 0-N number of partnerName, partnerLocation maps.

Now I am inserting this into a SQL table with

.to("sql:classpath:sql/sql_queries.sql")

My sql_queries.sql has the following query in it to insert data fields into the table:

INSERT INTO MY_TABLE(PID, TITLE, PartnerName1, PartnerLocation1, PartnerName2, PartnerLocation2, PartnerName3, PartnerLocation3) VALUES(:#${body['projectId']}, :#${body['title']}, :#${body['partners[0]']['partnerName']}, :#${body['partners[0]']['partnerLocation']} )

my problem is I cannot know the exact number of partners and without it I cannot write my SQL statement. I get IndexOutOfBounds exception if I access say :#${body['partners'][2]['partnerName']}

But there is only one partners in the incoming body.

So how can I in camel iterate over the nested map inside my JSON based on it's length and initialize my PartnerName, PartnerLocation fields for my insert statement?

c0ld · Accepted Answer · 2019-01-16T06:20:30.943

1

Try like this:

            .setProperty("projectId", simple("body['projectId']"))
            .setProperty("title", simple("body['title']"))
            .setBody(simple("body['partners']"))
            .split(simple("body"))
            .process{//prepare your properties here}
            .end()
            .to("sql:classpath:sql/sql_queries.sql");

And sql will look like this:

INSERT INTO MY_TABLE(PID, TITLE, PartnerName1, PartnerLocation1, PartnerName2, PartnerLocation2, PartnerName3, PartnerLocation3) VALUES(:#${exchangeProperty.projectId}, :#${exchangeProperty.title}, :#${exchangeProperty.partnerName1}, :#${exchangeProperty.partnerLocation1} , :#${exchangeProperty.partnerName2}, :#${exchangeProperty.partnerLocation2}, :#${exchangeProperty.partnerName3}, :#${exchangeProperty.partnerLocation3}   )

UPD: for all data in 1 row

edited Jan 16 '19 at 06:20

answered Jan 15 '19 at 06:45

c0ld

770
4
15

Would ` .split(simple("body"))` insert each value in the split and invoke the .to for every value? Also is there a way for me to put in a default value like say "" or "null" into INSERT some of the partnerName and partnerLocation values are null so that I don't get a BadSQLGrammarException, which happens to me number of fields in the VALUES() don't match the number in the INSERT INTO MY_TABLE(...). – Tabber Jan 15 '19 at 08:44
1) Yep, split will pass each partners node to your sql insert. 2) You can pre-process map body before calling sql as you want (check for null and skip inserting or change map value to some default) – c0ld Jan 15 '19 at 09:23
Changing values by .process. Checking values can be done with [simple](http://camel.apache.org/simple.html) – c0ld Jan 15 '19 at 10:48
Thx @c0ld that helped me get to the solution. – Tabber Jan 15 '19 at 13:15

score 0 · Answer 2 · answered Jan 15 '19 at 13:21

0

Ended up doing something like this:

               .process(new Processor() {
                @Override
                public void process(Exchange exchange) throws Exception {
                    Map<String, Object> body = (Map<String, Object>) exchange.getIn().getBody();
                    int i = 1;
                    for(Map entry : (List<Map>)body.get("partners"))

                            {

                              exchange.setProperty("PartnerName"+i, entry.get("partnerName"));
                              exchange.setProperty("PartnerLocation"+i, entry.get("partnerLocation"));

                              i++;
                       }

Then ended up using these fields in INSERT INTO (...PartnerName1, PartnerLocation1, PartnerName2, PartnerLocation2.....) VALUES (....:#${property.PartnerName1}, :#${property.PartnerLocation1}...)

answered Jan 15 '19 at 13:21

Tabber

151
3
16

Oh im so inattentive. Just now i noticed that all data from partners nodes is in one db row. Is this architecture by task ? Can you save each partners node in separate db rows ? What if numbers of nodes will be not 3 ? Your sql will not working. – c0ld Jan 16 '19 at 06:14
I updated my answer for being more correct to your purpose. – c0ld Jan 16 '19 at 06:21
Think this way looks too bulky. Try make simple array with objects from map. Sql will simplify and params will be looking like (#, #, #, # , #, #, #, #) – c0ld Jan 16 '19 at 06:30
Yes That's the architecture of our db unfortunately. It can be 0-6 partner values so I can now insert null based on numbers of incoming values. – Tabber Jan 16 '19 at 06:44
So you can't use named params in sql. You can try prepare sql in CamelSqlQuery header according to numbers of nodes. – c0ld Jan 16 '19 at 06:55
That would be great ll check it. Please point me to a resource if you have it. Right now I'm setting properties on exchange in the profess() and using those in my sql file statement – Tabber Jan 16 '19 at 07:00

How can I iterate over a nested list inside Camel body?

2 Answers2