Re: json aggregation question

Thanks, this is closer, but regex really scares me for something like
this...

On 28/02/2017 17:19, Yasin Sari wrote:
> Hi Chris,
>
> Maybe there is an another better solution;
>
> 1. sending values into jsonb_array_elements to getting elements
> (lateral join)
> 2. distinct to eliminate duplicates
> 3. regexp_replace to remove malformed Array literals
> 4. Casting into text array
>
> SELECT
> count(distinct tags ),
> string_to_array(regexp_replace(string_agg(distinct elem::text ,
> ','),'\[*\"*\s*\]*','','g'),',') AS list
> from thing as t, jsonb_array_elements(t.tags->'tag1') elem
> where tags->'tag2'?'t2val1'
>
> count | tag1
> 2 | {val1,val2,val3}
>
>
>
> 28 Şub 2017 Sal, 19:22 tarihinde, Chris Withers
> <chris(at)simplistix(dot)co(dot)uk <mailto:chris(at)simplistix(dot)co(dot)uk>> şunu yazdı:
>
> Hi All,
>
> Given the following table:
>
> |#createtablething (id serial,tags jsonb);#\d thing
> Table"public.thing"Column|Type |Modifiers
> --------+---------+----------------------------------------------------id
> |integer |notnulldefaultnextval('thing_id_seq'::regclass)tags
> |jsonb ||
>
> ...and the following data:
>
> |insertintothing (tags)values('{"tag1": ["val1", "val2"], "tag2":
> ["t2val1"]}');insertintothing (tags)values('{"tag1": ["val3",
> "val1"], "tag2": ["t2val1"]}');insertintothing
> (tags)values('{"tag1": ["val2", "val1"], "tag2": ["t2val2"]}');|
>
> 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|?
>
> The closes I can get is:
>
> |#selectcount(*),json_agg(tags)fromthing
> wheretags->'tag2'?'t2val1';count |json_agg
> -------+--------------------------------------------------------------------------------------------------2|[{"tag1":["val1","val2"],"tag2":["t2val1"]},{"tag1":["val3","val1"],"tag2":["t2val1"]}](1row)|
>
> ...but I really want:
>
> |count |tag1
> -------+-------------------------2|["val1","val2","val3"](1row)|
>
> cheers,
>
> Chris
>