JSONPATH and nested JSON data

Add-ons Other
1

Dear Community,

I use openhab 3.3 with configuration in different files.

I try to extract data from a nested JSON structure. I always got the message serviceName=‘JSONPATH’} failed: Invalid path

Here are the JSON data
{
“result”: {
“watts”: {
“2022-11-03 07:21:00”: 0,
“2022-11-03 08:00:00”: 3,
“2022-11-03 09:00:00”: 20,
“2022-11-03 10:00:00”: 46,
“2022-11-03 11:00:00”: 68,
“2022-11-03 12:00:00”: 87,
“2022-11-03 13:00:00”: 96,
“2022-11-03 14:00:00”: 96,
“2022-11-03 15:00:00”: 254,
“2022-11-03 16:00:00”: 341,
“2022-11-03 16:56:00”: 0,
“2022-11-04 07:23:00”: 0,
“2022-11-04 08:00:00”: 30,
“2022-11-04 09:00:00”: 223,
“2022-11-04 10:00:00”: 601,
“2022-11-04 11:00:00”: 920,
“2022-11-04 12:00:00”: 1245,
“2022-11-04 13:00:00”: 1422,
“2022-11-04 14:00:00”: 1412,
“2022-11-04 15:00:00”: 1397,
“2022-11-04 16:00:00”: 985,
“2022-11-04 16:54:00”: 0
},
“watt_hours_period”: {
“2022-11-03 07:21:00”: 0,
“2022-11-03 08:00:00”: 1,
“2022-11-03 09:00:00”: 12,
“2022-11-03 10:00:00”: 33,
“2022-11-03 11:00:00”: 57,
“2022-11-03 12:00:00”: 78,
“2022-11-03 13:00:00”: 92,
“2022-11-03 14:00:00”: 96,
“2022-11-03 15:00:00”: 175,
“2022-11-03 16:00:00”: 298,
“2022-11-03 16:56:00”: 159,
“2022-11-04 07:23:00”: 0,
“2022-11-04 08:00:00”: 9,
“2022-11-04 09:00:00”: 127,
“2022-11-04 10:00:00”: 412,
“2022-11-04 11:00:00”: 761,
“2022-11-04 12:00:00”: 1083,
“2022-11-04 13:00:00”: 1334,
“2022-11-04 14:00:00”: 1417,
“2022-11-04 15:00:00”: 1405,
“2022-11-04 16:00:00”: 1191,
“2022-11-04 16:54:00”: 443
},
“watt_hours”: {
“2022-11-03 07:21:00”: 0,
“2022-11-03 08:00:00”: 1,
“2022-11-03 09:00:00”: 13,
“2022-11-03 10:00:00”: 46,
“2022-11-03 11:00:00”: 103,
“2022-11-03 12:00:00”: 181,
“2022-11-03 13:00:00”: 273,
“2022-11-03 14:00:00”: 369,
“2022-11-03 15:00:00”: 544,
“2022-11-03 16:00:00”: 842,
“2022-11-03 16:56:00”: 1001,
“2022-11-04 07:23:00”: 0,
“2022-11-04 08:00:00”: 9,
“2022-11-04 09:00:00”: 136,
“2022-11-04 10:00:00”: 548,
“2022-11-04 11:00:00”: 1309,
“2022-11-04 12:00:00”: 2392,
“2022-11-04 13:00:00”: 3726,
“2022-11-04 14:00:00”: 5143,
“2022-11-04 15:00:00”: 6548,
“2022-11-04 16:00:00”: 7739,
“2022-11-04 16:54:00”: 8182
},
“watt_hours_day”: {
“2022-11-03”: 1001,
“2022-11-04”: 8182
}
},
“message”: {
“code”: 0,
“type”: “success”,
“text”: “”,
“info”: {
“latitude”: 51.7978,
“longitude”: 8.6123,
“distance”: 0,
“place”: “H\u00f6velhof, Paderborn, Nordrhein-Westfalen”,
“timezone”: “Europe/Berlin”,
“time”: “2022-11-03T16:52:56+01:00”,
“time_utc”: “2022-11-03T15:52:56+00:00”
},
“ratelimit”: {
“period”: 3600,
“limit”: 12,
“remaining”: 11
}
}

I tried to extract the data from ‘watt_hours_day’

Till now I am not successful

Thing http:url:weatherforecast “Weather Forecast” [
baseURL=“https://api.forecast.solar/estimate/51.798/8.612/38/40/10.2”,
refresh=3600,
bufferSize=4096,
authMode=“BASIC”,
stateMethod=“GET”,
commandMethod=“GET”,
ignoreSSLErrors=true] {

Channels:
Type string : forecast [ stateTransformation=“JSONPATH:$.result.watt_hours_day.[1]”, mode=“READONLY” ]
Type string : forecast [ stateTransformation=“JSONPATH:$.result[4].[1]”, mode=“READONLY” ]
Type string : forecast [ stateTransformation=“JSONPATH:$.result.[4].[1]”, mode=“READONLY” ]
Type string : forecast [ stateTransformation=“JSONPATH:$.[‘result’].[‘watt_hours_day’][1]”, mode=“READONLY” ]
}

Any hint for me? Thanks in advance.

2

Please can you edit your comment and use code fences for the JSON and Thing configuration data. Please RE-COPY from the original sources, otherwise there’s risk of characters being wrong (like the ")

3

Umm, they need unique names. Quite what you get when re-defining one channel many times, I don’t know. Just the last version I expect, which looks wrong to me.

4

Oh. I am sorry, not described clearly, my fault.

I tried these different channel definitions one after the after not at the same time.

5

Sorry, you are right. Here are the different code parts.

// weather forecast
String sforecast_today          "wettervorhersage today [%s]"
                                { channel="http:weatherforecast:forecast_today:string" }
String sforecast_tomorrow        "wettervorhersage tomorrow [%s]"
                                { channel="http:weatherforecast:forecast_tomorrow:string" }


Thing http:url:weatherforecast "Weather Forecast" [
        baseURL="https://api.forecast.solar/estimate/51.798/8.612/38/40/10.2",
        refresh=3600,
        bufferSize=4096,
        authMode="BASIC",
        stateMethod="GET",
        commandMethod="GET",
        ignoreSSLErrors="true"] {



   Channels:
      Type string : forecast_today        [ stateTransformation="JSONPATH:$.result.watt_hours_day.[0]", mode="READONLY" ]
      Type string : forecast_tomorrow     [ stateTransformation="JSONPATH:$.result.watt_hours_day.[1]", mode="READONLY" ]
}
6

The channel name is just “http:weatherforecast:forecast_today”

7

OK. I removed it but it dosen’t work. I got the following error message

2022-11-04 14:37:02.185 [WARN ] [ui.internal.items.ItemUIRegistryImpl] - Exception while formatting undefined value [sourcePattern=%.s, targetPattern=%.s, exceptionMessage=Conversion = ‘.’]

or this error

2022-11-04 15:37:10.035 [WARN ] [.transform.SingleValueTransformation] - Executing transformation ChannelStateTransformation{pattern=‘$.result.watt_hours_day.[0]’, serviceName=‘JSONPATH’} failed: Invalid path ‘$.result.watt_hours_day.[0]’

8

The problem is that all of your path statements try to access an element in an array. E.g. the [1] in:

JSONPATH:$.result.watt_hours_day.[1]

But there is no array in your JSON. This is an unfortunate side-effect of how forecast solar formats their return data. Each set of data is not an array of timestamps and values but an object where the timestamps are the keys. So you would have to use something like:

JSONPATH:$.result.watt_hours_day.2022-11-03

Of course, this is not going to work for a configuration in the JSONPATH because tomorrow the key will be different and you can’t (to my knowledge) put a dynamic value in the JSONPATH transformation.

There are a couple of possible solutions here:
The easiest solution is probably to just keep the entire return JSON in a single string item and then use a rule when that string changes to parse that item according to the current date and populate the other items you want.

You could also use a JS transform instead of a JSONPATH transform and write a JS script that will parse the item item and return the value you want. Of course, if you want more than one value, you’ll need a different JS transform script for each channel.

Instead of doing this yourself you could look at the Solar Forecast PV add-on that handles Forecast Solar as one of its options:

9

JustinG thanks for the detailed explanation. I will try both solutins in the next days.