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
<ch...@simplistix.co.uk <mailto:ch...@simplistix.co.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
