RESTful sensor, parsing json

Configuration
1

Hi,
I have following output from the service i call :

JSON content

{
“d”: “{“list”:[{“id”:27445,“materielnavn”:“140 l beholder - ugetømning (1 stk.)”,“ordningnavn”:“Dagrenovation”,“toemningsdage”:“Dagrenovation mandag hver uge”,“toemningsdato”:“Mandag den 06-05-2019”,“pris”:“kr. 1.181,50”,“mattypeid”:376,“antal”:1,“vejnavn”:“Ageren”,“modulsort”:0,“adrid”:30000,“fractionid”:5,“viskalender”:true,“modulId”:1},{“id”:89349,“materielnavn”:“Miljøboks (1 stk.)”,“ordningnavn”:“Dagrenovation”,“toemningsdage”:“Dagrenovation mandag hver uge”,“toemningsdato”:“Mandag den 06-05-2019”,“pris”:“Ingen”,“mattypeid”:618,“antal”:1,“vejnavn”:“Ageren”,“modulsort”:0,“adrid”:30000,“fractionid”:5,“viskalender”:true,“modulId”:1},{“id”:86886,“materielnavn”:“Madam Skrald genbrug 370 liter (1 stk.)”,“ordningnavn”:“Madam Skrald”,“toemningsdage”:“MS - Mandag område A”,“toemningsdato”:“Mandag den 13-05-2019”,“pris”:“Ingen”,“mattypeid”:430,“antal”:1,“vejnavn”:“Ageren”,“modulsort”:1,“adrid”:30000,“fractionid”:15,“viskalender”:true,“modulId”:12},{“id”:5523,“materielnavn”:“Storskrald (1 stk.)”,“ordningnavn”:“Pap og Storskrald”,“toemningsdage”:“Storskrald fredag i lige uger”,“toemningsdato”:“Fredag den 03-05-2019”,“pris”:“Ingen”,“mattypeid”:391,“antal”:1,“vejnavn”:“Ageren”,“modulsort”:2,“adrid”:30000,“fractionid”:6,“viskalender”:true,“modulId”:2},{“id”:93646,“materielnavn”:“Papbeholder villa (1 stk.)”,“ordningnavn”:“Pap og Storskrald”,“toemningsdage”:“Storskrald fredag i lige uger”,“toemningsdato”:“Fredag den 03-05-2019”,“pris”:“Ingen”,“mattypeid”:624,“antal”:1,“vejnavn”:“Ageren”,“modulsort”:2,“adrid”:30000,“fractionid”:6,“viskalender”:true,“modulId”:2},{“id”:9837,“materielnavn”:“Haveaffald (1 stk.)”,“ordningnavn”:“Haveaffald”,“toemningsdage”:“Haveaffald fredag hver uge 1/4-30/11”,“toemningsdato”:“Fredag den 03-05-2019”,“pris”:“Ingen”,“mattypeid”:390,“antal”:1,“vejnavn”:“Ageren”,“modulsort”:3,“adrid”:30000,“fractionid”:7,“viskalender”:true,“modulId”:3}]}”
}

I need to get to ordningnavn and toemningsdato but it seems after my knowledge this is not propperly formatted json since there is valid json inside a value which would not be a problem if it weren’t for the curly braces which destroys the format. Is there a way i can fix that so i can get to the values inside the array ?

2

There are 6 instances of ordningnavn and toemningsdato in the malformed JSON string. Do you want to retrieve all 6 of them or just the first one?

For example, the first one, "id":27445, contains "ordningnavn":"Dagrenovation". The third one, "id":86886, contains "ordningnavn":"Madam Skrald".

3

@123 thank you, i want all of them so i can show them in a sensor, maybe it’s better to make a sensor for each so i can send alerts accordingly (is the trash bin).

4

Is it always 6 instances or can it be any amount?

The reason why I’m asking is because I know how to extract the values using regex_findall_index. However, its flexibility is limited. For example, it can’t tell you how many instances it found. So if you ask for the fourth instance of ordningnavn but there are only three, the result will be an error.

I considered using replace to fix the damaged parts of the JSON list. It works but the result is a string in JSON format and not a JSON list.

5

At the moment yes and will be for a long time, at some point there might be another one but then again it’s fixed so i can correct the code at that time.

I was thinking the same thing, i could not get it to work, if using replace then it should end up in a valid JSON list as far as i can see, why is it it doesn’t end up in a list for you ?

6

There are two double-quotes that are responsible for the malformed JSON:

This double-quote near the start:

"d": "{"list":[
     ^

and this double-quote near the end:

,"modulId":3}]}"
               ^

