Re: Extracting data from jsonb array? - Mailing list pgsql-general
On Mon, Dec 7, 2020 at 5:20 PM Rob Sargent <robjsargent@gmail.com> wrote:
On 12/7/20 6:17 PM, David G. Johnston wrote:With that correction OP might have an answer?On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent <robjsargent@gmail.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.
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)
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"}
]');
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
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: