get counts of multiple field values in a jsonb column

From: Martin Norbäck Olivers <martin(at)norpan(dot)org>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: get counts of multiple field values in a jsonb column
Date: 2020-10-17 15:00:05
Message-ID: CALoTC6sN0E_oYayqFYL1d=65N+C0P3ngnr=RvXo0=1=3fPpczA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

HI!
I'm using postgres to store unstructured fields in a jsonb column. I also
have a quite complicated query on the table, joining it with other tables
etc, and given that query I want to get a count of all the values for a
number of keys in the data.

Currently I'm doing one query for each key, like this
select data->>'field1', count(*) from COMPLICATED QUERY group by 1
select data->>'field2', count(*) from COMPLICATED QUERY group by 1
...
select data->>'fieldN', count(*) from COMPLICATED QUERY group by 1

field1, field2, ..., fieldN are known at query time.

But as the number of keys I want to count for increases, so does the time
it takes to run all these queries. I think the main problem is that
COMPLICATED QUERY is complicated and takes time to run each time. I would
very much like to run only one query that counts all the values of all the
fields, but I'm not quite sure how to do that. I'm looking at all the
aggregation functions but can't quite find one that suits this purpose.

I would love to get some input on ways to make this faster.

Regards,

Martin

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2020-10-17 15:38:50 Re: get counts of multiple field values in a jsonb column
Previous Message David G. Johnston 2020-10-10 17:55:20 Re: total and partial sums in the same query??