JSONPath filter in a value_template

Jelte · September 9, 2019, 5:58pm

I’m running Grocy and I want to get the current meal of the day via the Grocy API. It puts out a JSON list with all the options. So I want to filter the result to get only the json of today.

I’ve used http://jsonpath.com/ to get working filter: $..[?(@.day=="2019-09-07")]

It gets the right JSON for me. But how to implement this JSONPath expression? Using a rest sensor with a value_template should do the trick. But I keep running into a configuration error for the filter character ‘$’ :

Invalid config for [sensor.rest]: invalid template (TemplateSyntaxError: unexpected char '$' at 15) for dictionary value @ data['value_template']. Got '{{ value_json.[$..[?(@.day=="2019-09-07")]] }}'. (See ?, line ?). Please check the docs at https://home-assistant.io/components/sensor.rest/

So how to implement the JSONpath expression in a value_template? It’s probably just a formatting way, but it’s one that’s hard to guess.

Below my code:

  - platform: rest
    name: "Grocy Meal plan" 
    resource: https://grocy.org/api/objects/meal_plan
    headers:
      GROCY-API-KEY: !secret GROCY-API-KEY
      accept: application/json
    json_attributes:
      - day
      - recipe_id
      - servings
    value_template: '{{ value_json.[$..[?(@.day=="2019-09-07")]] }}'

And below the JSON output of the API:

[
  {
    "id": "1",
    "day": "2019-07-09",
    "recipe_id": "2",
    "row_created_timestamp": "2019-07-09 11:27:10",
    "servings": "3"
  },
  {
    "id": "2",
    "day": "2019-07-10",
    "recipe_id": "7",
    "row_created_timestamp": "2019-07-09 11:27:21",
    "servings": "3"
  },
  {
    "id": "3",
    "day": "2019-07-11",
    "recipe_id": "9",
    "row_created_timestamp": "2019-07-09 11:27:29",
    "servings": "3"
  },
  {
    "id": "4",
    "day": "2019-07-08",
    "recipe_id": "11",
    "row_created_timestamp": "2019-07-09 11:27:53",
    "servings": "3"
  },
  {
    "id": "5",
    "day": "2019-07-07",
    "recipe_id": "10",
    "row_created_timestamp": "2019-07-09 11:28:12",
    "servings": "2"
  },
  {
    "id": "6",
    "day": "2019-07-06",
    "recipe_id": "8",
    "row_created_timestamp": "2019-07-09 11:28:24",
    "servings": "3"
  },
  {
    "id": "7",
    "day": "2019-08-03",
    "recipe_id": "4",
    "row_created_timestamp": "2019-08-03 08:43:55",
    "servings": "3"
  },
  {
    "id": "8",
    "day": "2019-08-04",
    "recipe_id": "11",
    "row_created_timestamp": "2019-08-03 08:44:28",
    "servings": "3"
  },
  {
    "id": "9",
    "day": "2019-08-05",
    "recipe_id": "30",
    "row_created_timestamp": "2019-08-03 08:44:44",
    "servings": "3"
  },
  {
    "id": "10",
    "day": "2019-08-06",
    "recipe_id": "2",
    "row_created_timestamp": "2019-08-03 08:45:11",
    "servings": "3"
  },
  {
    "id": "11",
    "day": "2019-08-07",
    "recipe_id": "12",
    "row_created_timestamp": "2019-08-03 08:45:25",
    "servings": "3"
  },
  {
    "id": "12",
    "day": "2019-08-08",
    "recipe_id": "3",
    "row_created_timestamp": "2019-08-03 08:45:40",
    "servings": "3"
  },
  {
    "id": "15",
    "day": "2019-08-14",
    "recipe_id": "8",
    "row_created_timestamp": "2019-08-10 12:51:36",
    "servings": "3"
  },
  {
    "id": "16",
    "day": "2019-08-12",
    "recipe_id": "2",
    "row_created_timestamp": "2019-08-10 12:51:53",
    "servings": "4"
  },
  {
    "id": "18",
    "day": "2019-08-15",
    "recipe_id": "7",
    "row_created_timestamp": "2019-08-10 12:52:40",
    "servings": "3"
  },
  {
    "id": "19",
    "day": "2019-08-22",
    "recipe_id": "10",
    "row_created_timestamp": "2019-08-17 09:33:04",
    "servings": "3"
  },
  {
    "id": "20",
    "day": "2019-08-21",
    "recipe_id": "12",
    "row_created_timestamp": "2019-08-17 09:33:13",
    "servings": "3"
  },
  {
    "id": "21",
    "day": "2019-08-20",
    "recipe_id": "2",
    "row_created_timestamp": "2019-08-17 09:33:36",
    "servings": "3"
  },
  {
    "id": "22",
    "day": "2019-08-19",
    "recipe_id": "4",
    "row_created_timestamp": "2019-08-17 09:33:52",
    "servings": "3"
  },
  {
    "id": "24",
    "day": "2019-08-18",
    "recipe_id": "3",
    "row_created_timestamp": "2019-08-17 09:43:36",
    "servings": "3"
  },
  {
    "id": "25",
    "day": "2019-08-28",
    "recipe_id": "11",
    "row_created_timestamp": "2019-08-23 19:09:49",
    "servings": "3"
  },
  {
    "id": "26",
    "day": "2019-08-26",
    "recipe_id": "2",
    "row_created_timestamp": "2019-08-23 19:10:03",
    "servings": "3"
  },
  {
    "id": "27",
    "day": "2019-08-29",
    "recipe_id": "10",
    "row_created_timestamp": "2019-08-23 19:10:29",
    "servings": "3"
  },
  {
    "id": "28",
    "day": "2019-08-27",
    "recipe_id": "7",
    "row_created_timestamp": "2019-08-23 19:12:10",
    "servings": "3"
  },
  {
    "id": "29",
    "day": "2019-08-25",
    "recipe_id": "25",
    "row_created_timestamp": "2019-08-23 21:56:42",
    "servings": "4"
  },
  {
    "id": "30",
    "day": "2019-09-02",
    "recipe_id": "2",
    "row_created_timestamp": "2019-08-30 19:39:14",
    "servings": "3"
  },
  {
    "id": "31",
    "day": "2019-09-04",
    "recipe_id": "8",
    "row_created_timestamp": "2019-08-30 19:41:13",
    "servings": "3"
  },
  {
    "id": "32",
    "day": "2019-09-05",
    "recipe_id": "10",
    "row_created_timestamp": "2019-08-30 19:41:24",
    "servings": "3"
  },
  {
    "id": "33",
    "day": "2019-09-03",
    "recipe_id": "36",
    "row_created_timestamp": "2019-08-30 19:48:15",
    "servings": "3"
  },
  {
    "id": "34",
    "day": "2019-09-09",
    "recipe_id": "2",
    "row_created_timestamp": "2019-09-07 08:59:52",
    "servings": "3"
  },
  {
    "id": "35",
    "day": "2019-09-11",
    "recipe_id": "12",
    "row_created_timestamp": "2019-09-07 09:00:02",
    "servings": "3"
  },
  {
    "id": "36",
    "day": "2019-09-10",
    "recipe_id": "30",
    "row_created_timestamp": "2019-09-07 09:00:16",
    "servings": "3"
  },
  {
    "id": "37",
    "day": "2019-09-07",
    "recipe_id": "4",
    "row_created_timestamp": "2019-09-07 09:00:41",
    "servings": "3"
  },
  {
    "id": "38",
    "day": "2019-09-13",
    "recipe_id": "40",
    "row_created_timestamp": "2019-09-07 09:04:02",
    "servings": "4"
  },
  {
    "id": "40",
    "day": "2019-09-08",
    "recipe_id": "42",
    "row_created_timestamp": "2019-09-07 09:10:57",
    "servings": "1"
  },
  {
    "id": "41",
    "day": "2019-09-12",
    "recipe_id": "41",
    "row_created_timestamp": "2019-09-07 09:11:37",
    "servings": "3"
  }
]

petro · September 9, 2019, 8:42pm

This will get you the the current day.

{% set today = now().strftime('%Y-%m-%d') %}
{{ value_json | selectattr('day','eq',today) | list | first }}

That will return a dictionary, it’s up to you to display what value you want out of the dictionary.

It will return this essentially for todays date:2019-09-09

{'id': '34', 'day': '2019-09-09', 'recipe_id': '2', 'row_created_timestamp': '2019-09-07 08:59:52', 'servings': '3'}

