How to convert json array into Postgres int array

Question

I have a scenario where I need to convert a json array into a Postgres int array and query it for the result. Below is my array

      ID            DATA
       1           {"bookIds" : [1,2,3,5], "storeIds": [2,3]} 
       2           {"bookIds" : [4,5,6,7], "storeIds": [1,3]}
       3           {"bookIds" : [11,12,10,9], "storeIds": [4,3]}

I want to convert booksId array into int array and later query it. Is it possible in Postgres 9.3? I know 9.4 + provides much more JSON support but I can't update my db at the moment.

The below query gives me the error

  Select data::json->>'bookIds' :: int[] from table

 ERROR:  malformed array literal: "bookIds"
 LINE 1: Select data::json->>'bookIds' :: int[] from table

Is it possible to query elements inside json array in Postgres 9.3?

Answer 1

The setup in the question should look like this:

create table a_table (id int, data json);
insert into a_table values
(1, '{"bookIds": [1,2,3,5], "storeIds": [2,3]}'), 
(2, '{"bookIds": [4,5,6,7], "storeIds": [1,3]}'),
(3, '{"bookIds": [11,12,10,9], "storeIds": [4,3]}');

Note the proper syntax of json values.

You can use the function json_array_elements()

select id, array_agg(book_id::text::int) as book_ids
from a_table
cross join json_array_elements(data->'bookIds') a(book_id)
group by 1
order by 1;

 id |   book_ids
----+--------------
  1 | {1,2,3,5}
  2 | {4,5,6,7}
  3 | {11,12,10,9}
(3 rows)    

Use any() to search for an element in the arrays, e.g.:

select *
from (
    select id, array_agg(book_id::text::int) as book_ids
    from a_table
    cross join json_array_elements(data->'bookIds') a(book_id)
    group by 1
    ) s
where 
    1 = any(book_ids) or
    11 = any(book_ids);

 id |   book_ids
----+--------------
  1 | {1,2,3,5}
  3 | {11,12,10,9}
(2 rows)

Read also about <@ operator.

You can also search in json array (without converting it to int array) by examine its elements, e.g.:

select t.*
from a_table t
cross join json_array_elements(data->'bookIds') a(book_id)
where book_id::text::int in (1, 11);

 id |                     data                      
----+-----------------------------------------------
  1 | {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
  3 | {"bookIds" : [11,12,10,9], "storeIds": [4,3]}
(2 rows)

Generic functions

You might want to have generic tools for conversion from JSON arrays. Use the function to convert a JSON array of arbitrary strings:

create or replace function jsonb_text_array(jsonb)
returns text[] language sql immutable as $$
    select array(select jsonb_array_elements_text($1));
$$;

The resulting array is text[] that can be cast to int[]. However, when dealing specifically with integer arrays, this function should be significantly faster:

create or replace function jsonb_integer_array(jsonb)
returns integer[] language sql immutable as $$
    select translate($1::text, '[]', '{}')::integer[];
$$;

select id, jsonb_integer_array(data::jsonb->'bookIds') as book_ids
from a_table;

 id |   book_ids
----+--------------
  1 | {1,2,3,5}
  2 | {4,5,6,7}
  3 | {11,12,10,9}
(3 rows)

Note that JSONB has been available since Postgres 9.4.

Answer 2

These two functions (for json/jsonb) modified from a fantastic answer to this question work perfectly

CREATE OR REPLACE FUNCTION json_array_castint(json) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM json_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION jsonb_array_castint(jsonb) RETURNS int[] AS $f$
    SELECT array_agg(x)::int[] || ARRAY[]::int[] FROM jsonb_array_elements_text($1) t(x);
$f$ LANGUAGE sql IMMUTABLE;

You can use them as follows:

SELECT json_array_castint('[1,2,3]')

Which gives the expected return {1,2,3} as in integer[]. If you wonder why I'm concatenating with an empty array in each of the SELECT statement it's because the cast is lossy and without it, if you try to cast an empty json/jsonb array to an integer[] you'll get no return (not desired) instead of an empty array (as expected). With the above method when you do

SELECT json_array_castint('[]')

You'll get {} instead of nothing. See here for more on why I added that.

Answer 3

In my case I had to cast json data stored in a table col to pg array format and this was handy :

-- username is the table column, which has values like ["john","pete","kat"]

select id, ARRAY(SELECT json_array_elements_text((username)::json)) usernames
from public.table-name;

-- this produces : {john,pete,kat}

Answer 4

I would go a bit simpler:

select * from
(
select t.id, value::text::int as bookvalue
  from testjson t, json_array_elements(t.data->'bookIds')
) as t
where bookvalue in (1,11)

See it working here: http://sqlfiddle.com/#!15/e69aa/37