[GENERAL] json aggregation question

Поиск
Список
Период
Сортировка
От Chris Withers
Тема [GENERAL] json aggregation question
Дата
Msg-id d7814043-9bf4-112b-8098-242d6386a4eb@simplistix.co.uk
обсуждение исходный текст
Ответы Re: [GENERAL] json aggregation question
Re: [GENERAL] json aggregation question
Список pgsql-general
Hi All,

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 по дате отправления:

Предыдущее
От: Chris Withers
Дата:
Сообщение: [GENERAL] json aggregation question
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE