Value template to extract JSON value

Configuration
1

Hello,

I am trying to create a sensor which shows the level of a river with the REST integration and I cannot for the life of me figure out how to do it. Can someone point me in the right direction on how to do this? the sensor is as follows, and I just need help figuring out the value template.

sensor:
  - platform: rest
    resource: https://waterservices.usgs.gov/nwis/iv/?format=json&sites=02037500&parameterCd=00065&siteStatus=all
    value_template: '{{ value_json.values}}'
    name: River Level

and the JSON the link returns is as follows

tievalues{
  "name": "ns1:timeSeriesResponseType",
  "declaredType": "org.cuahsi.waterml.TimeSeriesResponseType",
  "scope": "javax.xml.bind.JAXBElement$GlobalScope",
  "value": {
    "queryInfo": {
      "queryURL": "http://waterservices.usgs.gov/nwis/iv/format=json&sites=02037500&parameterCd=00065&siteStatus=all",
      "criteria": {
        "locationParam": "[ALL:02037500]",
        "variableParam": "[00065]",
        "parameter": []
      },
      "note": [
        {
          "value": "[ALL:02037500]",
          "title": "filter:sites"
        },
        {
          "value": "[mode=LATEST, modifiedSince=null]",
          "title": "filter:timeRange"
        },
        {
          "value": "methodIds=[ALL]",
          "title": "filter:methodId"
        },
        {
          "value": "2020-04-16T18:13:05.081Z",
          "title": "requestDT"
        },
        {
          "value": "ead24380-800d-11ea-a6ce-6cae8b6642f6",
          "title": "requestId"
        },
        {
          "value": "Provisional data are subject to revision. Go to http://waterdata.usgs.gov/nwis/help/?provisional for more information.",
          "title": "disclaimer"
        },
        {
          "value": "caas01",
          "title": "server"
        }
      ]
    },
    "timeSeries": [
      {
        "sourceInfo": {
          "siteName": "JAMES RIVER NEAR RICHMOND, VA",
          "siteCode": [
            {
              "value": "02037500",
              "network": "NWIS",
              "agencyCode": "USGS"
            }
          ],
          "timeZoneInfo": {
            "defaultTimeZone": {
              "zoneOffset": "-05:00",
              "zoneAbbreviation": "EST"
            },
            "daylightSavingsTimeZone": {
              "zoneOffset": "-04:00",
              "zoneAbbreviation": "EDT"
            },
            "siteUsesDaylightSavingsTime": true
          },
          "geoLocation": {
            "geogLocation": {
              "srs": "EPSG:4326",
              "latitude": 37.5632022,
              "longitude": -77.5469314
            },
            "localSiteXY": []
          },
          "note": [],
          "siteType": [],
          "siteProperty": [
            {
              "value": "ST",
              "name": "siteTypeCd"
            },
            {
              "value": "02080205",
              "name": "hucCd"
            },
            {
              "value": "51",
              "name": "stateCd"
            },
            {
              "value": "51087",
              "name": "countyCd"
            }
          ]
        },
        "variable": {
          "variableCode": [
            {
              "value": "00065",
              "network": "NWIS",
              "vocabulary": "NWIS:UnitValues",
              "variableID": 45807202,
              "default": true
            }
          ],
          "variableName": "Gage height, ft",
          "variableDescription": "Gage height, feet",
          "valueType": "Derived Value",
          "unit": {
            "unitCode": "ft"
          },
          "options": {
            "option": [
              {
                "name": "Statistic",
                "optionCode": "00000"
              }
            ]
          },
          "note": [],
          "noDataValue": -999999,
          "variableProperty": [],
          "oid": "45807202"
        },
        "values": [
          {
            "value": [
              {
                "value": "10.97",
                "qualifiers": [
                  "P"
                ],
                "dateTime": "2020-04-16T13:30:00.000-04:00"
              }
            ],
            "qualifier": [
              {
                "qualifierCode": "P",
                "qualifierDescription": "Provisional data subject to revision.",
                "qualifierID": 0,
                "network": "NWIS",
                "vocabulary": "uv_rmk_cd"
              }
            ],
            "qualityControlLevel": [],
            "method": [
              {
                "methodDescription": "",
                "methodID": 147078
              }
            ],
            "source": [],
            "offset": [],
            "sample": [],
            "censorCode": []
          }
        ],
        "name": "USGS:02037500:00065:00000"
      }
    ]
  },
  "nil": false,
  "globalScope": true,
  "typeSubstituted": false
}

I am just trying to extract the portion which says 10.97 but cannot for the life of me figure out the value template to do it.

Thanks!

2

That’s not valid JSON.

SyntaxError: JSON Parse error: Unexpected identifier “tievalues”

