Qlik Talend Data Integration: Using JSONPath expressions to filter JSON data

[TalendSolutionExpert]

JSON data returned from a web service often contains a single parent object, which wraps child objects or arrays. If the parent object is used directly, the response will not display any detail records.

When using tExtractJSONFields to extract JSON data from a JSON string or using tFileInputJSON to read a JSON file, if the number of array elements is very large, it will take up a lot of resources. Sometimes you only need to extract JSON data that matches specific criteria, and it is more convenient to return an array of child objects instead of a parent object.

Resolution

Use JSONPath expressions in the Loop Json query field to query and filter JSON data from a JSON file.

Example JSON file:

{
  "array": [
    {
      "filename": "CoverLetter.docx",
      "url": "",
      "type": "cover_letter",
      "created_at": "2022-01-03T19:09:12.839Z",
      "price": 8.95
    },
    {
      "filename": "Resume1.pdf",
      "url": "",
      "type": "resume",
      "created_at": "2022-02-03T19:09:12.602Z",
      "price": 10.11
    },
    {
      "filename": "image.jpg",
      "url": "",
      "type": "image",
      "created_at": "2023-02-27T13:01:04.786Z",
      "price": 8.85
    },
       {
      "filename": "Resume2.pdf",
      "url": "",
      "type": "resume",
      "created_at": "2022-03-04T19:09:12.602Z",
      "price": 11.11
    }
  ]
}

Example 1: Filter the JSON data only when the type is 'resume'.

$.array[?(@.type=='resume')]

Expected result:



Example 2: Filter the JSON data only when the type is 'resume' and created_at is <'2022-03-01'.

$.array[?(@.type=='resume'&&@.created_at<'2022-03-01')]

Expected result:


 
For more information about JSONPath syntax, see the JSONPath - XPath for JSON page on goessner.net.

Environment

• Talend Data Integration 7.3.1, 8.0.1