How to extract from nested JSON?

klogg · February 17, 2019, 11:38am

Can someone help me extract from this JSON? I can get elements that are nested two ‘deep’ but cannot for the life of me find a way to go any deeper.

In the example JSON posted here I can get 'statuscode', copyright.text and maxResults with
{{ json_data['info'].statuscode }}
{{ json_data['info']['copyright'].text }}
{{ json_data['options']['maxResults'] }}

But try as I might I cannot get anything from results

Thanks in advance for any pointers

Original JSON is as follows

{"info":{"statuscode":0,"copyright":{"text":"\u00A9 2019 MapQuest, Inc.","imageUrl":"http://api.mqcdn.com/res/mqlogo.gif","imageAltText":"\u00A9 2019 MapQuest, Inc."},"messages":[]},"options":{"maxResults":1,"thumbMaps":true,"ignoreLatLngInput":false},"results":[{"providedLocation":{"latLng":{"lat":99.519138,"lng":-99.321133}},"locations":[{"street":"MyStreet","adminArea6":"","adminArea6Type":"Neighborhood","adminArea5":"MyCity","adminArea5Type":"City","adminArea4":"","adminArea4Type":"County","adminArea3":"England","adminArea3Type":"State","adminArea1":"GB","adminArea1Type":"Country","postalCode":"A12","geocodeQualityCode":"B1AAA","geocodeQuality":"STREET","dragPoint":false,"sideOfStreet":"N","linkId":"0","unknownInput":"","type":"s","latLng":{"lat":99.519138,"lng":-99.321135},"displayLatLng":{"lat":99.519138,"lng":-99.321135}}]}]}

Which formats as

123 · February 17, 2019, 1:31pm

results contains lat in three places. Here is how to get all three of them and street. You can modify these examples to get any value you want in results.

{{value_json.results[0].providedLocation.latLng.lat}}
{{value_json.results[0].locations[0].street}}
{{value_json.results[0].locations[0].latLng.lat}}
{{value_json.results[0].locations[0].displayLatLng.lat}}

klogg · February 17, 2019, 1:45pm

Thank you!!!
If you only knew how long I’d been trying this. I would never have got to using [0]

Another day. Another thing learnt form this forum!

petro · February 17, 2019, 1:47pm

to expand on what @123 is saying

Example:

my dictionary is this:

{'item':'value', 'item2':'value2'}
^                                ^
|                                |
indicates dictionary

accessing dictionary items is simple as

dictionary['item'] #  returns 'value'
dictionary['item2'] # returns 'value2'

dictionaries are indicated by items surrounded by {}.

accessing items a list

this is my list

['a','b','c','d']
^               ^
|               |
indicates list

access items is simple as

list[0] # returns 'a'
list[1] # returns 'b'

So you have a complex structure. You have dictionaries in lists. So you need to go through your results object and figure out how to use the index or item properly

klogg · February 17, 2019, 1:51pm

Thanks @petro another excellent explanation.

123 · February 17, 2019, 2:07pm

A challenge for you:

Forget what I posted.
Now use petro’s explanation exclusively to extract a value from results.
Let us know how it goes.

Hint:

Start with {{ value_json['results'] }}

… let me know if you get stuck. I have the answer … and it looks different from the solution I originally posted.

klogg · February 17, 2019, 4:29pm

Not following your start point but these work

{{ value_json.results[0].locations[0].latLng.lat }}
{{ value_json.results[0].providedLocation.latLng.lat }}

And as you’re here, the whole reason I’m doing this is to try and use the MapQuest reverse geocoding api.

Ultimately I think I’m going to need to use the command_line sensor as I want to template the url with lat, lon and my api key but I am testing with the Rest Sensor but can’t get it to work. The initial problem is that the state cannot be more than 256 chars and the api will always return more than that so I am trying to use json_attributes.