Jelte · September 10, 2019, 7:32am

Thanks Petro

Now I do have the json part I need, but the attributes trick of the RESTful sensor doesn’t work. I assume because it’s a dictionary now.

So how to extract the recipe_id for example in a sensor?

I tried this in a template:

{% set value_json= states.sensor.grocy_meal_plan.state %}
JSON: {{ value_json }}
Recipe id:  {{ value_json['recipe_id'] }}

{% set value_json =
{'id': '36', 'day': '2019-09-10', 'recipe_id': '30', 'row_created_timestamp': '2019-09-07 09:00:16', 'servings': '3'}
%}
JSON: {{ value_json }}
Recipe id: {{ value_json['recipe_id'] }}

And got this as a response:

JSON: {'id': '36', 'day': '2019-09-10', 'recipe_id': '30', 'row_created_timestamp': '2019-09-07 09:00:16', 'servings': '3'}
Recipe id:  


JSON: {'id': '36', 'day': '2019-09-10', 'recipe_id': '30', 'row_created_timestamp': '2019-09-07 09:00:16', 'servings': '3'}
Recipe id: 30

So it works if I put the json in directly, but not as the state of a sensor.

Can you point me in the right direction?

petro · September 10, 2019, 1:16pm

No, it’s a string.

If you want the recipe ID, that’s what you should display. Unfortunately, the restful sensor isn’t set up to peel more than 1 item from a json response. When you pull the whole object and place it into the state, it’s a string and you won’t be able to convert it to any object type because jinja doesn’t have the ability to do so.

So your only recourse is to peel 1 item at a time into different sensors.

  - platform: rest
    name: "Grocy Meal Plan Date" 
    resource: https://grocy.org/api/objects/meal_plan
    headers:
      GROCY-API-KEY: !secret GROCY-API-KEY
      accept: application/json
    value_template: >
      {% set today = now().strftime('%Y-%m-%d') %}
      {% set items = value_json | selectattr('day','eq',today) | list | first %}
      {{ items['day'] }}

  - platform: rest
    name: "Grocy Meal Plan Recipe Id" 
    resource: https://grocy.org/api/objects/meal_plan
    headers:
      GROCY-API-KEY: !secret GROCY-API-KEY
      accept: application/json
    value_template: >
      {% set today = now().strftime('%Y-%m-%d') %}
      {% set items = value_json | selectattr('day','eq',today) | list | first %}
      {{ items['recipe_id'] }}

  - platform: rest
    name: "Grocy Meal Plan Servings" 
    resource: https://grocy.org/api/objects/meal_plan
    headers:
      GROCY-API-KEY: !secret GROCY-API-KEY
      accept: application/json
    value_template: >
      {% set today = now().strftime('%Y-%m-%d') %}
      {% set items = value_json | selectattr('day','eq',today) | list | first %}
      {{ items['servings'] }}

Or get the values in a known order and separate them with commas.

  - platform: rest
    name: "Grocy Meal Plan" 
    resource: https://grocy.org/api/objects/meal_plan
    headers:
      GROCY-API-KEY: !secret GROCY-API-KEY
      accept: application/json
    value_template: >
      {% set today = now().strftime('%Y-%m-%d') %}
      {% set items = value_json | selectattr('day','eq',today) | list | first %}
      {% set attributes = ['day', 'recipe_id', 'servings'] %}
      {{ items.items() | selectattr(0, 'in', attributes) | sort(attribute=0) | map(attribute=1) | join(',') }}

Then each sensor would be:

- platform:
  sensors:
    grocy_meal_plan_date:
      friendly_name: Grocy Meal Plan Date
      value_template: "{{ states('sensor.grocy_meal_plan').split(',')[0] }}"
    grocy_meal_plan_recipe_id:
      friendly_name: Grocy Meal Plan Recipe Id
      value_template: "{{ states('sensor.grocy_meal_plan').split(',')[1] }}"
    grocy_meal_plan_servings:
      friendly_name: Grocy Meal Plan Servings
      value_template: "{{ states('sensor.grocy_meal_plan').split(',')[2] }}"

Jelte · September 10, 2019, 2:05pm

Wow, I have no idea what you are doing in that second template. HA isn’t sure either, because it sees everything after the combined template as a text. But I cannot see a missing ’ or something. Can you check if it is correct?

