postgres extract int array from text

Question

I have the following postgresql statement:

SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );

Basically I have a string which contains an array of integers seperated by comma, like: [1, 2, 3] and sometimes this array could be empty too, like: []. Now, I want to write a query (as part of a bigger query) where I would be able to find out if an element is matching any integers in the text. For example:

SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') ); -- Should return true
SELECT 1 = ANY( jsonb_array_elements_text('[]') ); -- should return false

However, the above query fails with an error message:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: SELECT 1 = ANY( jsonb_array_elements_text('[2, 1, 3]') );

Any help how I can extract an integer array out of a text so that I can use it in a join condition ?

I am using postgres 9.4 if it matters.

This would be a lot easier with a native integer array rather than JSON: 1 = any(array[1,2,3]) — user330315
– user330315, Commented Nov 14, 2018 at 9:33
Yes, no doubt. But this is part of a bigger query and so I have simplified here for brevity. — Sankar
– Sankar, Commented Nov 14, 2018 at 9:35

Sankar · Accepted Answer · 2018-11-14 10:00:37Z

0

I have found it. The answer is:

SELECT 1 IN (SELECT json_array_elements('[2, 1, 3]')::text::int);
SELECT 1 IN (SELECT json_array_elements('[]')::text::int);
SELECT 1 IN (SELECT json_array_elements('[12, 10, 3]')::text::int);

answered Nov 14, 2018 at 10:00

Sankar

6,59113 gold badges70 silver badges98 bronze badges

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

Collectives™ on Stack Overflow

postgres extract int array from text

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related