Search Postgresql Archives

Re: Extracting data from jsonb array?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Mon, Dec 7, 2020 at 7:12 PM Steve Baldwin <steve.baldwin@xxxxxxxxx> wrote:
How about this:

b2bcreditonline=# select f.id, array_agg(t.key2) from foo as f, jsonb_to_recordset(js) as t(key2 text) group by f.id;
 id |     array_agg
----+--------------------
  2 | {r2k2val,r2k2val2}
  1 | {r1k2val,r1k2val2}
(2 rows)

Oh I like that, and thanks!  It seems a little clearer to me, but maybe that's because records still seem more familiar than json.  Applying the quantitative cumbersome-syntax test, this clocks in 8 characters shorter than the other one (99 vs. 107).  But this has a big advantage in that you can just add other fields to the query, thusly:

=> select f.id, f.f1,f.f2,array_agg(t.key2) from foo as f, jsonb_to_recordset(js) as t(key2 text) group by f.id;
 id |    f1     |     f2     |     array_agg      
----+-----------+------------+--------------------
  2 | My Text 2 | My Text 2a | {r2k2val,r2k2val2}
  1 | My Text 1 | My Text 1a | {r1k2val,r1k2val2}
(2 rows)


That clocks in at 109 characters, compared to 178 for the similar query we previously had:

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


Upgrade to v12+ for access to simpler/cleaner.  

I can't upgrade just yet, but that is something to look forward to.  Out of curiosity, what would an equivalent query look like in V12?

Cheers,
Ken




--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux