Returning values within a JSONB[] column postgres

Question

So I have a column within my database called divisions that lives on the company table. using a simple SELECT divisions FROM companies; I get the division rows of course

{"{\"id\": \"c88c8cf6-4b23-4575-a99f-1da966ad68a4-ceo\", \"title\": 
 \"CEO\", ... }"}

What I need to do is return just one field of each JSONB, title for example.

I have tried various methods to do this, the closest I can find seems to be

SELECT json_array_elements(divisions->>'title'::jsonb) FROM companies;

but this gives back the error

ERROR:  invalid input syntax for type json
LINE 1: SELECT json_array_elements(divisions->>'title'::jsonb) FROM ...
                                           ^
DETAIL:  Token "title" is invalid.

any idea?

Laurenz Albe · Accepted Answer · 2020-08-11 05:41:04Z

1

You need to unnest the PostgreSQL array first:

SELECT d->>'title'
FROM companies AS c
   CROSS JOIN LATERAL unnest(c.divisions) AS d(d);

edited Aug 11, 2020 at 5:41

answered Aug 10, 2020 at 15:51

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KaratemanJohnson Over a year ago

This worked with a little bit of finageling, SELECT d ->> 'title' as title FROM companies AS c CROSS JOIN LATERAL unnest(c.divisions) AS d(d); got what I needed!

AdamKG · Accepted Answer · 2020-08-10 15:50:08Z

0

unnest() the array column using a CROSS JOIN, then you can use each jsonb document normally.

testdb=# create table companies(company_id integer, divisions jsonb[]);
CREATE TABLE
testdb=# insert into companies select 1, ARRAY['{"title": "Foo Co"}', '{"title": "Bar Inc"}']::jsonb[];
INSERT 0 1
testdb=# insert into companies select 2, ARRAY['{"title": "Baz Ltd"}']::jsonb[];
INSERT 0 1
testdb=# select company_id, div->>'title' as title
from companies
cross join unnest(divisions) as div;
 company_id |  title  
------------+---------
          1 | Foo Co
          1 | Bar Inc
          2 | Baz Ltd
(3 rows)

answered Aug 10, 2020 at 15:50

AdamKG

14.1k3 gold badges41 silver badges48 bronze badges

Collectives™ on Stack Overflow

Returning values within a JSONB[] column postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related