http://json.parser.online.fr/

3

Sorry that it added that after I ran it through something that made it more readable the actual output is as follows

{"name":"ns1:timeSeriesResponseType","declaredType":"org.cuahsi.waterml.TimeSeriesResponseType","scope":"javax.xml.bind.JAXBElement$GlobalScope","value":{"queryInfo":{"queryURL":"http://waterservices.usgs.gov/nwis/iv/format=json&sites=02037500&parameterCd=00065&siteStatus=all","criteria":{"locationParam":"[ALL:02037500]","variableParam":"[00065]","parameter":[]},"note":[{"value":"[ALL:02037500]","title":"filter:sites"},{"value":"[mode=LATEST, modifiedSince=null]","title":"filter:timeRange"},{"value":"methodIds=[ALL]","title":"filter:methodId"},{"value":"2020-04-16T18:13:05.081Z","title":"requestDT"},{"value":"ead24380-800d-11ea-a6ce-6cae8b6642f6","title":"requestId"},{"value":"Provisional data are subject to revision. Go to http://waterdata.usgs.gov/nwis/help/?provisional for more information.","title":"disclaimer"},{"value":"caas01","title":"server"}]},"timeSeries":[{"sourceInfo":{"siteName":"JAMES RIVER NEAR RICHMOND, VA","siteCode":[{"value":"02037500","network":"NWIS","agencyCode":"USGS"}],"timeZoneInfo":{"defaultTimeZone":{"zoneOffset":"-05:00","zoneAbbreviation":"EST"},"daylightSavingsTimeZone":{"zoneOffset":"-04:00","zoneAbbreviation":"EDT"},"siteUsesDaylightSavingsTime":true},"geoLocation":{"geogLocation":{"srs":"EPSG:4326","latitude":37.5632022,"longitude":-77.5469314},"localSiteXY":[]},"note":[],"siteType":[],"siteProperty":[{"value":"ST","name":"siteTypeCd"},{"value":"02080205","name":"hucCd"},{"value":"51","name":"stateCd"},{"value":"51087","name":"countyCd"}]},"variable":{"variableCode":[{"value":"00065","network":"NWIS","vocabulary":"NWIS:UnitValues","variableID":45807202,"default":true}],"variableName":"Gage height, ft","variableDescription":"Gage height, feet","valueType":"Derived Value","unit":{"unitCode":"ft"},"options":{"option":[{"name":"Statistic","optionCode":"00000"}]},"note":[],"noDataValue":-999999.0,"variableProperty":[],"oid":"45807202"},"values":[{"value":[{"value":"10.97","qualifiers":["P"],"dateTime":"2020-04-16T13:30:00.000-04:00"}],"qualifier":[{"qualifierCode":"P","qualifierDescription":"Provisional data subject to revision.","qualifierID":0,"network":"NWIS","vocabulary":"uv_rmk_cd"}],"qualityControlLevel":[],"method":[{"methodDescription":"","methodID":147078}],"source":[],"offset":[],"sample":[],"censorCode":[]}],"name":"USGS:02037500:00065:00000"}]},"nil":false,"globalScope":true,"typeSubstituted":false}

thanks for your help!

4

Screenshot from 2020-04-16 16-33-10
Screenshot from 2020-04-16 16-33-10876×236 20.3 KB

What a dog’s breakfast!

Paste this into the Template Editor to confirm it for yourself:

{% set value_json = {"name":"ns1:timeSeriesResponseType","declaredType":"org.cuahsi.waterml.TimeSeriesResponseType","scope":"javax.xml.bind.JAXBElement$GlobalScope","value":{"queryInfo":{"queryURL":"http://waterservices.usgs.gov/nwis/iv/format=json&sites=02037500&parameterCd=00065&siteStatus=all","criteria":{"locationParam":"[ALL:02037500]","variableParam":"[00065]","parameter":[]},"note":[{"value":"[ALL:02037500]","title":"filter:sites"},{"value":"[mode=LATEST, modifiedSince=null]","title":"filter:timeRange"},{"value":"methodIds=[ALL]","title":"filter:methodId"},{"value":"2020-04-16T18:13:05.081Z","title":"requestDT"},{"value":"ead24380-800d-11ea-a6ce-6cae8b6642f6","title":"requestId"},{"value":"Provisional data are subject to revision. Go to http://waterdata.usgs.gov/nwis/help/?provisional for more information.","title":"disclaimer"},{"value":"caas01","title":"server"}]},"timeSeries":[{"sourceInfo":{"siteName":"JAMES RIVER NEAR RICHMOND, VA","siteCode":[{"value":"02037500","network":"NWIS","agencyCode":"USGS"}],"timeZoneInfo":{"defaultTimeZone":{"zoneOffset":"-05:00","zoneAbbreviation":"EST"},"daylightSavingsTimeZone":{"zoneOffset":"-04:00","zoneAbbreviation":"EDT"},"siteUsesDaylightSavingsTime":true},"geoLocation":{"geogLocation":{"srs":"EPSG:4326","latitude":37.5632022,"longitude":-77.5469314},"localSiteXY":[]},"note":[],"siteType":[],"siteProperty":[{"value":"ST","name":"siteTypeCd"},{"value":"02080205","name":"hucCd"},{"value":"51","name":"stateCd"},{"value":"51087","name":"countyCd"}]},"variable":{"variableCode":[{"value":"00065","network":"NWIS","vocabulary":"NWIS:UnitValues","variableID":45807202,"default":true}],"variableName":"Gage height, ft","variableDescription":"Gage height, feet","valueType":"Derived Value","unit":{"unitCode":"ft"},"options":{"option":[{"name":"Statistic","optionCode":"00000"}]},"note":[],"noDataValue":-999999.0,"variableProperty":[],"oid":"45807202"},"values":[{"value":[{"value":"10.97","qualifiers":["P"],"dateTime":"2020-04-16T13:30:00.000-04:00"}],"qualifier":[{"qualifierCode":"P","qualifierDescription":"Provisional data subject to revision.","qualifierID":0,"network":"NWIS","vocabulary":"uv_rmk_cd"}],"qualityControlLevel":[],"method":[{"methodDescription":"","methodID":147078}],"source":[],"offset":[],"sample":[],"censorCode":[]}],"name":"USGS:02037500:00065:00000"}]},"nil":false,"globalScope":true,"typeSubstituted":false}%}
 
{{ value_json["value"]["timeSeries"][0]["values"][0]["value"][0]["value"] }}
2 Likes
5

I think this is right. It’s a really stupid schema and I’m not 100% sure I have it right.

value_template: ‘{{ value_json.value.timeseries.values.value.value }}’

Nope. I missed the indexes. See 123’s answer above.

6

Perfect! that worked! Thanks alot, both of you!

1 Like
7

I didn’t realise the template editor could be used for evaluating JSON like that. Good tip. Thanks 123.

2 Likes
8

FWIW, because the data uses complex combinations of dictionaries and lists, it’s the lists that can break the template. For example, let’s say the next time you acquire the data, the desired information is no longer the first (zeroth) item of each one of the three lists but the second or third. If there’s any change of position, the template will end up looking in the wrong place.

Just keep that in mind if, in the future, whatever uses this template returns some weird result (or, more likely, causes an error message).

1 Like
9

Will do! thanks again, I also had no idea the template editor could do that, and I was sitting here trying to figure it all out in my head haha.

10

That code must have been written by someone paid by the line. Or on an excessive amount of amphetamines.

11

Just doing it in the Template Editor was challenging enough (plus every dictionary key is called “value” or “values”).

Ideally, there would be a tool where you slap in some long-winded JSON data, highlight the value you want, and it spits out the template needed to extract it. It may exist but I just don’t know about it.

12

Like this!

http://jsonpathfinder.com/

IMG_0546
IMG_05462732×1795 1.28 MB

3 Likes
13

Oh yea that’s definitely going to be useful in the future!

14

Funnily enough, I had tried that one but used it incorrectly.

I pasted the data in the left-hand pane, selected the data I wanted, in the left-hand pane, and nothing showed up in the upper right-hand pane. OK, the mistake was selecting the data in the left-hand pane. You select it using the tree-view in the right-hand pane.

The result is this:

x.value.timeSeries[0].values[0].value[0].value

Looks like a good tool to share with other users BUT there’s a gotcha … which this particular example demonstrates very well. Look at what happens when you use that template in the Template Editor:

Screenshot from 2020-04-16 17-06-18
Screenshot from 2020-04-16 17-06-181479×259 25.4 KB

The tool displays the JSON path using dot notation and the word “value”, which has special meaning, becomes ambiguous to Home Assistant’s Jinja2 interpreter. It can’t decide which way you want it interpreted (as a built-in function or as the literal key name) so it throws an error (to be more precise it assume you want to use it as a function and then things go sideways from there).

That’s why I posted my version of the JSON path using bracket notation because it unambiguously indicates how I want “value” to be handled (as a key name).

As long as one knows how to steer clear of this pitfall, that tool is very useful because it does all the heavy lifting and, should the need arise, it’s easy to convert its result from dot to bracket notation.

Just for fun, here’s the maximum amount of dot notation you can sneak into the path and still have it work:

Screenshot from 2020-04-16 17-18-06
Screenshot from 2020-04-16 17-18-06850×250 21.3 KB

2 Likes