Merge JSON with DB results

[#1]

I have an input that is a JSON from which I extract a property that is necessary to build a query against a DB. After I obtain the query results I need to join the result of the query with the rest of the information that is on the JSON from which I extracted a property to do the query.

I extract the property with EvaluateJsonPath, after that I call ExecuteSQL and send the result to ConvertAvroToJSON but now I don't know to merge this with the previous JSON I have.

[#2 · Accepted solution]

@Manuel Carnerero

As you are extracting a property that is necessary to build a query along with that Extract all the required content from the input JSON and keep as attributes to the flowfile.

Make your Join with the extracted property once you get results back now the flowfile content is changed but the extracted attributes will not be lost.

So extract all the required information from the join result content also and keep them as attributes to the flowfile.

Now you are having all the required values as attributes to the flowfile ,Use AttributesToJSON processor mention all your attribute names in this processor Based on the attribute values processor creates new json message.

Flow:

AttributesToJSON processor configs:

inputjson_attr1,inputjson_attr2 are extracted from first EvaluateJsonPath processor,
joinjson_attr1,joinjson_attr2 are extracted from second EvaluateJsonPath processor and now we are merging both results into one json message.

(or)

Method2 Using Replace Text Processor:
After ConvertAvroToJSON processor we can also use ReplaceText processor to search for } (or) }](if the wrap single record is set to true) and replace with all the attributes that are extracted from the input Json message.

Flow:

ReplaceText Configs:

earch Value

}$

Replacement Value

,"inputjson_attr1":"${inputjson_attr1}","inputjson_attr2":"${inputjson_attr2}" }

Maximum Buffer Size

1 MB //change the size according to your feeding input flowfile size

Replacement Strategy

Regex Replace

Evaluation Mode

Entire text

we are preparing key/value pairs in replace text processor by searching end of json message and replacing that with inputjson attributes adding the close curly braces at the end.

You can select either of these ways as per your requirements i would suggest to go with using AttributesToJson processor to prepare the new merged json message instead of using ReplaceText processor.

-

If the Answer addressed your question, Click on Accept button below to accept the answer, That would be great help to Community users to find solution quickly for these kind of issues.

[#3]

Thank you @Shu again! I didn't know that attributes where accessible over the flow to use them later. I use the first approach.