user2719546 user2719546 - 3 months ago 7
JSON Question

Postgres JSON, aggregating over values (regardless of keys)

I have a data set like this:

{
"a1": {"score": "20"},
"a2": {"score": "30"}
}


And I want to get the average of the scores, but the aN length can vary.

Answer

Try this

SELECT avg((value->>'score')::int) FROM json_each('{"a1": {"score": "20"},"a2": {"score": "30"}}'::json);
Comments