OK, let me try asking again. (I'm trying to actually get something that works.) So given an example like this:
CREATE TEMP TABLE foo (
id INTEGER,
js JSONB
);
INSERT INTO foo (id,js) VALUES (1,
'[
{"key":"r1kval","key2":"r1k2val"},
{"key":"r1kval2","key2":"r1k2val2"}
]');
INSERT INTO foo (id,js) VALUES (2,
'[
{"key":"r2kval","key2":"r2k2val"},
{"key":"r2kval2","key2":"r2k2val2"}
]');
Can anyone help me with a working query (preferably with the least cumbersome syntax possible!) that would return these values (the key2 values) as array text elements:
id Agg_val
---- ------------------------
1 {r1k2val,r1k2val2}
2 {r2k2val,r2k2val}
(2 rows)
postgres=# select id, array_agg(fa) from (select id, (jsonb_array_elements(js)->'key') as fa from foo) g group by id order by id;
id | array_agg
----+------------------------------
1 | {"\"r1kval\"","\"r1kval2\""}
2 | {"\"r2kval\"","\"r2kval2\""}
(2 rows)
I think the quotes are a fault of example data?
Cumbersome is in the eyes of the beholder ;)