Check if a Postgres JSON array contains a string

Question

I have a table to store information about my rabbits  It looks like this   create table rabbits  rabbit id bigserial primary key  info json not null   insert into rabbits  info  values       name   Henry    food    lettuce   carrots             name   Herald   food    carrots   zucchini             name   Helen    food    lettuce   cheese         How should I find the rabbits who like carrots  I came up with this   select info- gt  gt  name  from rabbits where exists     select 1 from json array elements info- gt  food   as food   where food  text     carrots        I don t like that query  It s a mess   As a full-time rabbit-keeper  I don t have time to change my database schema  I just want to properly feed my rabbits  Is there a more readable way to do that query

User · Answer

Not smarter but simpler   select info- gt  gt  name  from rabbits WHERE info- gt  gt  food  LIKE    carrots

User · Answer

As of PostgreSQL 9 4  you can use the   operator   select info- gt  gt  name  from rabbits where  info- gt  food    jsonb    carrots     You can even index the   query on the  food  key if you switch to the jsonb type instead   alter table rabbits alter info type jsonb using info  jsonb  create index on rabbits using gin   info- gt  food     select info- gt  gt  name  from rabbits where info- gt  food     carrots     Of course  you probably don t have time for that as a full-time rabbit keeper   Update  Here s a demonstration of the performance improvements on a table of 1 000 000 rabbits where each rabbit likes two foods and 10  of them like carrots   d   -- Postgres 9 3 solution d   explain analyze select info- gt  gt  name  from rabbits where exists   d   select 1 from json array elements info- gt  food   as food d     where food  text     carrots   d       Execution time  3084 927 ms  d   -- Postgres 9 4  solution d   explain analyze select info- gt  name  from rabbits where  info- gt  food    jsonb    carrots    Execution time  1255 501 ms  d   alter table rabbits alter info type jsonb using info  jsonb  d   explain analyze select info- gt  name  from rabbits where info- gt  food     carrots    Execution time  465 919 ms  d   create index on rabbits using gin   info- gt  food     d   explain analyze select info- gt  name  from rabbits where info- gt  food     carrots    Execution time  256 478 ms

User · Answer

You could use    operator to do this something like  SELECT info- gt  gt  name  FROM rabbits WHERE info- gt  food    gt    carrots

User · Answer

A small variation but nothing new infact  It s really missing a feature     select info- gt  gt  name  from rabbits  where   carrots     ANY  ARRAY      select   from json array elements info- gt  food     text

[json] Check if a Postgres JSON array contains a string

Examples related to json

Examples related to postgresql

Examples related to postgresql-9.3