Re: json aggregation question

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