enrich one JSON array with data from another based on multiple conditions using dataweave 2.0

Question

Recently, I asked this question about filter and merge two JSONs based on multiple conditions.

However, now I need mongo variable to be enriched with mysql variable data. If conditions

mongo.NUM_CONTROL = mysql.DOCNUM
mongo.NUM_ITEM = mysql.LIN_NUM_ITEM

do not match, each mongo element stays the same. But if they match, each mongo element must be enriched with mysql equivalent item.

Answer 1

You can use the following dataweave expression:

%dw 2.0
import * from dw::core::Arrays
output application/json
---
leftJoin(mongo, mysql, (m) -> m.NUM_CONTROL ++ "_" ++ m.NUM_ITEM, (s) -> s.DOCNUM ++ "_" ++ s.LIN_NUM_ITEM) map (item, index) -> 
  item.l ++ (if (item.r != null) item.r else {})

In order to left join two arrays, a common key field is needed. In this case, based on your scenario, the common keys corresponds to:

• mongo: NUM_CONTROL and NUM_ITEM
• mysql: DOCNUM and LIN_NUM_ITEM

So, concatenating mongo.NUM_CONTROL with mysql.NUM_ITEM will give the unique record key for mongo, and concatenating mysql.DOCNUM and mysql.LIN_NUM_ITEM will give the unique record key for mysql. Now those calculated keys can be used to left join the arrays. Using an underscore character (or whatever other non numeric character like a pipe, for example) as a separator will make sure that the correct records will be matched (if you have a mongo record with NUM_CONTROL = 1 and NUM_ITEM = 11 and a mysql record with DOCNUM = 11 and LIN_NUM_ITEM = 1, without the separator you would have the same calculated key value for mongo and mysql (111) and they would be joined incorrectly. With the separator, this won't happen as the mongo calculated key would be 1_11 and mysql calculated key 11_1).