How to convert json array into Postgres int array

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 table:

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 an 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?

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.