Help with JSON with multiple arrays

gr4z · January 28, 2020, 1:24pm

Hi
I am trying to set some sensors from the following JSON that details my bin collections. As these have duplicate fields, how would I select the field ‘nextDate’ for jobID 9573337 and the ‘nextDate’ for jobID 9584045? I am stumped…

{
  "recordCount": 2,
  "jobs_FeatureScheduleDates": [
    {
      "jobID": 9573337,
      "jobName": "Empty Bin 240L Black",
      "jobDescription": "Empty Bin 240L Black",
      "jobStatus": "Not Started",
      "previousDate": "2020-01-28T09:52:17.837",
      "nextDate": "2020-02-11T07:00:00",
      "rescheduledDate": "0001-01-01T00:00:00",
      "rescheduledDateSpecified": false,
      "rescheduledReason": "",
      "uprn": 100090181402,
      "usrn": 30101686,
      "address1": "",
      "address2": 12,
      "street": "xx",
      "locality": "xx",
      "town": "xx",
      "postCode": "xx",
      "easting": xx,
      "northing": xx,
      "longitude": xx,
      "latitude": xx
    },
    {
      "jobID": 9584045,
      "jobName": "Empty Bin 240L Green",
      "jobDescription": "Empty Bin 240L Green",
      "jobStatus": "Not Started",
      "previousDate": "2020-01-21T11:53:24.833",
      "nextDate": "2020-02-04T07:00:00",
      "rescheduledDate": "0001-01-01T00:00:00",
      "rescheduledDateSpecified": false,
      "rescheduledReason": "",
      "uprn": 100090181402,
      "usrn": 30101686,
      "address1": "",
      "address2": 12,
      "street": "xx",
      "locality": "xx",
      "town": "xx",
      "postCode": "xx",
      "easting": xx,
      "northing": xx,
      "longitude": -xx,
      "latitude": xx
    }
  ]
}

flamingm0e · January 28, 2020, 1:30pm

Does the data always come in that order? Or do you WANT to use the jobID?

If it always comes as an array like that you can just use {{ value_json.jobs_FeatureScheduleDates[0].nextDate }} to access the first item in the array, or [1] to access the second item in the array

gr4z · January 28, 2020, 2:05pm

Mmmm so I used

  - platform: template
    sensors:
      bintype:
        value_template: '{{ value_json.jobs_FeatureScheduleDates[0].nextDate }}'

But the sensor just comes back with ‘unavailable’.

I am generating the JSON with

  - platform: rest
    name: bindata
    json_attributes:
      - jobs_FeatureScheduleDates
    resource: https://www.peterborough.gov.uk/api/jobs/2020-1-01/2020-12-31/100090181402
    value_template: '{{ value_json.bindata }}'

petro · January 28, 2020, 2:09pm

Well, this is a tough one. Your next date appears to be the 2nd item, assuming your date is Year-Month-day.

Now, This template will have to change depending on the timezone. It’s not provided in the dates, so I have to assume it’s local. But it could be UTC. Either way, it could be off by a few hours depending on tz.

{% set format = '%Y-%m-%dT%H:%M:%S' %}
{% set namespace(timestamps=[]) %}
{% for job in value_json.bindata.jobs_FeatureScheduleDates %}
  {% set ts = strptime(job.nextDate, format).timestamp() %}
  {% set namespace.timestamps = namespace.timestamps + [ ts ] %}
{% endfor %}
{% if namespace.timestamps %}
  {% set next = namespace.timestamps | min | timestamp_custom(format) %}
  {% set jobid = value_json.bindata.jobs_FeatureScheduleDates | selectattr('nextDate','eq', next) | map(attribute='jobID') | list %}
  {{ jobid | first if jobid | length > 0 else 'unknown' }}
{% else %}
  unknown
{% endif %}

gr4z · January 28, 2020, 2:11pm

Thanks for this. All dates/times are UK GMT. There are two jobs that I need the ‘nextDate’ for. The Black waste bin and the Green waste bin. Each is detailed in a jobID array.

petro · January 28, 2020, 2:14pm

well either way, this template will only tell you the next jobid. It won’t show you all 4 or whatever.

I don’t know why you guys have such a complicated garbage system anyways. In the states they pick up everything on the same day, once a week. No use for any of these crazy templates.

gr4z · January 28, 2020, 2:18pm

We are stuck with what we have they alternate between landfill waste and recycling waste every week. I can never remember what the next collection is, hence HA to the rescue. If I can work out the value_template syntax!

petro · January 28, 2020, 2:18pm

Well, the template I provided will tell which is next, and only which is next.