In my testing none of these return any attributes but the state is set correctly.

  - platform: rest
    name: address_rest
    resource: http://www.mapquestapi.com/geocoding/v1/reverse?key=xxxxxxxxxxxxxxxxxxxx&location=99.519138,-99.321133
    value_template: '{{ value_json.info.copyright.text }}'
    json_attributes:
      - "value_json.info.copyright.imageUrl"
      - "info.copyright.imageUrl"
      - "imageUrl"
      - "value_json.results[0].locations[0].postalCode"
      - "results[0].locations[0].postalCode"

123 · February 17, 2019, 5:02pm

They may work but they are are not examples of what petro had suggested. This is an example of following petro’s explanation exclusively:

{{ value_json['results'][0]['providedLocation']['latLng']['lat'] }}

My point is simply that you’ve marked it as the solution, so other people can quickly see how it solved your problem, but you’re not using it as described.

Anyway, the upshot is there’s more than one way to extract the values and they can be combined depending on your preferences.

I believe you are correct. I’ve seen another post by someone wishing to template the REST sensor and it simply does not support templates (whereas command_line does).

If you don’t mind me asking, what is the intended use for this sensor? Reverse-geocode to be used for what? I don’t use this in my home automation so I’d like to learn more about its applications.

klogg · February 17, 2019, 5:14pm

I’m certain I tried that and failed! Must’ve had a typo somewhere…

Fair point but, “don’t follow me I’m lost” and “@petro is always right” are pretty good maxims

Not at all. It is partly a learning experience but I also thought I could use it to get the address of a device tracker without needing to use any of the custom components out there.

I stopped using google a few days ago as Life360 seems to be more than good enough on its own but Life360 doesn’t in my experience populate the address field very often (ever?) and the Places custom component is potentially brilliant but I have had too many issues with it to persevere (and I think the developer of it has stopped working on it).

I am of course now expecting you to tell me of a much simpler way I could do this that I haven’t even thought of

petro · February 17, 2019, 6:38pm

Lol I wish I was always right!

Bergerie · March 27, 2020, 9:27am

I thought I understood @petro outline and made various trials on the starting example with success. However, when I tried to apply it to my json file mentioned below, I got stuck again with no glue what I’m doing wrong, e.g. value_template: “{{ value_json.retrieve_reply.seqnr }}” or value_template: “{{ value_json[‘retrieve_reply’][‘control’][‘ch_status’] }}” gives an Unknown result. Any help would be appreciated.

{
“retrieve_reply”: {
“seqnr”: 0,
“status”: {
“device_id”: “6808-1401-3107_15-07-002-091”,
“date_time”: 638614357
},
“report”: {
“report_time”: 638614357,
“burning_hours”: 9445.14,
“details”: {
“boiler_temp”: 22.0,
“boiler_return_temp”: 22.0
}
},
“control”: {
“ch_status”: 161,
“ch_control_mode”: 0,
“ch_mode”: 2
},
“schedules”: {
“ch_schedule”: {
“base_temp”: 19.0,
“entries”: [
[]
]
},
“dhw_schedule”: {
“base_temp”: 60.0,
“entries”: [
[]
]
}
},
“configuration”: {
“temp_unit”: 0,
“time_format”: 1,
“time_zone”: 0
},
“acc_status”: 2
}
}

petro · March 27, 2020, 10:35am

Your attempts are correct. So your source data must be setup wrong or something else is wrong. Show your whole config for what you are doing.

Bergerie · March 27, 2020, 11:19am

Thanks for yr quick turnaround which is appreciated and at least with one encouraging note. Before I avail further info I would appreciate your advise where I could locate the commands for the forum postings to ensure the yaml code is formatted properly and how to insert the lengthy json file with scroll bars.

Bergerie · March 27, 2020, 12:28pm

For the location of the source.json file and sensor file, the configuration.yaml file contains:

homeassistant:
  whitelist_external_dirs:
    - /config

sensor: !include_dir_merge_list sensors

In the sensor sub-directory I have the json.yaml file containing:

- platform: file
  name: Test-JSON variable
  file_path: source.json
  value_template: "{{ value_json.retrieve_reply.seqnr }}"

