Re: [GENERAL] json aggregation question - Mailing list pgsql-general

On 02/28/2017 08:21 AM, Chris Withers wrote:
> How can I aggregate the results of a query that equates to "show me the
> number of matching rows and the set of |tag1| value that have
> a |tag2| value of |t2val1|?
>
> ...but I really want:
>
> |count |tag1 -------+-------------------------2|["val1","val2","val3"](1row)|

Seems like this does the trick?:

     SELECT  COUNT(DISTINCT id),
             json_agg(DISTINCT elem)
     FROM    (
       SELECT  id,
               jsonb_array_elements_text(tags->'tag1') AS elem
       FROM    thing
       WHERE   tags->'tag2'?'t2val1'
     ) x;

You are looking to get always one result, right?

Yours,
Paul