Average of numeric values in Postgres JSON column

Question

I have a jsonb column with the following structure:

{
    "key1": {
        "type": "...",
        "label": "...",
        "variables": [
            {
                "label": "Height",
                "value": 131315.9289,
                "variable": "myVar1"
            },
            {
                "label": "Width",
                "value": 61085.7525,
                "variable": "myVar2"
            }
        ]
    },
}

I want to query for the average height across all rows. The top-level key values are unknown, so I have something like this:

select id,
       avg((latVars ->> 'value')::numeric) as avg
from "MyTable",
    jsonb_array_elements((my_json_field->jsonb_object_keys(my_json_field)->>'variables')::jsonb) as latVars
where my_json_field is not null
group by id;

It's throwing the following error: ERROR: set-returning functions must appear at top level of FROM

Moving the jsonb_array_elements function above MyTable in the FROM clause doesn't work.

I'm following the basic advice found in this SO answer to no avail.

Any advice?

Edouard · Accepted Answer · 2021-12-17 20:12:45Z

0

jsonb_array_elements is not relevant until my_json_field is a json array at the top level.

You can use instead the jsonb_path_query function based on the jsonpath language if postgres >= 12 :

select id
     , avg(v.value :: numeric) as avg
from "MyTable"
   , jsonb_path_query(my_json_field, '$.*.variables[*] ? (@.label == "Height").value') AS v(value)
where my_json_field is not null
group by id;

answered Dec 17, 2021 at 20:12

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Average of numeric values in Postgres JSON column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related