Support Questions

Find answers, ask questions, and share your expertise

QueryRecord - Filter out object containing empty array field

New Contributor

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

Super Collaborator

Hi,

 

I think you were close with the last option but did not use the correct syntax with null values where instead of '<> null',  use 'is not null' as follows:

 

select * from FLOWFILE where RPATH(fields, '/field') is not null

 

However when I run that I got the correct result but for some reason a MapRecord Syntax is added to each array element :

 

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

Not sure why this happens but that should get you close to what you are trying to accomplish.

Hope that helps. If it does, please accept solution.

Thanks

 

View solution in original post

3 REPLIES 3

New Contributor

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..

Super Collaborator

Hi,

 

I think you were close with the last option but did not use the correct syntax with null values where instead of '<> null',  use 'is not null' as follows:

 

select * from FLOWFILE where RPATH(fields, '/field') is not null

 

However when I run that I got the correct result but for some reason a MapRecord Syntax is added to each array element :

 

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

Not sure why this happens but that should get you close to what you are trying to accomplish.

Hope that helps. If it does, please accept solution.

Thanks

 

New Contributor

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

 

Thanks!

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.