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

View solution in original post


All Replies
Highlighted
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.

View solution in original post

2019 GARTNER 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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog