Complex filter in Loop XPath query in tExtractJSONField

Highlighted
Nine Stars

Complex filter in Loop XPath query in tExtractJSONField

Hi, I am able to do simple filtering in the Loop XPath query in a tExtractJsonField component.  For example, if my input JSON string is:

 

[
 {
   "snack": {
       "name": "Apple",
        "id": 1,
        "type": "Fruit"
   }
 },
 {
   "snack": {
       "name": "Orange",
       "id": 2,
       "type": "Fruit"
   }
 },
 {
   "snack": {
       "name": "Broccoli",
       "id": 3,
       "type": "Vegetable"
   }
 }
]

 

I can use this Loop XPath query with a simple filter to return only the fruit snacks:

 

"/snack[type="Fruit"]"

 

 

I want to do something a little more complex.  I want to select the Apple if the count of vegetables is <=1, else I want to select the Orange.  I tried adding more complex xpath using the count() function but could not get it to work:

 

"/snack[type="Fruit" and (
(name ="Apple" and count(/snack/type="Vegetable") <= 1) or
(name ="Orange" and count(/snack/type="Vegetable") > 1))]"

 

 

Are these more complex filters even supported in Talend?

 

Thanks in advance for any help.

 

--
Please give Kudos and mark topics as solved where appropriate.

Accepted Solutions
Nine Stars

Re: Complex filter in Loop XPath query in tExtractJSONFields

I figured it out.  You can use complex filters like the one I describe.  The exact syntax would be:

 

"/snack[type="Fruit"][
(name ="Apple" and count(//type[text()='Vegetable']) <= 1) or
(name ="Orange" and count(//type[text()='Vegetable']) > 1)]"

 

--
Please give Kudos and mark topics as solved where appropriate.

All Replies
Nine Stars

Re: Complex filter in Loop XPath query in tExtractJSONFields

I figured it out.  You can use complex filters like the one I describe.  The exact syntax would be:

 

"/snack[type="Fruit"][
(name ="Apple" and count(//type[text()='Vegetable']) <= 1) or
(name ="Orange" and count(//type[text()='Vegetable']) > 1)]"

 

--
Please give Kudos and mark topics as solved where appropriate.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch