Restful Sensor - Trying to Extract Multiple Nested JSON Values

jivesinger · January 29, 2018, 9:03am

Hi Guys,

I’m trying to configure a Restful sensor and extract multiple nested JSON values and am struggling with the value_template ( I think )
I can successfully configure and extract one value at a time, but one hit to the same URL for every sensor might make my electricity provider cranky

My sensor config throws no errors, it just doesn’t populate.
I’m trying to return values nested under the “results” tag from the sample JSON output below.

- platform: rest
  name: JSON elec
  resource: https://xxxxxxx/uat/private/current/portal/dashboard/month/current
  username: xxxxxxx
  password: xxxxxxxx
  authentication: basic
  json_attributes:
    - DayNumber
    - TotalBillingDays
  value_template: '{{ value_json.results[0] }}'
  headers:
    Content-Type: application/json
    User-Agent: Home Assistant REST sensor

Sample JSON output to be parsed

{
  "service": "Dashboard Data",
  "status": "ok",
  "results": {
    "LatestBillingStartDate": "2017-05-06",
    "LatestBillingEndDate": "2017-06-05",
    "LatestReadDate": "2017-06-03",
    "DayNumber": 29,
    "TotalBillingDays": 31,
    "MonthlyTotalUsage": 321.45,
    "MonthlyTotalSpend": 123.54,
    "MonthlyTotalGeneration": 0,
    "MonthlyTotalSolarCredit": 0,
    "ForecastSpend": 154.32,
    "LatestBills": [
      {
        "InvoiceNumber": 99998,
        "AmountDue": 123.45,
        "DueDate": "2017-06-13 00:00:00",
        "AmountPaid": 0,
        "AccountNumber": "610130000000",
        "InvoiceDate": "2017-05-23 16:15:55",
        "TotalAmountDue": 123.45,
        "TotalUsage": 543.21,
        "BillEndDate": "2017-05-05 00:00:00"
      },
      {
        "InvoiceNumber": 99997,
        "AmountDue": 123.45,
        "DueDate": "2017-05-18 00:00:00",
        "AmountPaid": 123.45,
        "AccountNumber": "610130000000",
        "InvoiceDate": "2017-04-28 16:36:32",
        "TotalAmountDue": 123.45,
        "TotalUsage": 432.10,
        "BillEndDate": "2017-04-05 00:00:00"
      }
    ]
  },
  "agent": {
    "device": "Other 0.0.0",
    "platform": "Windows 10 0.0.0",
    "browser": "Chrome 58.0.3029",
    "remote_addr": "14.200.52.132"
  }
}

TIA for any help

gpbenton · January 29, 2018, 9:26am

I think you need to store the whole response in your Restful sensor and then use template sensors to extract individual readings.

jivesinger · January 29, 2018, 9:29am

Thanks for the reply.

I’ll be able to work out the template sensors without too much drama, but could you give me an example of how I’d store the whole response ?

gpbenton · January 29, 2018, 9:30am

Just remove the value_template. The whole string is then stored as the state.

jivesinger · January 29, 2018, 9:49am

OK so its now trying to work but the string is too long and HA is returning

homeassistant.exceptions.InvalidStateError: Invalid state encountered for entity id: sensor.json_elec. State max length is 255 characters.

Looks like I still need to template some stuff out

andrewdolphin · January 29, 2018, 10:50am

Json attributes needs to start from the top of the json tree.

So something like - results[0].TotalBillingDays

I think…!

jivesinger · January 29, 2018, 11:24am

Thanks for the reply.
Currently nothing that is nested is being rendered into attributes.
I’m poking it with any permutation that I can think of and so far nothing other than the first few un-nested tags are working.

Current sensor config

- platform: rest
  name: JSON elec
  resource: https://xxxxxx/uat/private/current/portal/dashboard/month/current
  username: xxxxxx
  password: xxxxxx
  authentication: basic
  json_attributes:
    - service
    - status
    - results[0].DayNumber
    - results.["TotalBillingDays"]
    - results.LatestReadDate
    - results["TotalBillingDays"]
    - results[0]["TotalBillingDays"]
    - results.[TotalBillingDays]
    - results[TotalBillingDays]
  value_template: '{{ value_json.value}}'
  headers:
    Content-Type: application/json
    User-Agent: Home Assistant REST sensor

From the states tab:

entity:                   state:       attributes:
sensor.json_elec		               service: Dashboard Data
                                       status: ok
                                       friendly_name: JSON elec

jivesinger · January 29, 2018, 11:30am

Why I don’t try the simplest thing first I’ll never know

json_attributes:
    - results

successfully imports all nested tags under “results” as attributes.

Thanks for your input and steering me in the right direction guys

