user2719546 user2719546 - 1 year ago 96
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 Source

Try this

SELECT avg((value->>'score')::int) FROM json_each('{"a1": {"score": "20"},"a2": {"score": "30"}}'::json);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download