[GENERAL] json aggregation question
| От | Chris Withers |
|---|---|
| Тема | [GENERAL] json aggregation question |
| Дата | |
| Msg-id | fd1a6fb0-cec9-49b6-9c98-35774d9a86e7@withers.org обсуждение исходный текст |
| Список | pgsql-general |
Hi All,
Chris
Given the following table:
# create table thing (id serial, tags jsonb);
# \d thing Table "public.thing"Column | Type | Modifiers
--------+---------+----------------------------------------------------id | integer | not null default nextval('thing_id_seq'::regclass)tags | jsonb | ...and the following data:
insert into thing (tags) values ('{"tag1": ["val1", "val2"], "tag2": ["t2val1"]}');
insert into thing (tags) values ('{"tag1": ["val3", "val1"], "tag2": ["t2val1"]}');
insert into thing (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:
# select count(*), json_agg(tags) from thing where tags->'tag2'?'t2val1';count | json_agg
-------+-------------------------------------------------------------------------------------------------- 2 | [{"tag1": ["val1", "val2"], "tag2": ["t2val1"]}, {"tag1": ["val3", "val1"], "tag2": ["t2val1"]}]
(1 row)...but I really want:
count | tag1
-------+------------------------- 2 | ["val1", "val2", "val3"]
(1 row) cheers,Chris
В списке pgsql-general по дате отправления: