Re: Extracting data from jsonb array?

On Mon, Dec 7, 2020 at 5:20 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
> On 12/7/20 6:17 PM, David G. Johnston wrote:
>
> On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
>>
>> 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?
>>
> The quotes are the fault of the query author choosing the "->" operator
> instead of "->>".
>
> David J.
>
> With that correction OP might have an answer?
>

Thank you Rob! I would say yes, except I fear I over-simplified my
example. What if there are other fields in the table, and I want to treat
this array_agg as just another field? So here's the query you had (with
the ->> change):

=> select id, array_agg(fa) from (select id,
(jsonb_array_elements(js)->>'key') as fa from foo) g group by id;
id | array_agg
----+------------------
1 | {r1kval,r1kval2}
2 | {r2kval,r2kval2}
(2 rows)

And here's the table/data with two other fields added, f1 & f2:

CREATE TEMP TABLE foo (
id INTEGER PRIMARY KEY,
f1 TEXT,
f2 TEXT,
js JSONB
);
INSERT INTO foo (id,f1,f2,js) VALUES (1,'My Text 1','My Text 1a',
'[
{"key":"r1kval","key2":"r1k2val"},
{"key":"r1kval2","key2":"r1k2val2"}
]');
INSERT INTO foo (id,f1,f2,js) VALUES (2,'My Text 2','My Text 2a',
'[
{"key":"r2kval","key2":"r2k2val"},
{"key":"r2kval2","key2":"r2k2val2"}
]');

If I want all 4 of my fields, all I can think to do is join your query back
to the table. Something like this:

=> SELECT id,f1,f2,array_agg AS vals FROM foo LEFT JOIN (select id,
array_agg(fa) from (select id, (jsonb_array_elements(js)->>'key') as fa
from foo) g group by id) foo2 USING (id);

id | f1 | f2 | vals
----+-----------+------------+------------------
1 | My Text 1 | My Text 1a | {r1kval,r1kval2}
2 | My Text 2 | My Text 2a | {r2kval,r2kval2}
(2 rows)

That seems to work, but is there any other way to streamline or simplify
that?

Cumbersome is in the eyes of the beholder ;)

Maybe. There's probably an aesthetic component, but also an aspect that
can be quantified, likely in character counts. :)

I'm of course very glad Postgresql has the ability to work with JSON at
all, but as I dig into it I'm kinda surprised at the level of complexity
needed to extract data in relatively simple ways. Hopefully eventually it
will seem simple to me, as it seems to appear to others.

Cheers,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.