current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 6 down vote favorite
1

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?

share|improve this question
    
Interesting question. I've played around with it, but then it dawned on me, I'm not sure what you mean by "better". What criteria are you judging your answers by? Readability? Efficiency? Other? –  David S Nov 12 '13 at 15:55
    
@DavidS: (I updated the question.) I'd prefer readability over efficiency. I certainly don't expect anything better than a full table scan, since I'm holding the schema fixed. –  Snowball Nov 13 '13 at 0:53

3 Answers 3

up vote 7 down vote

As of PostgreSQL 9.4, you can do

select * from rabbits where info->'food' ? '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 idxfood on rabbits using gin ((info->'food'));

Of course, you probably don't have time for that as a full-time rabbit keeper.

share|improve this answer
up vote 4 down vote

Not smarter but simpler:

select info->>'name' from rabbits WHERE info->>'food' LIKE '%"carrots"%';
share|improve this answer
up vote 3 down vote

A small variation but nothing new infact. It's really missing a feature...

select info->>'name' from rabbits where '"carrots"' = ANY (ARRAY(select * from json_array_elements(info->'food'))::text[]);
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.