olekenneth · May 15, 2018, 1:57pm

I made a PR to extract multiple values from the JSON into attributes:
https://github.com/home-assistant/home-assistant/pull/13677

Feel free to comment or review

sean.mcgrath · October 14, 2018, 8:33am

A bit of an old topic, but am hoping jivesinger can post his code.

Hi jivesinger,
I have very similar looking json output and am also getting the State max length is 255 characters. error. I tried loading a top branch like you mentioned below, but same error. Could you have a look and see if you can tell what I’m doing wrong?

cheers,
Sean

my config:

sensor:
  - platform: rest
    name: testBR
    resource: https://api.buienradar.nl/data/public/2.0/jsonfeed/
    json_attributes:
      - buienradar

Part of the json output:

{

  "buienradar": {

    "copyright": "(C)opyright Buienradar / RTL. Alle rechten voorbehouden",

    "terms": "Deze feed mag vrij worden gebruikt onder voorwaarde van bronvermelding buienradar.nl inclusief een hyperlink naar https://www.buienradar.nl. Aan de feed kunnen door gebruikers of andere personen geen rechten worden ontleend."

  },

  "actual": {

    "stationmeasurements": [

      {

        "stationid": 6391,

        "stationname": "Meetstation Arcen",

        "lat": 51.5,

        "lon": 6.2,

        "regio": "Venlo",

        "timestamp": "2018-10-13T20:10:00",

        "graphurl": "https://www.buienradar.nl/nederland/weerbericht/weergrafieken/6391",

        "iconurl": "https://www.buienradar.nl/resources/images/icons/weather/30x30/cc.png",

        "weatherdescription": "Zwaar bewolkt",

        "winddirection": "ZO",

        "airpressure": null,

        "temperature": 21.9,

        "groundtemperature": 20.2,

        "feeltemperature": 21.9,

        "visibility": null,

        "windgusts": 4.43,

        "windspeed": 2.57,

        "windspeedBft": 2,

        "humidity": 54.0,

        "precipitation": 0.0,

        "sunpower": 0.0,

        "rainFallLast24Hour": 0.003,

        "rainFallLastHour": 0.0,

        "winddirectiondegrees": 141,

        "dayhistory": {

          "timestamp": "2018-10-13T00:00:00",

          "temperatureMin": 14.9,

          "temperatureMax": 27.3,

          "groundtemperatureMin": 13.2,

          "sunshineHours": 8.33,

          "windgustsMax": 9.83,

          "windspeedMax": 5.71,

          "windspeedBftMax": 4,

          "windDirectionDegreesMax": 159

        }

      },

      {

        "stationid": 6275,

        "stationname": "Meetstation Arnhem",

        "lat": 52.07,

        "lon": 5.88,

        "regio": "Arnhem",

        "timestamp": "2018-10-13T

finity · October 14, 2018, 3:21pm

What part are you trying to extract out of that feed?

And you didn’t post the complete json string of the output of that resource. I had to close a couple of } and one ] to get it to not error out in the template editor.

Bottom line is that you will have to use a “value_template:” and the format the template to pull only the info you want for the state to prevent it from trying to pull the whole json string and sticking that as the state.

sean.mcgrath · October 14, 2018, 6:29pm

Ah, sorry, I should have closed the brackets – it’s a long output and was too many characters for a post.

To test it, I would be happy to extract the “buienradar” part just to see that it works. What I really want to extract is what I get from the armbian command line using curl and jq like this:

$ curl -s https://api.buienradar.nl/data/public/2.0/jsonfeed | jq '.actual.stationmeasurements[] | select(.stationid == 6375)'

finity · October 14, 2018, 7:59pm

{{ sensor.testBR.buienradar }}

Should give you the first section:

{'copyright': '(C)opyright Buienradar / RTL. Alle rechten voorbehouden', 'terms': 'Deze feed mag vrij worden gebruikt onder voorwaarde van bronvermelding buienradar.nl inclusief een hyperlink naar https://www.buienradar.nl. Aan de feed kunnen door gebruikers of andere personen geen rechten worden ontleend.'}

Should give you:

6275

That will only work if the ‘stationid’ you are trying to parse out is always in the same order in the array.

I didn’t see any ‘6375’ in the json you posted above.

If you want everything in the ‘stationmeasurements’ section for the second item in the array then just leave out the [‘stationid’] part.

You’ll have to play with it in the template editor to see what gives you the result you want.

sean.mcgrath · October 14, 2018, 8:07pm

Thanks, that’s great – I’ll give it a try. I see I was thrown by the doc example because the name of the variable was the same as the attribute it wanted to add and I mixed them up.

Yes, the stations are in alphabetical order, and 6275 is Volkel. It’s somewhere at character 60,000.