Need JSONPath help... Extract Variables not working as expected :-(

[pdouglas]

So close yet so far... I can return the resourceIds (extracted.StreamIds) but not a filtered list of "where isActive==true" (extracted.activeStreamIds).

This is grabbing a response from a ServiceCallout. Do I need to explicitly set the response to be application/json and if so, how would I do that?

<JSONPayload>
<Variable name="extracted.activeStreamIds">
<JSONPath>$.[?(@.isActive==true)].resourceId</JSONPath>
</Variable>
<Variable name="extracted.StreamIds">
<JSONPath>$..resourceId</JSONPath>
</Variable>
</JSONPayload>

Thanks!

[dchiesa1 · Accepted solution]

hmm ok I've looked a little further.

using this example JSON

[
    {
      "isActive" : "false",
      "resourceId" : 1
    },
    {
      "isActive" : "false",
      "resourceId" : 2
    },
    {
      "isActive" : "true",
      "resourceId" : 3
    }
]

I have found that this jsonpath query works:

$[?(@.isActive == 'true')].resourceId

It returns an array with a single element, 3.

But using a json with boolean values instead of string values, like this:

[
    {
      "isActive" : false,
      "resourceId" : 1
    },
    {
      "isActive" : false,
      "resourceId" : 2
    },
    {
      "isActive" : true,
      "resourceId" : 3
    }
]

I could not find a query that worked as desired.

This does not work:

$[?(@.isActive == true)].resourceId

This query checks for the existence of the isActive property, not for its value:

$[?(@.isActive)].resourceId

...and yields [1,2,3], which I think is not what you want.

I think this is a limitation of the v0.8.0 jayway JSON Path library currently used by Apigee.

If I try this query

$[?(@.isActive == true)].resourceId

...with v2.4.0 of jayway, against a JSON file that uses boolean literals and not strings, it works as expected, returns [3] .

But you can't ask to use v2.4.0 of jayway in ExtractVariables. It is currently locked to v0.8.0.

There are other people who have asked for an upgrade to a current version of jsonpath, but for now it's not possible. The ticket that tracks the request to allow a more modern jsonpath is b/132486339 .

The workarounds available to you as I see them:

1. use a JS-based jsonpath, (import a jsonpath JS module and do it in a JS callout)
2. use a Java callout that takes advantage of a more recent jsonpath library, like this one: https://github.com/DinoChiesa/ApigeeEdge-Java-JSONPath (I built this to address similar limitations for other customers)
3. pre-process your JSON to replace all boolean literals with strings, and THEN use the ExtractVariables with the string fiilter syntax
4. transform the JSON to XML and use xpath

[pdouglas]

My first question should be, is Apigee's JSONPath support not fully functional in terms of complex queries? Thanks

[dchiesa1]

I can infer it, but can you show me some example JSON just so I can be absolutely clear?

[dchiesa1 · Accepted solution]

hmm ok I've looked a little further.

using this example JSON

[
    {
      "isActive" : "false",
      "resourceId" : 1
    },
    {
      "isActive" : "false",
      "resourceId" : 2
    },
    {
      "isActive" : "true",
      "resourceId" : 3
    }
]

I have found that this jsonpath query works:

$[?(@.isActive == 'true')].resourceId

It returns an array with a single element, 3.

But using a json with boolean values instead of string values, like this:

[
    {
      "isActive" : false,
      "resourceId" : 1
    },
    {
      "isActive" : false,
      "resourceId" : 2
    },
    {
      "isActive" : true,
      "resourceId" : 3
    }
]

I could not find a query that worked as desired.

This does not work:

$[?(@.isActive == true)].resourceId

This query checks for the existence of the isActive property, not for its value:

$[?(@.isActive)].resourceId

...and yields [1,2,3], which I think is not what you want.

I think this is a limitation of the v0.8.0 jayway JSON Path library currently used by Apigee.

If I try this query

$[?(@.isActive == true)].resourceId

...with v2.4.0 of jayway, against a JSON file that uses boolean literals and not strings, it works as expected, returns [3] .

But you can't ask to use v2.4.0 of jayway in ExtractVariables. It is currently locked to v0.8.0.

There are other people who have asked for an upgrade to a current version of jsonpath, but for now it's not possible. The ticket that tracks the request to allow a more modern jsonpath is b/132486339 .

The workarounds available to you as I see them:

1. use a JS-based jsonpath, (import a jsonpath JS module and do it in a JS callout)
2. use a Java callout that takes advantage of a more recent jsonpath library, like this one: https://github.com/DinoChiesa/ApigeeEdge-Java-JSONPath (I built this to address similar limitations for other customers)
3. pre-process your JSON to replace all boolean literals with strings, and THEN use the ExtractVariables with the string fiilter syntax
4. transform the JSON to XML and use xpath

[pdouglas]

Spectacular answer @Dino-at-Google. Yes it is a boolean active flag. We will update this post with our chosen solution when it is done. Many thanks for your time, this is an excellent explanation and workaround(s).

[dchiesa1]

Thanks, I'll be interested to hear what you choose.

[pdouglas]

We chose #2 from Dino's answer, see below. I'd recommend this for anyone that finds Extract Variables is not behaving with complex JSONPath queries. Thanks, Dino!

[dchiesa1 · Accepted solution]

Glad to help, Peter!

[pdouglas]

We used the JAR solution again for filtering a response, it is easy to add and play with. @dchiesa1 thanks again!

[dchiesa1]

Of course, glad to help.  

BTW, just an update.  The JSONPath capability in Apigee X and hybrid, including as accessible from ExtractVariables or message templates, is significantly updated. And it should be fully capable at this point. There's no plan to add that updated JSONPath to Apigee Edge. The Java JAR approach will continue to work there.

[akansson]

Hi,

I was previously using json-path version 2.6.0 in my project, and the logs were being printed to the console. However, after upgrading to version 2.9.0, the logs are no longer being printed.

Could someone please help resolve this issue?

Thanks.

[dchiesa1]

>  was previously using json-path version 2.6.0 in my project, and the logs were being printed to the console. 

which project?  and which console?  Can you elaborate?  Are you using Apigee?

[akansson]

I am working on a Spring Boot project with JDK 17. I upgraded the JSON-Path version to 2.9.0, but when I started the Apache Tomcat server and checked the logs in the terminal, the logs were not being printed. However, when I used the same JSON-Path library version 2.9.0 in a Spring Boot project with JDK 8, the logs were printed in the console upon starting the Apache Tomcat server.

I checked the release notes for JSON-Path v2.9.0, but there were no changes listed related to logging.

[dchiesa1]

Hi, I think maybe you're not using Apigee. This forum is dedicated to Apigee, so you're asking your question in the wrong place. Maybe try to find a forum that is dedicated to spring boot, or running applications with jdk17, and ask your question there ..?