How to convert json array into postgres int array in postgres 9.3

Question

I have scenario where i need to convert a json array into 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 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.

Below query gives me 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.. Thanks in advance ...

Solution 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(e::text::int)
from a_table, json_array_elements(data->'bookIds') e
group by 1
order by 1;

 id |  array_agg   
----+--------------
  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(e::text::int) arr
    from a_table, json_array_elements(data->'bookIds') e
    group by 1
    ) s
where 
    1 = any(arr) or
    11 = any(arr);

 id |     arr      
----+--------------
  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, json_array_elements(data->'bookIds') e
where e::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)

Solution 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.

Solution 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}

Solution 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

View more solutions

Comments

[Max Maddy]

Thank you for the answer, my problem is how do i serach for elements than? suppose i want to search for rows containing 1 and 11.

[Max Maddy]

This works like a charm but only problem when i search for more than one element of same row it gives duplicate. For example select t.* from a_table t, json_array_elements(data->'bookIds') e where e::text::int = any ('{1, 2}' :: int[]); it gives me row one two times. I can apply distinct .