current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Dismiss
Announcing Stack Overflow Documentation

We started with Q&A. Technical documentation is next, and we need your help.

Whether you're a beginner or an experienced developer, you can contribute.

Sign up and start helping → Learn more about Documentation →
up vote 0 down vote favorite

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

share|improve this question
up vote 2 down vote accepted

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)
share|improve this answer
    
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 Jun 7 at 18:22
    
See the edited answer. – klin Jun 7 at 18:40
    
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 . – Max Maddy Jun 8 at 4:17
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.