Extract elements from an array inside a jsonb field in Postgresql

Question

In my Postgresql table, I have a jsonb field called data, which contains data in the following format:

{
 list:[1,2,3,4,5]
}

I use the query:

select data->'list' from "Table" where id=1

This gives me the array [1,2,3,4,5]

The problem is that I want to use this result in another select query within the IN clause. It's not accepting the array.

IN ([1,2,3,4,5]) fails

It wants:

IN (1,2,3,4,5)

So, In my original query I don't know how to covert [1,2,3,4,5] to just 1,2,3,4,5

My current query is:

select * from "Table2" where "items" in (select data->'list' from "Table" where id=1)

Please help

I don't understand how exactly you need to use the values in the list. Please edit your question and add the complete query where you are trying to access the array's elements. — user330315
– user330315, Commented Apr 14, 2020 at 6:22

Nick · Accepted Answer · 2020-04-14 06:42:56Z

1

You can use the array contains operator (@>) rather than IN if you cast the search value to jsonb. For example:

SELECT *
FROM "Table2"
WHERE items::jsonb <@ (SELECT data->'list' FROM "Table" WHERE id=1)

Note that if items is an int you will need to cast it char before casting to jsonb:

SELECT *
FROM "Table2"
WHERE cast(items as char)::jsonb <@ (SELECT data->'list' FROM "Table" WHERE id=1)

Demo on dbfiddle

edited Apr 14, 2020 at 6:42

answered Apr 14, 2020 at 6:39

Nick

147k23 gold badges67 silver badges106 bronze badges

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

Comments

score 0 · Accepted Answer · 2020-04-14 07:14:31Z

0

Use jsonb_array_elements() to turn the elements into rows

select t2.*
from table_2 t2
where t2.items in (select jsonb_array_elements_text(t1.data -> 'list')::int
                   from table_1 t1 
                   where t1.id = 1);

This assumes that items is defined as text or varchar and contains a single value - however the name (plural!) seems to indicate yet another de-normalized column.

edited Apr 14, 2020 at 7:14

answered Apr 14, 2020 at 6:40

user330315

4 Comments

asanas Over a year ago

I think this is close but I still get error. I created this demo dbfiddle.uk/…

user330315 Over a year ago

@asanas: well, you are comparing an integer with a text value. You need to cast the result of the array. See my edit

user330315 Over a year ago

@asanas Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/…

asanas Over a year ago

The query worked. Thanks a lot!. Yes, they are painful. I will avoid them.

Collectives™ on Stack Overflow

Extract elements from an array inside a jsonb field in Postgresql

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related