Re: Extracting data from jsonb array?

> 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 ;)