The above setup works perfectly well using the jason file contained in the first post of this topic. Hence, for me there are only two issues remaining which could attribute to my failure to extract data:

the used value_template is wrongly formatted which you have already responded to is not the case;
the source.json file is corrupt, which according to online json validation does not appear to be the case.

petro · March 27, 2020, 2:25pm

your path seems wrong. I’ve never used the file platform to get something in the root config directory so I can’t be certain. But that’s where I’d start looking.

Bergerie · March 27, 2020, 2:42pm

h’mm, surprising since I use exactly the same setup for the file quoted at the beginning of this topic and that seems to be working fine. It’s all in the same directory. Only the json source file changed. Is there a possibility you check my jason file your end with the used value templates? If so, pls. let me know how I can insert a file with copy clipboard capability.

Bergerie · March 27, 2020, 3:43pm

Hi Petro, I sussed it out and it’s all working fine now. It appeared that my json file shows up with two blank lines appended at the end which screwed it up. All data I can get across based on your Feb 19 dictionary and list guidelines. Thanks for standing by and availing yourselves to sort this out.

kajmaj · January 28, 2021, 6:41pm

Hi, I would like to jump into this topic
I have following mqtt output from distant raspberry monitor ISP-RPi-mqtt-daemon:

{
  "info": {
    "timestamp": "2021-01-28T18:21:44+00:00",
    "rpi_model": "RPi 3 Model B r1.2",
    "ifaces": "e,w,b",
    "host_name": "raspberrypi",
    "fqdn": "raspberrypi.home",
    "ux_release": "buster",
    "ux_version": "5.4.79-v7+",
    "up_time": " 9:53",
    "last_update": "2021-01-28T07:09:19+00:00",
    "fs_total_gb": 16,
    "fs_free_prcnt": 18,
    "networking": {
      "eth0": {
        "IP": "192.168.1.156",
        "mac": "b8:27:eb:88:xa:84"
      },
      "wlan0": {
        "mac": "b8:27:eb:dd:f9:yz"
      }
    },
    "drives": {
      "root": {
        "size_gb": 16,
        "used_prcnt": 18,
        "device": "/dev/root",
        "mount_pt": "/"
      },
      "mnt-nfs_shares": {
        "size_gb": 2048,
        "used_prcnt": 38,
        "device-nfs": {
          "ip": "192.168.1.99",
          "dvc": "/volume1/Shinobi"
        },
        "mount_pt": "/mnt/nfs_shares"
      }
    },
    "memory": {
      "size_mb": "924.844",
      "free_mb": "618.168"
    },
    "cpu": {
      "hardware": "BCM2835",
      "model": "ARMv7 Processor rev 4 (v7l)",
      "number_cores": 4,
      "bogo_mips": "307.20",
      "serial": "00000000f688ac84"
    },
    "throttle": [
      "bad response [VCHI initialization failed] from vcgencmd"
    ],
    "temperature_c": 46.2,
    "temp_gpu_c": -1,
    "temp_cpu_c": 46.2,
    "reporter": "ISP-RPi-mqtt-daemon v1.5.4",
    "report_interval": 5
  },
  "friendly_name": "NVR Shinobi"
}

I need to extract IP, mnt-nfs_shares (size_gb, used_prcnt) and temperature_c

Could anybody advice pls? @petro I guess

123 · January 28, 2021, 8:08pm

Go to https://jsonpathfinder.com

Paste your JSON string into the left hand pane.
It will appear as a JSON object in the right hand pane.
In the right hand pane, click on the key you want (for example, IP).
The correct JSON path will appear at the top of the right hand pane.

In the following screenshot, the JSON path is x.info.networking.eth0.IP

To use it in Home Assistant, replace x with value_json to produce:

value_json.info.networking.eth0.IP

kajmaj · January 29, 2021, 5:39am

@123 Thanks a lot. I missed such a tool somehow. It saves lot of time.