Check if a Postgres JSON array contains a string

by

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->>'name' from rabbits where exists (
  select 1 from json_array_elements(info->'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?

7 Answers
7

Leave a Comment