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.