JsonPathOnUrl & filters


#1

Hello,

I am trying to use the fonction JsonPathOnUrl but I get some errors message. I guess there is a problem with the JsonPath I am using.

Here is an example with this URL : http://magicseo.fr/test.json

I need to get the value of Nq only if Db = fr. So I have this JsonPath :
$..[?(@.Db=='fr')].Nq

which works if I try on this JsonPath evaluator :
chrome_2018-08-10_09-50-24

But if I use the same syntax on Excel
=JsonPathOnUrl("http://magicseo.fr/test.json";"$..[?(@.Db=='fr')].Nq")

I get an error
EXCEL_2018-08-10_09-53-14

Any idea of what I could do to make it work ?

Thanx !


#2

This syntax works for me:

image

To be honest, I'm not exactly such why your JsonPath doesn't work.


#3

@diskborste:
Unfortunately your screenshot doesn't show up anymore. Cann you post your solution again? I'm having the same issue as @CydZ had.

Thanks a lot!


#4

Yeah, sorry about the lost images.

Here is a working formula for me:
=JsonPathOnUrl(A1;"$.this.data[?(@.Db=='fr')].Nq")

Where cell A1 contains the link


#5

Thank your for the response.

In my case it's not working.

This is the json

{
   "filters":{
  "all":[
     {
        "uiTextKey":"color",
        "values":[
           {
              "name":"Blau",
              "active":false
           },
           {
              "name":"Braun",
              "active":true
           },
           {
              "name":"Bunt",
              "active":false
           }
        ]
     }
  ]   
}

I'm trying to get the name-attribute of the object, which is active (active == true) - so my jsonpath is:
$..[?(@.active == true)].name

But then I get the error:
"Failed to evaluate expression '@.active==true': The Object must implement IConvertible'

Any idea why this is happening?


#6

Can you try and use the full path instead of ".."? This is what I did to make it work in the example above.


#7

Tried to do it this way: $.filters.all.[?(@.active==true)].name
Doesn't work either!

Error Message: Expection - InvalidCastException
The Object from Type "Newtonsoft.Json.Linq.JValue" can not be transformed into the type "Newtonsoft.Json.Linq.JContainer"


#8

Weird. Can you provide the json link so I can try? Also, what version of Seotools are you using?


#9

Uploaded one example here: https://api.myjson.com/bins/v7lcg

Thanks in advance!


#10

That json array is trucky because of many all elements. This works for me:
=JsonPathOnUrl(A1;"$.filters.all[*].values[?(@.active==true)].name")


#11

It worked perfectly - thank you! :slight_smile:


#12

Hi guys,

sorry I don't know why my images don't show up anymore.

Thanks to your messages, I also managed to do what I needed.

Here is the formula that works for me :

=JsonPathOnUrl("http://magicseo.fr/test.json";"$.this.data[?(@.Db=='fr')].Nq")