How to search multiple items in JSON array in Postgres 9.3

Question

I have scenario where i need to search multiple values in a JSON array. Below is my schema.

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

I want all the rows with value 1,2. Below is query i am using (This is query is written by one of fellow stackoverflow user Mr. klin credit to him).

  select t.*
  from JSONTest t, json_array_elements(data->'bookIds') books
  where books::text::int in (1, 2);

However output I am duplicate rows in output, below is my output.

     id      data
     1       {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
     1       {"bookIds" : [1,2,3,5], "storeIds": [2,3]}
     2       {"bookIds" : [1,2], "storeIds": [1,3]}
     2       {"bookIds" : [1,2], "storeIds": [1,3]}

I want only two rows in output that is id 1,2. How can i do that? I don't want use Distinct due to other constraints,

SQL Fiddle : http://sqlfiddle.com/#!15/6457a/2

user330315 · Accepted Answer · 2016-06-09 22:09:12Z

1

Unfortunately there is no direct conversion function from a JSON array to a "real" Postgres array. (data ->'bookIds')::text returns something that is nearly a Postgres array literal: e.g. [1,2,3,5]. If you replace the [] with {} the value can be cast to an integer array. Once we have a proper integer array we can use the @> to test if it contains another array:

select *
from jsontest
where translate((data ->'bookIds')::text, '[]', '{}')::int[] @> array[1,2];

translate((data ->'bookIds')::text, '[]', '{}') will convert [1,2,3,5] to {1,2,3,5} which then is converted to an array using ::int[]

SQLFiddle: http://sqlfiddle.com/#!15/6457a/4

answered Jun 9, 2016 at 22:09

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Max Maddy Over a year ago

Thanks this gave me some way to proceed :) How costly is this operation?

Collectives™ on Stack Overflow

How to search multiple items in JSON array in Postgres 9.3

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related