Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.4\))
Subject: Re: get counts of multiple field values in a jsonb column
From: Rob Sargent <robjsargent@gmail.com>
In-Reply-To: 
 <CALoTC6sN0E_oYayqFYL1d=65N+C0P3ngnr=RvXo0=1=3fPpczA@mail.gmail.com>
Date: Sat, 17 Oct 2020 10:15:12 -0600
Cc: pgsql-sql@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <D96111AE-073A-4A0F-865A-BA34B57EC92C@gmail.com>
References: 
 <CALoTC6sN0E_oYayqFYL1d=65N+C0P3ngnr=RvXo0=1=3fPpczA@mail.gmail.com>
To: =?utf-8?Q?Martin_Norb=C3=A4ck_Olivers?= <martin@norpan.org>
Precedence: bulk


> On Oct 17, 2020, at 9:00 AM, Martin Norb=C3=A4ck Olivers =
<martin@norpan.org> wrote:
>=20
> 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.
>=20
> 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
>=20
> field1, field2, ..., fieldN are known at query time.
>=20
> 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.
>=20
> I would love to get some input on ways to make this faster.
>=20
> Regards,
>=20
> Martin

If the =E2=80=9CCOMPLICATED QUERY=E2=80=9D is repeated verbatim each =
time, run it once into a temp table and do the counts against the temp =
table.

create temp table complicate as COMPLICATED_QUERY;
/*maybe index*/
select data->>'field1', count(*) from COMPLICATED QUERY group by 1

You can put the whole thing in a function and drop the temp table each =
run.


A CTE based on COMPLICATED_QUERY might work too.=