If we assume the variable z contains the malformed JSON, this template, using two consecutive replace functions, will remove the two double-quotes:

{% set x = (z.replace('"d": "{', '"d": {')).replace('}]}"', '}]}') %}

Set your REST Sensor’s value_template to this:

    value_template: >-
      {% set x = (value.replace('"d": "{', '"d": {')).replace('}]}"', '}]}') %}
      {{ x['d']['list'][0]['ordningnavn'] }}

The variable x contains the result of the corrected JSON. If x is understood to be a JSON list, and not a string, then the next statement will return the value of the first (zeroth) instance of ordningnavn (which will be Dagrenovation).

If you want the value of the third instance of ordningnavn (which will be Madam Skrald), change the 0 to 2 like this:

      {{ x['d']['list'][2]['ordningnavn'] }}

If it fails to report a value, that means x is not understood to be a JSON list but just a string in JSON format.

Screenshot%20from%202019-05-03%2012-19-50
Screenshot%20from%202019-05-03%2012-19-50875×842 81.8 KB

7

So i tried this :

Error

{% set z = {
“d”: “{“list”:[{“id”:27445,“materielnavn”:“140 l beholder - ugetømning (1 stk.)”,“ordningnavn”:“Dagrenovation”,“toemningsdage”:“Dagrenovation mandag hver uge”,“toemningsdato”:“Mandag den 06-05-2019”,“pris”:“kr. 1.181,50”,“mattypeid”:376,“antal”:1,“vejnavn”:“Agerledet”,“modulsort”:0,“adrid”:24750,“fractionid”:5,“viskalender”:true,“modulId”:1},{“id”:89349,“materielnavn”:“Miljøboks (1 stk.)”,“ordningnavn”:“Dagrenovation”,“toemningsdage”:“Dagrenovation mandag hver uge”,“toemningsdato”:“Mandag den 06-05-2019”,“pris”:“Ingen”,“mattypeid”:618,“antal”:1,“vejnavn”:“Agerledet”,“modulsort”:0,“adrid”:24750,“fractionid”:5,“viskalender”:true,“modulId”:1},{“id”:86886,“materielnavn”:“Madam Skrald genbrug 370 liter (1 stk.)”,“ordningnavn”:“Madam Skrald”,“toemningsdage”:“MS - Mandag område A”,“toemningsdato”:“Mandag den 13-05-2019”,“pris”:“Ingen”,“mattypeid”:430,“antal”:1,“vejnavn”:“Agerledet”,“modulsort”:1,“adrid”:24750,“fractionid”:15,“viskalender”:true,“modulId”:12},{“id”:5523,“materielnavn”:“Storskrald (1 stk.)”,“ordningnavn”:“Pap og Storskrald”,“toemningsdage”:“Storskrald fredag i lige uger”,“toemningsdato”:“Fredag den 03-05-2019”,“pris”:“Ingen”,“mattypeid”:391,“antal”:1,“vejnavn”:“Agerledet”,“modulsort”:2,“adrid”:24750,“fractionid”:6,“viskalender”:true,“modulId”:2},{“id”:93646,“materielnavn”:“Papbeholder villa (1 stk.)”,“ordningnavn”:“Pap og Storskrald”,“toemningsdage”:“Storskrald fredag i lige uger”,“toemningsdato”:“Fredag den 03-05-2019”,“pris”:“Ingen”,“mattypeid”:624,“antal”:1,“vejnavn”:“Agerledet”,“modulsort”:2,“adrid”:24750,“fractionid”:6,“viskalender”:true,“modulId”:2},{“id”:9837,“materielnavn”:“Haveaffald (1 stk.)”,“ordningnavn”:“Haveaffald”,“toemningsdage”:“Haveaffald fredag hver uge 1/4-30/11”,“toemningsdato”:“Fredag den 03-05-2019”,“pris”:“Ingen”,“mattypeid”:390,“antal”:1,“vejnavn”:“Agerledet”,“modulsort”:3,“adrid”:24750,“fractionid”:7,“viskalender”:true,“modulId”:3}]}”
}%}

{% set x = (z.replace(’“d”: “{’, ‘“d”: {’)).replace(’}]}”’, ‘}]}’) %}

and i keep getting : Error rendering template: UndefinedError: 'dict object' has no attribute 'replace'

8

I just noticed why you get difference result, so the real output that comes from the service has backslash so here is how i run it in the template editor. The forum replaces those so you didn’t get them. I don’t know if that’s the reason i can do the replace ?

9

Rather than try to make this work in the Template Editor, I suggest you create a REST Sensor and use test the template I suggested.

