Get counts of JSONB properties

Question

I have jsonb column answers which may look like this for a 3 rows:

{answer1: {label: "", value: "answer1_value1"}, answer2: {label: "", value: "answer2_value1"}}
{answer1: {label: "", value: "answer1_value2"}, answer2: {label: "", value: "answer2_value1"}}
{answer1: {label: "", value: "answer1_value2"}, answer2: {label: "", value: "answer2_value2"}}

Answer properties names can be different for a set of rows. I want to get this result(count for each answer grouped by value) from the example above:

answer1: { answer1_value1: 1, answer1_value2: 2}
answer2: { answer2_value1: 2, answer2_value2: 1}

Is it possible?

I got field names like this SELECT DISTINCT json_object_keys(answers::json) as field, but do not know what can I do next.

Answer 1

Use jsonb_each() to be able to count answers and aggregate the results back into a json object:

select jsonb_build_object(key, jsonb_object_agg(val, count))
from (
    select key, value->>'value' as val, count(*)
    from my_table
    cross join jsonb_each(answers) 
    group by key, val
    ) s
group by key;

Working example in rextester.