agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedget counts of multiple field values in a jsonb column
4+ messages / 4 participants
[nested] [flat]
* get counts of multiple field values in a jsonb column
@ 2020-10-17 15:00 Martin Norbäck Olivers <martin@norpan.org>
0 siblings, 3 replies; 4+ messages in thread
From: Martin Norbäck Olivers @ 2020-10-17 15:00 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: get counts of multiple field values in a jsonb column
@ 2020-10-17 15:38 David G. Johnston <david.g.johnston@gmail.com>
parent: Martin Norbäck Olivers <martin@norpan.org>
2 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2020-10-17 15:38 UTC (permalink / raw)
To: Martin Norbäck Olivers <martin@norpan.org>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Sat, Oct 17, 2020 at 8:00 AM Martin Norbäck Olivers <martin@norpan.org>
wrote:
> 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.
>
If you want counts the count function is your goto aggregate function.
What you are missing is performing conditional counting.
SELECT fld, count(*) FILTER (WHERE expression) FROM query GROUP BY fld;
https://www.postgresql.org/docs/13/sql-expressions.html#SYNTAX-AGGREGATES
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: get counts of multiple field values in a jsonb column
@ 2020-10-17 16:15 Rob Sargent <robjsargent@gmail.com>
parent: Martin Norbäck Olivers <martin@norpan.org>
2 siblings, 0 replies; 4+ messages in thread
From: Rob Sargent @ 2020-10-17 16:15 UTC (permalink / raw)
To: Martin Norbäck Olivers <martin@norpan.org>; +Cc: pgsql-sql@lists.postgresql.org
> On Oct 17, 2020, at 9:00 AM, Martin Norbäck Olivers <martin@norpan.org> wrote:
>
> 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
If the “COMPLICATED QUERY” 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: get counts of multiple field values in a jsonb column
@ 2020-10-20 08:56 Simon Riggs <simon@2ndquadrant.com>
parent: Martin Norbäck Olivers <martin@norpan.org>
2 siblings, 0 replies; 4+ messages in thread
From: Simon Riggs @ 2020-10-20 08:56 UTC (permalink / raw)
To: Martin Norbäck Olivers <martin@norpan.org>; +Cc: pgsql-sql@lists.postgresql.org
On Sat, 17 Oct 2020 at 16:00, Martin Norbäck Olivers <martin@norpan.org> wrote:
>
> 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.
You want something like this...
select key, count(*) from (select (jsonb_each_text(data)).key from
COMPLICATED QUERY) as kv group by 1;
--
Simon Riggs http://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2020-10-20 08:56 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-17 15:00 get counts of multiple field values in a jsonb column Martin Norbäck Olivers <martin@norpan.org>
2020-10-17 15:38 ` David G. Johnston <david.g.johnston@gmail.com>
2020-10-17 16:15 ` Rob Sargent <robjsargent@gmail.com>
2020-10-20 08:56 ` Simon Riggs <simon@2ndquadrant.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox