Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

QueryRecord - Filter out object containing empty array field

avatar
Explorer

Hi Team,

 

Using QueryRecord to filter the incoming array of objects where each object has an array field.

The field can be an empty array or it contains some objects in it but it is not null. Tried a lot of SQL queries but didn't succeed. Also, I am trying to consider Record based approach as I want to work on multiple flow files instead of single ones

 

In Actual flow wanted to use ForkRecord on this array but it outputs an empty array if any object contains an empty array in the path so had to use QueryRecord before ForkRecord to filter those empty arrays. Not sure if it's the bug/expected behavior in ForkRecord

 

Flow:

rk1337_0-1665907880934.png

 

GenerateFlowFile:

rk1337_1-1665907918180.png

Custom Text:

 

[
	{
		"fields": [
			{
				"field": "field1"
			},
			{
				"field": "field1"
			}
		]
	},
	{
		"fields": []
	},
	{
		"fields": [
			{
				"field": "field2"
			},
			{
				"field": "field2"
			}
		]
	},
	{
		"fields": [
			{
				"field": "field3"
			},
			{
				"field": "field3"
			}
		]
	}
]

 


Expected output: To filter out the 2nd object whose field is an empty array

QueryRecord:

rk1337_2-1665908172357.png

Record Reader and Writer services are set to infer the schema

 

Solutions Tried:

 

1.

rk1337_5-1665908350013.png

 rk1337_4-1665908302361.png

 

2.

rk1337_6-1665908398143.pngrk1337_7-1665908415324.png

 

3.

rk1337_8-1665908522066.png

No Error but didn't filter out

rk1337_9-1665908570058.png

 

4.

rk1337_10-1665908625286.pngrk1337_11-1665908638741.png


5.

rk1337_12-1665908799411.png

No Error but didn't filter out

Using RPATH I was not able to pick specifically the first element to check if it exists or not

 

rk1337_13-1665908979331.pngrk1337_14-1665908992949.png

 

 

 

Got stuck because of this particular processor. Please help me out with the problem and let me know if any better way to do this with other processors. I'm avoiding working with single-flow files as am aware of the EvaluateJsonPath -> RouteOnAttribute solution and want to work specifically on Record based.

 

 

 

@SAMSAL @MattWho @araujo 

1 ACCEPTED SOLUTION

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login
3 REPLIES 3

avatar
Explorer

I have resolved the issue using ScriptedFilterRecord. I thought it is similar to the ExecuteScript processor and might include a long script with boiler code but in the former processor a single-line code did the job

 

Would still like to know if QueryRecord can solve this problem or not..

avatar
hide-solution

This problem has been solved!

Want to get a detailed solution you have to login/registered on the community

Register/Login

avatar
Explorer

Thanks for the reply, it worked 🙂 
Also, I got the expected output and didn't see the `MapRecord` as displayed in your output

 

Thanks!