I would prefer the second option to limit the calls to the Grocy API. This normally only chances once a day.

But the first way is also a great way, it works! Thanks!

petro · September 10, 2019, 2:26pm

It’s working on my end.

I’ll explain what it’s doing.

This makes a string of the current date.

{% set today = now().strftime('%Y-%m-%d') %}

This makes a dictionary. It selects a dictionary from the json provided in the rest sensor. It’s selection matches the attribute ‘day’. Then converts the selectattr generate object into a list. Lastly, we get the first item in that list.

{% set items = value_json | selectattr('day','eq',today) | list | first %}

This creates a list of attributes that we care about

{% set attributes = ['day', 'recipe_id', 'servings'] %}

This last line returns a comma separated string day, recipe_id, servings.

{{ items.items() | selectattr(0, 'in', attributes) | sort(attribute=0) | map(attribute=1) | join(',') }}

First we convert the dictionary into a list of keys and values by using items.items(). It returns this:

[('id', '36'), ('day', '2019-09-10'), ('recipe_id', '30'), ('row_created_timestamp', '2019-09-07 09:00:16'), ('servings', '3')]

Next we filter out the items that we care about using selectattr(0, 'in', attributes), specifically looking at the 1st item in each tuple.

('id', '36')
  ^     ^
  |     |
  |   2nd item (index=1)
 1st item (index=0)

That returns this.

[('day', '2019-09-10'), ('recipe_id', '30'), ('servings', '3')]

Next we sort the list based on the first item, because we don’t know if it will be in order. This sorts it alphabetically, so we should end up with day, recipe_id, servings. sort(attribute=0) returns:

[('day', '2019-09-10'), ('recipe_id', '30'), ('servings', '3')]

Next, we only want the results so we map the 2nd item to a list of 2nd items with map(attribute=1), which returns:

['2019-09-10', '30', '3']

Lastly we join that information together, separating it with commas and that’s the result of the sensor join(',')

2019-09-10,30,3

Jelte · September 10, 2019, 3:02pm

Thanks for the very complete explanation.

One small error. You’ve missed the specification of the platform:

- platform: template
  sensors:
    grocy_meal_plan_date:

I see that it does work, but it breaks the yaml markup of visual studio code. I’ll make an issue there

Jelte · September 11, 2019, 8:00am

Hopefully last question. Based on the recipe ID I want to post an request to the grocy API.

For now I want to return the name of the recipe. The api request is:
curl -X GET https://grocy.org/api/objects/recipes/12 -H "GROCY-API-KEY: secret"
Where I want the recipe_id (12 today) to be based on a sensor.
Getting the id into the url seems easy with using {{ states('sensor.grocy_meal_plan_recipe_id' ) }}

But the : after GROCY-API-KEY is an issue. While the code below works in the template tester, it doesn’t work in Home Assistant because it doesn’t allow mapping values in the command line, refering to the :.
So how to get the complete API request with headers into the command line?

curl -X GET https://grocy..org/api/objects/recipes/{{ states('sensor.grocy_meal_plan_recipe_id' ) }} -H "GROCY-API-KEY: secret"

Putting ' ' around the complete curl request also gives errors.

Home Assistant is great and it gives a lot of possibilities, but getting the formatting just right is a pain in the ass sometimes.

petro · September 11, 2019, 12:07pm

command line sensors accept templates…

this should work

# Example configuration.yaml entry
sensor:
  - platform: command_line
    command: 'curl -X GET https://grocy..org/api/objects/recipes/{{ states("sensor.grocy_meal_plan_recipe_id") }} -H "GROCY-API-KEY: secret"'

Jelte · September 11, 2019, 5:47pm

Thanks for your continued effort . Greatly appreciated.

Problem here is that the " " around the GROCY-API-KEY: secret dissapear when the command is processed. I found this in the log:

2019-09-11 19:45:27 ERROR (SyncWorker_2) [homeassistant.components.command_line.sensor] Command failed: curl -X GET https://grocy.org/api/objects/recipes/12 -H GROCY-API-KEY: secret

And therefore the command fails.

petro · September 11, 2019, 6:23pm

escape the characters

'curl -X GET https://grocy..org/api/objects/recipes/{{ states("sensor.grocy_meal_plan_recipe_id") }} -H \"GROCY-API-KEY: secret\"'