Mapping JSON Data with the json.path() Function

Use the json.path() function to query an element within JSON data. The JSON data being queried can come from the output of an Activity or trigger. In the mapper, you can use the json.path() function by itself when providing value to an input parameter or use it within expressions to refer to data within a JSON structure.

This function takes two arguments:

  • the search path to the element within the JSON data
  • the JSON object that contains the JSON data you are searching
You can specify a filter to be used by the json.path() function to narrow down the results returned by the json.path() function.

In order to reach the desired node or a specific field in the node in the JSON data, you must follow a specific notation defined in the JsonPath specification. Refer to https://github.com/oliveagle/jsonpath for details on the notation to be used and specific examples of using the notation.

Consider the example below which is available for you to experiment with at https://github.com/TIBCOSoftware/tci-flogo/tree/master/samples/app-dev/json.path.sample.

Examples

The following is an example of how to use the function: 

json.path("$.store.book[?(@.price > 10)].title", $flow.body)

In this example, $.store.book[?(@.price > 10)].title is the query path. [?(@.price > 10)] is a filter used to narrow down the query results. $flow.body is the JSON object against which the query is run (in this case the JSON object comes from the flow input, hence $flow). So, this query searches the books array within the $flow.body JSON object and returns the title of the books whose price is more than $10.

Consider the following sample JSON data:

{
  "store": {
    "book": [
      {
        "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "Availability": [
          {
            "Country": "India",
            "Quantity": 4000,
            "Address": [
              {
                "city": "houston"
              }
            ]
          }
        ],
        "price": 8.95
      },
      {
        "category": "fiction2",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "Availability": [
          {
            "Country": "USA",
            "Quantity": 5000,
            "Address": [
              {
                "city": "sugarland"
              }
            ]
          }
        ],
        "price": 12.99
      },
      {
        "category": "fiction3",
        "author": "Herman Melville",
        "title": "Moby Dick",
        "isbn": "0-553-21311-3",
        "Availability": [
          {
            "Country": "UK",
            "Quantity": 7000,
            "Address": [
              {
                "city": "stafford"
              }
            ]
          }
        ],
        "price": 8.99
      },
      {
        "category": "fiction4",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "Availability": [
          {
            "Country": "Australia",
            "Quantity": 2000,
            "Address": [
              {
                "city": "aaaaa"
              }
            ]
          }
        ],
        "price": 22.99
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  },
  "expensive": 10
}

The following are examples of some JSON query paths that search the JSON data above and return the category of the book. In the examples below, the second input parameter for this function, data is the name of the file that contains the above JSON code.

  • json.path("$.store.book[?(@.Availability[?(@.Quantity >= 6000)])].category", $flow.data)

    In the example above, the query scope is the entire book array. The filter used to query this array is the condition, [?(@.Availability[?(@.Quantity >= 6000)])]. Only the category values for the book elements that have Quantity >= 6000 is returned. So, this query returns fiction3.

  • json.path("$.store.book[?(@.author == 'Nigel Rees')].category", $flow.data)

    returns reference since it uses the filter [?(@.author == 'Nigel Rees')] and the only book authored by Nigel Rees in this array of books has its category as reference.

  • json.path("$.store.book[?(@.Availability[?(@.Address[?(@.city == 'sugarland')])])].category", $flow.data)

    This query is an example of a nested filter where [?(@.Availability[?(@.Address[?(@.city == 'sugarland')])])] is the outer filter and the nested filter within it is [?(@.city == 'sugarland')]. It returns reference.

  • json.path("$.store.book[0].category", $flow.data)

    This query does not use a filter. It returns reference, since your query scope is limited to the book[0] element only within the store object and your request is to return the value of category.