Extract data from JSON (conditional)

Configuration
,
1

Hi all,

Goal:
For multiple datasets, I want to go through all orders and find the latest filled order (where “filled: 1”). Then from that specific order, I want to take the “sum”, then add it to the next datasets sum value for as long as there might be datasets.

I receive the following JSON structure from a REST call:

{
    "date": "2024-12-19T10:54:28.116Z",
    "data": [
        {
            "active": true,
            "date": "2024-12-16T21:55:15.022Z",
            "status": 0,
            "orders": [
                {
                    "orderNo": 1,
                    "orderId": 1944875176,
                    "amount": 15.48,
                    "sum": 15.48,
                    "filled": 1,
                    "dateFilled": "2024-12-16T21:55:15.981Z"
                },
                {
                    "orderNo": 2,
                    "orderId": 1944976052,
                    "amount": 15.24,
                    "sum": 30.72,
                    "filled": 1,
                    "dateFilled": "2024-12-16T22:37:53.530Z"
                },
                {
                    "orderNo": 3,
                    "orderId": 1945133844,
                    "amount": 16.75,
                    "sum": 47.47,
                    "filled": 1,
                    "dateFilled": "2024-12-16T23:35:39.312Z"
                },
                {
                    "orderNo": 4,
                    "orderId": 1945243976,
                    "amount": 18.42,
                    "sum": 65.89,
                    "filled": 1,
                    "dateFilled": "2024-12-17T00:07:56.709Z"
                },
                {
                    "orderNo": 5,
                    "orderId": "",
                    "amount": 57.58,
                    "sum": 123.47,
                    "filled": 0
                },
                {
                    "orderNo": 6,
                    "orderId": "",
                    "amount": 57.58,
                    "sum": 181.05,
                    "filled": 0
                }
            ],
            "createdAt": "2024-12-16T21:55:15.024Z",
            "updatedAt": "2024-12-16T21:55:15.024Z"
        }
    ]
}

In this example, the result should be 65.89. If there were more entries with results X and Y, my overall result should be 65.89 + X +Y.

I am okay with finding a specific set in the JSON like this:

value_template: "{{ value_json.0.data[0].orders[3].sum }}"

But I have absolutely no clue how to iterate through this while summing up. Can anyone help out?

Thanks!

2

Maybe there is something shorter but this works

{{ (value_json.data.0.orders | selectattr('filled','eq', 1) | list | last).sum }}

But I am not 100% sure as you say you want to iterate / add to the ‘last’ but in your example 65.89 is the last which is not an iteration / a sum of the previous ones

EDIT: this one sums up all with filed = 1

{{ value_json.data.0.orders | selectattr('filled','eq', 1) | map(attribute='sum') | sum }}
1 Like
3

Hi @vingerha - wow, halfway there! Thank you very much.

Your first expression will give me the value of the last filled sum field, which is correct:

{{ (value_json.0.data.0.orders | selectattr(‘filled’,‘eq’, 1) | list | last).sum }}

However, the real JSON is way bigger and has more entries on the first layer (I had stripped down the JSON for readability). Any idea how to iterate on the first level as well, then sum the above results alltogether?

For reference, I have extended the JSON accordingly (the overall result should be 65.89 + 10 = 75.89

{
    "date": "2024-12-19T10:54:28.116Z",
    "data": [
        {
            "active": true,
            "date": "2024-12-16T21:55:15.022Z",
            "status": 0,
            "orders": [
                {
                    "orderNo": 1,
                    "orderId": 1944875176,
                    "amount": 15.48,
                    "sum": 15.48,
                    "filled": 1,
                    "dateFilled": "2024-12-16T21:55:15.981Z"
                },
                {
                    "orderNo": 2,
                    "orderId": 1944976052,
                    "amount": 15.24,
                    "sum": 30.72,
                    "filled": 1,
                    "dateFilled": "2024-12-16T22:37:53.530Z"
                },
                {
                    "orderNo": 3,
                    "orderId": 1945133844,
                    "amount": 16.75,
                    "sum": 47.47,
                    "filled": 1,
                    "dateFilled": "2024-12-16T23:35:39.312Z"
                },
                {
                    "orderNo": 4,
                    "orderId": 1945243976,
                    "amount": 18.42,
                    "sum": 65.89,
                    "filled": 1,
                    "dateFilled": "2024-12-17T00:07:56.709Z"
                },
                {
                    "orderNo": 5,
                    "orderId": "",
                    "amount": 57.58,
                    "sum": 123.47,
                    "filled": 0
                },
                {
                    "orderNo": 6,
                    "orderId": "",
                    "amount": 57.58,
                    "sum": 181.05,
                    "filled": 0
                }
            ],
            "createdAt": "2024-12-16T21:55:15.024Z",
            "updatedAt": "2024-12-16T21:55:15.024Z"
        },
        {
            "active": true,
            "date": "2024-12-16T21:55:15.022Z",
            "status": 0,
            "orders": [
                {
                    "orderNo": 1,
                    "orderId": 1944875176,
                    "amount": 1,
                    "sum": 1,
                    "filled": 1,
                    "dateFilled": "2024-12-16T21:55:15.981Z"
                },
                {
                    "orderNo": 2,
                    "orderId": 1944976052,
                    "amount": 1,
                    "sum": 2,
                    "filled": 1,
                    "dateFilled": "2024-12-16T22:37:53.530Z"
                },
                {
                    "orderNo": 3,
                    "orderId": 1945133844,
                    "amount": 3,
                    "sum": 5,
                    "filled": 1,
                    "dateFilled": "2024-12-16T23:35:39.312Z"
                },
                {
                    "orderNo": 4,
                    "orderId": 1945243976,
                    "amount": 5,
                    "sum": 10,
                    "filled": 1,
                    "dateFilled": "2024-12-17T00:07:56.709Z"
                },
                {
                    "orderNo": 5,
                    "orderId": "",
                    "amount": 10,
                    "sum": 20,
                    "filled": 0
                },
                {
                    "orderNo": 6,
                    "orderId": "",
                    "amount": 15,
                    "sum": 35,
                    "filled": 0
                }
            ],
            "createdAt": "2024-12-16T21:55:15.024Z",
            "updatedAt": "2024-12-16T21:55:15.024Z"
        }
    ]
}
4 
{% set ns = namespace(sum1=0) %}
{% for x in value_json.data %}
{% set ns.sum1 = ns.sum1 + (x.orders | selectattr('filled','eq', 1) | list | last).sum | float(0) %}
{% endfor %}
{{ ns.sum1 }}
1 Like
5

You are awesome, thanks.

There was a typo in your answer

  • json_value instead of value_json
  • I had to append a 0, don’t ask me why:

{% for x in value_json.0.data %}

With those changes it is working as expected, thank you so much :heart:

6

Difficult… as it works with me without :slight_smile:

7

Strange :slight_smile:

One last thing. I am now trying to implement this in my rest sensor in configuration.yaml which comes back with a lot of syntax issues.

sensor:
  - platform: rest
    resource_template: url
    method: GET
    scan_interval: 300
    name: "Sum"
    unit_of_measurement: "$"
    value_template: "{% set ns = namespace(sum1=0) %}
{% for x in value_json.0.data %}
{% set ns.sum1 = ns.sum1 + (x.orders | selectattr('filled','eq', 1) | list | last).sum | float(0) %}
{% endfor %}
{{ ns.sum1 }}"

This worked with easier templates before but the variables/for statements seem to be wrong here. Any idea?

Thanks

8

I had no idea there was a thing called multi line notation, that worked out.

9

Small steps … been there too … hope it works now