I would test it for you but I don’t the URL that retrieves the JSON data.

10

Thanks, i will do that, i just thought it would be easier with the template editor to test. I will test.
In case you want to see the URL is : RenoWeb Office and the JSON post data is :

{
    "adrid": 24750,
    "common": false
}
11

I just tried with a sensor, unfortunnately i got unknown in the state :

- platform: rest
  name: "Dagrenovation"
  resource: https://herlev.renoweb.dk/Legacy/JService.asmx/GetAffaldsplanMateriel_mitAffald
  method: POST
  payload: '{"adrid": 24750,"common": false}'
  value_template: >-
      {% set x = (value.replace('"d": "{', '"d": {')).replace('}]}"', '}]}') %}
      {{ x['d']['list'][0]['ordningnavn'] }}
12

Also i was looking at the last example here : https://www.home-assistant.io/components/rest/
Which seems pretty smart so i don’t have to make 6 post calls but one then make sensors pointing to the main one. If i get the first one to work, maybe i can try something like this later. Seems smart.
When i finaly get seomthing and i get to the date i would have to find a way to parse Mandag den 06-05-2019 to a date HASS can understand so i can act on it.

13

I had a feeling it might not work because the value in x would no longer be understood to be a JSON list but an ordinary string.

Thanks for the URL. I’ll experiment with it later and see if I can get some useful results.

14

It’s me who is thanking :wink:

15

I created this REST Sensor:

  - platform: rest
    name: "Dagrenovation"
    resource: https://herlev.renoweb.dk/Legacy/JService.asmx/GetAffaldsplanMateriel_mitAffald
    method: POST
    payload: '{"adrid": 24750,"common": false}'
    value_template: >-
        {{value | truncate(250)}}

Here’s the value it reports:

<!DOCTYPE html>



<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <meta charset="UTF-8">

    <title>RenoWeb offline</title>



    <!-- Bootstrap -->

    <link rel="stylesheet"...

That’s definitely not in JSON format. Can you confirm the URL is correct? Or is the service unavailable?

16

ohh i am sorry, i forgot to add above in the example, if it doesn’t have the Content-Type it will throw the error you see. thank you for trying to help me :wink:

  headers:
    Content-Type: application/json
17

Well now, that was an educational experience ending in failure. :thinking:

I managed to produce a template that corrects the invalid JSON lists received from the website. You can’t store more than 255 characters in the sensor’s state, so I just took the first 125 characters and the last 125 characters. The point was just to confirm that the beginning and the end of the JSON list was now properly formatted.

  - platform: rest
    name: "Dagrenovation"
    resource: https://herlev.renoweb.dk/Legacy/JService.asmx/GetAffaldsplanMateriel_mitAffald
    method: POST
    payload: '{"adrid": 24750,"common": false}'
    headers:
      Content-Type: application/json
    value_template: >-
      {% set x = ((value.replace('"d":"{', '"d":{')).replace('}]}"', '}]}')).replace('\\','') %}
      {{x[:125]}} .. {{x[-125:]}}

The result is this:

{"d":{"list":[{"id":27445,"materielnavn":"140 l beholder - ugetømning (1 stk.)","ordningnavn":"Dagrenovation","toemningsdage" .. "mattypeid":390,"antal":1,"vejnavn":"Agerledet","modulsort":3,"adrid":24750,"fractionid":7,"viskalender":true,"modulId":3}]}}

Both ends have been purged of the extra double-quotes and there are no \ characters. Looks good!

Now for the real test. I modified value_template to display the value in ordningnavn.

    value_template: >-
      {% set x = ((value.replace('"d":"{', '"d":{')).replace('}]}"', '}]}')).replace('\\','') %}
      {{ x['d']['list'][0]['ordningnavn'] }}

The result is unknown.

The reason is because the template produced a nice clean string in JSON format. What it didn’t produce is a JSON list. Therefore this approach, attempting to fix the broken JSON list, doesn’t work.

The second approach is to simply use regex_findall_index to search through the string and find all instances of ordningnavn and report their values.

18

Yeah that’s back to square one :frowning:

I am also looking whether python scripts can be used for this but it doesn’t seem so :frowning: I need to go to AppDaemon but that seems overkill.

19

You could use a Command Line Sensor instead and have it use an external script to process the data and return what you want as a proper JSON list.

The external script can use whatever is best to get the job done (shell script, python, etc). A combination of curl and grep might work but you’d probably have more flexibility with python.

20

Yes i was looking at that, i just can’t seem to find an example how to set a value of the sensor with output from the python script file. I will search further, thank you for trying to help.