gr4z · January 28, 2020, 2:28pm

Need both unfortunately. The first is landfill waste and the second is recycling waste.

petro · January 28, 2020, 2:30pm

So if you need to know which one to put out, do you need to know the date of the second one? Just saying. This will simply tell you that this week you need to put out job # x. Then, when that job passes, it will tell you the next weeks.

gr4z · January 28, 2020, 2:45pm

Surely with the JSON data it must be possible to set a sensor with the corresponding fields? Seems crazy amount of code just to get a date when it is already stored in the nextDate field.

petro · January 28, 2020, 2:49pm

You can’t compare dates as strings. 9 is greater than 10 as strings. So you have to convert the dates to a number. Then for some reason, that js list is not ordered based on next date, and the order can change. So you need to dynamically look through the list. Thats what @flamingm0e was eluding to with this comment:

Because if the order changes and next week you get job1 and job2 swapped then your template will show the wrong information.

You’re welcome to not use the template. But you need to understand the ramifications.

gr4z · January 28, 2020, 2:52pm

Yes it always comes in that order.

“jobID”: 9573337. Is always the Black bin collection.
“jobID”: 9584045. Is always the Green bin collection.

Both jobs are always in the same order. Sorry if I am being dumb, but this looks straight forward but seemingly it isn’t!

petro · January 28, 2020, 2:59pm

If they are in the same order then @flamingm0e’s solution will work, and it is straight forward.

  - platform: rest
    name: Black Bin
    resource: https://www.peterborough.gov.uk/api/jobs/2020-1-01/2020-12-31/100090181402
    value_template: '{{ value_json.bindata.jobs_FeatureScheduleDates[0].nextDate }}'

  - platform: rest
    name: Green Bin
    resource: https://www.peterborough.gov.uk/api/jobs/2020-1-01/2020-12-31/100090181402
    value_template: '{{ value_json.bindata.jobs_FeatureScheduleDates[1].nextDate }}'

Or if you want a single rest sensor and 2 template sensors:

  - platform: rest
    name: bindata
    json_attributes:
      - bindata
    resource: https://www.peterborough.gov.uk/api/jobs/2020-1-01/2020-12-31/100090181402
    value_template: 'OK'

  - platform: template
    sensors:
      black_bin:
        friendly_name: Black Bin
        value_template: "{{ state_attr('sensor.bindata','bindata').jobs_FeatureScheduleDates[0].nextDate }}"
      green_bin:
        friendly_name: Green Bin
        value_template: "{{ state_attr('sensor.bindata','bindata').jobs_FeatureScheduleDates[1].nextDate }}"

gr4z · January 28, 2020, 3:12pm

Thank you for your continued help. Unfortunately the first example comes back with ‘unknown’ while the second is ‘unavailable’

I am banging my head against the wall

petro · January 28, 2020, 3:15pm

what does

  - platform: rest
    name: bindata
    resource: https://www.peterborough.gov.uk/api/jobs/2020-1-01/2020-12-31/100090181402
    value_template: {{ value }}

give?

EDIT Nevermind, i’m just going to the site.

and, that’s your problem. bindata is no where to be found…

petro · January 28, 2020, 3:18pm

So… because bindata doesn’t actually exist…

@flamingm0e’s solution in his first reply that you never tried…

  - platform: rest
    name: Black Bin
    resource: https://www.peterborough.gov.uk/api/jobs/2020-1-01/2020-12-31/100090181402
    value_template: '{{ value_json.jobs_FeatureScheduleDates[0].nextDate }}'

  - platform: rest
    name: Green Bin
    resource: https://www.peterborough.gov.uk/api/jobs/2020-1-01/2020-12-31/100090181402
    value_template: '{{ value_json.jobs_FeatureScheduleDates[1].nextDate }}'

Or if you want a single rest sensor and 2 template sensors:

  - platform: rest
    name: bindata
    json_attributes:
      - jobs_FeatureScheduleDates
    resource: https://www.peterborough.gov.uk/api/jobs/2020-1-01/2020-12-31/100090181402
    value_template: 'OK'

  - platform: template
    sensors:
      black_bin:
        friendly_name: Black Bin
        value_template: "{{ state_attr('sensor.bindata','jobs_FeatureScheduleDates')[0].nextDate }}"
      green_bin:
        friendly_name: Green Bin
        value_template: "{{ state_attr('sensor.bindata','jobs_FeatureScheduleDates')[1].nextDate }}"

gr4z · January 28, 2020, 3:20pm

Ah ha. Awesome that has worked, thank you so much for the help!