how to convert array elements from string to int in postgres?

0
votes

I have a column in the table as jsonb [14,21,31] and I want to get all the rows with selected element eg

SELECT * 
FROM t_customers 
WHERE tags ?| array['21','14']

but the jsonb elements are in integer format how do i convert the sql array elements into integer i tried removing the quotes from the array but it gives an error

1
sqlpostgresqljsonb
@LaurenzAlbe: but you can't use an int[] as the right hand value for the ?| operator - that requires a text[] - a_horse_with_no_name
It would be a lot easier (and probably more efficient) if you stored those integers in an int[] column rather than a jsonb - a_horse_with_no_name
is there any other operator that i can use instead of ?| to match int[] @a_horse_with_no_name - Desmond Dias
Not with JSON arrays. As I said: this would better be stored in a native Postgres array - a_horse_with_no_name

1 Answers

0
votes

A naive solution would be:

t=# with t_customers(tags) as (values('[14,21,31]'::jsonb))
select
  tags
, translate(tags::text,'[]','{}')::int[] jsonb_int_to_arr
, translate(tags::text,'[]','{}')::int[] @> array['21','14']::int[] includes
from
t_customers;
     tags     | jsonb_int_to_arr | includes
--------------+------------------+----------
 [14, 21, 31] | {14,21,31}       | t
(1 row)

https://www.postgresql.org/docs/current/static/functions-array.html if you want to cast as array - you should use @> operator to check if contains.

(at first I proposed it because I misunderstood the question - so it goes the opposite way, "turning" jsonb to array and checking if it contains, but now maybe this naive approach is the shortest)

the right approach here probably would be:

t=# with t_customers(tags) as (values('[14,21,31]'::jsonb))
, t as (select tags,jsonb_array_elements(tags) from t_customers)
select jsonb_agg(jsonb_array_elements::text) ?| array['21','14'] tags from t group by tags;
 tags
------
 t
(1 row)

which is basically "repacking" jsonb array with text representations of integers