Jsonpath issue / data format?

Setup, Configuration and Use Items & Sitemaps
1

I have been trying to track the “virus who shall not be named lol” cases here in FL.

I have been pulling the data from this json file from the cdc.
https://www.cdc.gov/coronavirus/2019-ncov/json/us-cases-map-data.json

I am pulling the cases reported from element 10. or atleast that’s what im trying.

if i load the json data in to a jsonpath tool (like this one https://jsonpath.com/)

and do $.[10] i get the florida block. ok, cool, progress. If i do, $.[10].[‘Cases Reported’] i get the return value of the number of cases…

But if i put that path in my item… i get an error saying it’s an invalid path. My guess is that it has to do with the fact that the elements don’t have names, just one after another and maybe im calling it incorrectly.

Anyone with some more jsonpath experience have any ideas?

2

If you show the error message, I think it wil also contain the raw data it is complaining about.

3

Here is the error. Doesn’t tell me much.

org.openhab.core.transform.TransformationException: Invalid path '$[10].['Cases Reported']' in '[{"Jurisdiction":"Alabama","Range":"5001 to 10000","Cases Reported":5831,"Community Transmission":"Yes, defined area(s)","URL": "http://www.adph.org/"},{"Jurisdiction":"Alaska","Range":"101 to 1000","Cases Reported":335,"Community Transmission":"Yes, defined area(s)","URL": "http://dhss.alaska.gov/Pages/default.aspx"},{"Jurisdiction":"American Samoa","Range":"None","Cases Reported":"None","Community Transmission":"N/A","URL": "http://dhss.as/"},{"Jurisdiction":"Arizona","Range":"5001 to 10000","Cases Reported":5459,"Community Transmission":"Yes, widespread","URL": "http://www.azdhs.gov/"},{"Jurisdiction":"Arkansas","Range":"1001 to 5000","Cases Reported":2281,"Community Transmission":"Yes, widespread","URL": "https://www.healthy.arkansas.gov/"},{"Jurisdiction":"California","Range":"10001 or more","Cases Reported":35396,"Community Transmission":"Yes, widespread","URL": "https://www.cdph.ca.gov/"},{"Jurisdiction":"Colorado","Range":"10001 or more","Cases Reported":10825,"Community Transmission":"Yes, widespread","URL": "https://www.colorado.gov/cdphe"},{"Jurisdiction":"Connecticut","Range":"10001 or more","Cases Reported":22469,"Community Transmission":"Yes, widespread","URL": "http://www.ct.gov/dph/site/default.asp"},{"Jurisdiction":"Delaware","Range":"1001 to 5000","Cases Reported":3308,"Community Transmission":"Yes, widespread","URL": "http://www.dhss.delaware.gov/dhss/dph/index.html"},{"Jurisdiction":"District of Columbia","Range":"1001 to 5000","Cases Reported":3206,"Community Transmission":"Undetermined","URL": "http://doh.dc.gov/"},{"Jurisdiction":"Florida","Range":"10001 or more","Cases Reported":27791,"Community Transmission":"Yes, widespread","URL": "http://www.floridahealth.gov/"},{"Jurisdiction":"Georgia","Range":"10001 or more","Cases Reported":20769,"Community Transmission":"Yes, widespread","URL": "http://dph.georgia.gov/"},{"Jurisdiction":"Guam","Range":"101 to 1000","Cases Reported":138,"Community Transmission":"Yes, widespread","URL": "https://dphss.guam.gov/dph/"},{"Jurisdiction":"Hawaii","Range":"101 to 1000","Cases Reported":537,"Community Transmission":"Yes, defined area(s)","URL": "http://health.hawaii.gov/"},{"Jurisdiction":"Idaho","Range":"1001 to 5000","Cases Reported":1802,"Community Transmission":"Yes, widespread","URL": "http://www.healthandwelfare.idaho.gov/"},{"Jurisdiction":"Illinois","Range":"10001 or more","Cases Reported":35108,"Community Transmission":"Yes, widespread","URL": "http://www.idph.state.il.us/"},{"Jurisdiction":"Indiana","Range":"10001 or more","Cases Reported":12438,"Community Transmission":"Undetermined","URL": "http://www.in.gov/isdh/"},{"Jurisdiction":"Iowa","Range":"1001 to 5000","Cases Reported":3748,"Community Transmission":"Yes, widespread","URL": "http://idph.iowa.gov/"},{"Jurisdiction":"Kansas","Range":"1001 to 5000","Cases Reported":2211,"Community Transmission":"Yes, defined area(s)","URL": "http://www.kdheks.gov/"},{"Jurisdiction":"Kentucky","Range":"1001 to 5000","Cases Reported":3373,"Community Transmission":"Undetermined","URL": "https://chfs.ky.gov/agencies/dph/Pages/default.aspx"},{"Jurisdiction":"Louisiana","Range":"10001 or more","Cases Reported":25317,"Community Transmission":"Yes, widespread","URL": "http://dhh.louisiana.gov/"},{"Jurisdiction":"Maine","Range":"101 to 1000","Cases Reported":907,"Community Transmission":"Yes, widespread","URL": "http://www.maine.gov/dhhs/index.shtml"},{"Jurisdiction":"Marshall Islands","Range":"None","Cases Reported":"None","Community Transmission":"N/A","URL": ""},{"Jurisdiction":"Maryland","Range":"10001 or more","Cases Reported":15737,"Community Transmission":"Yes, widespread","URL": "http://dhmh.maryland.gov/Pages/Index.aspx"},{"Jurisdiction":"Massachusetts","Range":"10001 or more","Cases Reported":42944,"Community Transmission":"Yes, widespread","URL": "https://www.mass.gov/orgs/department-of-public-health"},{"Jurisdiction":"Michigan","Range":"10001 or more","Cases Reported":33966,"Community Transmission":"Yes, widespread","URL": "http://www.michigan.gov/mdch"},{"Jurisdiction":"Micronesia","Range":"None","Cases Reported":"None","Community Transmission":"N/A","URL": "http://www.fsmgov.org/ngovt.html"},{"Jurisdiction":"Minnesota","Range":"1001 to 5000","Cases Reported":2721,"Community Transmission":"Yes, widespread","URL": "http://www.health.state.mn.us/"},{"Jurisdiction":"Mississippi","Range":"5001 to 10000","Cases Reported":5153,"Community Transmission":"Yes, widespread","URL": "http://www.msdh.state.ms.us/"},{"Jurisdiction":"Missouri","Range":"5001 to 10000","Cases Reported":6137,"Community Transmission":"Yes, widespread","URL": "http://health.mo.gov/index.php"},{"Jurisdiction":"Montana","Range":"101 to 1000","Cases Reported":442,"Community Transmission":"Yes, defined area(s)","URL": "http://dphhs.mt.gov/"},{"Jurisdiction":"Nebraska","Range":"1001 to 5000","Cases Reported":1813,"Community Transmission":"Yes, widespread","URL": "http://dhhs.ne.gov/Pages/default.aspx"},{"Jurisdiction":"Nevada","Range":"1001 to 5000","Cases Reported":4081,"Community Transmission":"Yes, widespread","URL": "http://dpbh.nv.gov/"},{"Jurisdiction":"New Hampshire","Range":"1001 to 5000","Cases Reported":1588,"Community Transmission":"Yes, widespread","URL": "http://www.dhhs.nh.gov/"},{"Jurisdiction":"New Jersey","Range":"10001 or more","Cases Reported":95865,"Community Transmission":"Yes, widespread","URL": "http://www.state.nj.us/health/"},{"Jurisdiction":"New Mexico","Range":"1001 to 5000","Cases Reported":2210,"Community Transmission":"Yes, widespread","URL": "http://nmhealth.org/"},{"Jurisdiction":"New York","Range":"10001 or more","Cases Reported":253219,"Community Transmission":"Yes, widespread","URL": "https://www.health.ny.gov/"},{"Jurisdiction":"North Carolina","Range":"5001 to 10000","Cases Reported":7220,"Community Transmission":"Yes, widespread","URL": "http://www.ncdhhs.gov/"},{"Jurisdiction":"North Dakota","Range":"101 to 1000","Cases Reported":679,"Community Transmission":"Yes, defined area(s)","URL": "http://www.ndhealth.gov/"},{"Jurisdiction":"Northern Marianas","Range":"1 to 100","Cases Reported":14,"Community Transmission":"Undetermined","URL": "http://chcc.gov.mp/"},{"Jurisdiction":"Ohio","Range":"10001 or more","Cases Reported":14117,"Community Transmission":"Yes, defined area(s)","URL": "https://www.odh.ohio.gov/"},{"Jurisdiction":"Oklahoma","Range":"1001 to 5000","Cases Reported":2894,"Community Transmission":"Yes, widespread","URL": "https://www.ok.gov/health/"},{"Jurisdiction":"Oregon","Range":"1001 to 5000","Cases Reported":2059,"Community Transmission":"Yes, widespread","URL": "http://www.oregon.gov/oha/ph/pages/index.aspx"},{"Jurisdiction":"Palau","Range":"None","Cases Reported":"None","Community Transmission":"N/A","URL": "http://www.palauhealth.org/"},{"Jurisdiction":"Pennsylvania","Range":"10001 or more","Cases Reported":35684,"Community Transmission":"Yes, defined area(s)","URL": "https://www.health.pa.gov/Pages/default.aspx"},{"Jurisdiction":"Puerto Rico","Range":"1001 to 5000","Cases Reported":1213,"Community Transmission":"Undetermined","URL": "http://www.salud.gov.pr/Pages/Home.aspx"},{"Jurisdiction":"Rhode Island","Range":"5001 to 10000","Cases Reported":6012,"Community Transmission":"Yes, widespread","URL": "http://www.health.state.ri.us/"},{"Jurisdiction":"South Carolina","Range":"1001 to 5000","Cases Reported":4761,"Community Transmission":"Yes, widespread","URL": "http://www.scdhec.gov/"},{"Jurisdiction":"South Dakota","Range":"1001 to 5000","Cases Reported":1858,"Community Transmission":"Yes, widespread","URL": "http://doh.sd.gov/"},{"Jurisdiction":"Tennessee","Range":"5001 to 10000","Cases Reported":7572,"Community Transmission":"Yes, defined area(s)","URL": "https://www.tn.gov/health.html"},{"Jurisdiction":"Texas","Range":"10001 or more","Cases Reported":21069,"Community Transmission":"Yes, defined area(s)","URL": "https://www.dshs.state.tx.us/"},{"Jurisdiction":"Utah","Range":"1001 to 5000","Cases Reported":3540,"Community Transmission":"Yes, widespread","URL": "https://health.utah.gov/"},{"Jurisdiction":"Vermont","Range":"101 to 1000","Cases Reported":823,"Community Transmission":"Yes, widespread","URL": "http://healthvermont.gov/"},{"Jurisdiction":"Virgin Islands","Range":"1 to 100","Cases Reported":54,"Community Transmission":"Yes, defined area(s)","URL": "https://doh.vi.gov/"},{"Jurisdiction":"Virginia","Range":"10001 or more","Cases Reported":10998,"Community Transmission":"Yes, widespread","URL": "http://www.vdh.state.va.us/"},{"Jurisdiction":"Washington","Range":"10001 or more","Cases Reported":12494,"Community Transmission":"Yes, widespread","URL": "http://www.doh.wa.gov/"},{"Jurisdiction":"West Virginia","Range":"101 to 1000","Cases Reported":963,"Community Transmission":"Yes, defined area(s)","URL": "http://www.dhhr.wv.gov/bph/Pages/default.aspx"},{"Jurisdiction":"Wisconsin","Range":"1001 to 5000","Cases Reported":4845,"Community Transmission":"Yes, widespread","URL": "https://www.dhs.wisconsin.gov/"},{"Jurisdiction":"Wyoming","Range":"101 to 1000","Cases Reported":447,"Community Transmission":"Yes, defined area(s)","URL": "http://health.wyo.gov/"}]
'
        at org.openhab.core.transform.TransformationHelper$TransformationServiceDelegate.transform(TransformationHelper.java:67) ~[196:org.openhab.core.compat1x:2.4.0]
        at org.openhab.binding.http.internal.HttpBinding.execute(HttpBinding.java:194) [267:org.openhab.binding.http:1.13.0]
        at org.openhab.core.binding.AbstractActiveBinding$BindingActiveService.execute(AbstractActiveBinding.java:144) [196:org.openhab.core.compat1x:2.4.0]
        at org.openhab.core.service.AbstractActiveService$RefreshThread.run(AbstractActiveService.java:166) [196:org.openhab.core.compat1x:2.4.0]

Atleast not that I can see other than it doesn’t like my path. But the path works on the validator tools I have found… so I can only imagine i have something messed up by the way i am doing it in openhab.

4

I tried using a jsonpath path builder here.

http://jsonpathfinder.com/

and shows that [10][‘Cases Reported’] should return what im looking for.

I created a new string item that will take in whatever. So far, no matter what path i put it, it’s not drilling down to the 10th element.

5

I did try with the Jasonpath online evaluator with $.[10]['Cases Reported'] no dot between the number and the object

What you could try it changing the quotes " or ’ if I use double quotes it in the evaluator it doesn’t work may OH requires the double quotes.

Worth a try.

6

No, it was for our benefit. We cannot see what you see.

JSONPATH transformation service is not JSONPATH, and has differences e.g. in the way it handles arrays. The return is always just a plain string, for example.

The following params works for me -

// test data, just two states
val rawjson = '[{"Jurisdiction":"Alabama","Range":"5001 to 10000","Cases Reported":5831,"Community Transmission":"Yes, defined area(s)","URL": "http://www.adph.org/"},{"Jurisdiction":"Alaska","Range":"101 to 1000","Cases Reported":335,"Community Transmission":"Yes, defined area(s)","URL": "http://dhss.alaska.gov/Pages/default.aspx"}]'
// showing how to select element of list by position
var results = transform("JSONPATH", "$.[1].Jurisdiction", rawjson)
logInfo("test", "return " + results)
// showing how to use a key with spaces, this is a tricky one
results = transform("JSONPATH", "$.[1].['Cases Reported']", rawjson)
logInfo("test", "return " + results)
// showing how to select list element by content
results =  transform("JSONPATH", "$.[?(@.Jurisdiction=='Alaska')].['Cases Reported']", rawjson)
logInfo("test", "return " + results)

log

2020-04-24 20:38:51.012 [INFO ] [.eclipse.smarthome.model.script.test] - return Alaska
2020-04-24 20:38:51.020 [INFO ] [.eclipse.smarthome.model.script.test] - return 335
2020-04-24 20:38:51.022 [INFO ] [.eclipse.smarthome.model.script.test] - return 335
2 Likes
7

so i could just all the data that comes in, in to a string item (like i have in my _test item) and then trigger the rule.

I thought the path and the transform were the same, which i think is where i got confused. Ill give this a try!

8

You never showed us that, so I can’t comment on it.

That’s one way.

Another way is to exploit the http cache feature (see the docs) to fetch the JSON package, then configure individual Items to pick values out with JSONPATH transforms (without doing more http requests). Same end result, no rule.

9

I must be missing something.

here is my rule.

rule "Corona FL"

when
    Item CoronaFlorida_raw received update 
then
    var rawjson = triggeringItem.state.toString 
    var results = transform("JSONPATH", "$[10].['Cases Reported']", rawjson)
    logInfo("corona", "return " + results)
end

no errors… but in the log, i get "return " and the entire json package again. the transform doesn’t appear to be working in the rule for me. results = rawjson

10

oooohhh very interesting. If i hard code rawjson to match the raw json in the string item. it does work. But not when I try to parse it from the raw http object.

item

String CoronaFlorida_raw  { http="<[https://www.cdc.gov/coronavirus/2019-ncov/json/us-cases-map-data.json:30000:REGEX((.*))]" }

The above rule fails. But if instead of loading the triggeringitem state in to a var. I paste the exact same contents it works correctly. I am unsure what the difference is.

11

I upgraded to 2.5.4 today. No change in the behavior. Is there something i should do to the string value before trying the transform? Maybe encoding or something? Just very strange that the value copied directly from the log and pasted in as a var will work, but coming from the http binding the transform refuses to parse. No errors, just returns the same data as is fed in. Like it can’t see the json itself

12

By design, that is what all the transformation services do in case of error.

There’s probably something in your source like extra quotemarks, newlines, etc. that get hidden in the logged version (which has also been pre-processed by the transformation). Perhaps you could do a character count of the source string and transform results to see a difference.

13

i have taken that raw data from the json feed and put in all kinds of validator tools and have not see any issues.

I tried copying straight from the source and pasting in to my rule to test and it works.

It seems there is something with the http binding -> rule. it doesn’t seem to like that data from the binding.

Could it be that it’s not coming in as a string, but as an object. maybe the mime type is not just “plain text”? gonna check that now. Doesn’t seem to make sense, but who knows.

14

Ok. i am baffled. I have no idea what the difference is. But i found another json source and have updated my item to the following.

Number CoronaFlorida "Florida Corona Cases [%s]" {http="<[https://corona-stats.online/states/US?format=json:30000:JSONPATH($.data.[?(@.state=='Florida')].cases)]"}

This works no prob. The raw data, when punched in the browser for both this json feed and the other does have 2 differences I can see right off the bat, but neither make much sense as to why it would or wouldn’t work.

  1. the original one from the CDC wraps the entire dataset in [ ] this one does not.
  2. the second one starts the json with a data: block. the CDC doesn’t.

Only thing i can think of is that one is an array of json objects. where the other is a straight json object.

Any thoughts?

15

I used your url with sendHttpGetRequest and logging out the value does a weird font change in my log viewer.
Copy/paste this log line to VSC editor reveals an unprintable character in front of the leading [
I think you could use a REGEX to pre-process and select between [ … ] - but I don’t know how to use regex.

My clumsy workaround

val scrape = sendHttpGetRequest("https://www.cdc.gov/coronavirus/2019-ncov/json/us-cases-map-data.json")
logInfo ("test", "scrape " + scrape)
   // need to escape [
val scrapebodge = scrape.split("\\[")
logInfo("test", "spoiler >" + scrapebodge.get(0) + "< length " + scrapebodge.get(0).length.toString)
var scrapetweak = "[" + scrapebodge.get(1)
var results = transform("JSONPATH", "$.[?(@.Jurisdiction=='Florida')].['Cases Reported']", scrapetweak)
logInfo("test", "return " + results)

jsonlog
jsonlog711×257 51.1 KB

EDIT - a getBytes on the spoiler character gives [-17, -69, -65] corresponding to EF, BB, BF which seems to be UTF-8 for “ZERO WIDTH NO-BREAK SPACE”. I think we called that nul in teletype code years ago.