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->>'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?

Teran

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? – Eileen

@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. – Teran

How to check the existence of a json in a json array that contains list of jsons? Basically in this form of json: {"a":[{"id":1,mark:23},{"id":2,mark:45}], "b":[{"id":4324,mark:21233},{"id":2131,mark:52123}]} I want to find out whether there is a combination as such available: {"a":{"id":1,mark:23}, "b":{"id":2131,mark:52123}} – Entresol

Answer 1

As of PostgreSQL 9.4, you can use the ? operator:

select info->>'name' from rabbits where (info->'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->'food'));
select info->>'name' from rabbits where info->'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->>'name' from rabbits where exists (
d(# select 1 from json_array_elements(info->'food') as food
d(#   where food::text = '"carrots"'
d(# );
 Execution time: 3084.927 ms

d=# -- Postgres 9.4+ solution
d=# explain analyze select info->'name' from rabbits where (info->'food')::jsonb ? 'carrots';
 Execution time: 1255.501 ms

d=# alter table rabbits alter info type jsonb using info::jsonb;
d=# explain analyze select info->'name' from rabbits where info->'food' ? 'carrots';
 Execution time: 465.919 ms

d=# create index on rabbits using gin ((info->'food'));
d=# explain analyze select info->'name' from rabbits where info->'food' ? 'carrots';
 Execution time: 256.478 ms

Teran

how to get the rows where food array inside json is non empty , for example if we can consider , their are JSON , where food array also empty ,can you help – Diapedesis

@Diapedesis select * from rabbits where info->'food' != '[]'; – Teran

Does anyone know how this works in case you need to select an integer instead of a string/text? – Tamelatameless

@Tamelatameless You can use the @> operator: create table t (x jsonb); insert into t (x) values ('[1,2,3]'), ('[2,3,4]'), ('[3,4,5]'); select * from t where x @> '2';. Note that '2' is a JSON number; don't be mislead by the quotes. – Teran

@Snowball, this query select info->>'name' from rabbits where (info->'food')::jsonb ? 'carrots'; is working perfect for search word from JSON. But how I can get all the records does not contain 'carrots' word ? – Purpleness

For people using hibernates and needs to use the function instead of the operator, ? corresponds to the function jsonb_exists – Bursa

Using JSONB for info, wouldn't it be better to use where info @> '{"food":["carrots"]}'? This uses the GIN index on the info column while using -> (like ext->'hobbies' @> '"eating"') prevents it. This means there is no need for indexing the JSON keys, only the whole column once (if "contains" operation is enough, of course). – Beaty

If you're using Java and JDBC or JPA, one downside of using the ? operator is that it gets improperly conflated with the JDBC parameter syntax. I could not get around this in JPA, but was able to use an escaped form in a JDBC query (??). I eventually went with the @> syntax as shown in gori's answer to avoid the issue. – Lucrecialucretia

does ? only accept constant values as argument, or can it be used when joining rabbits table with another using a certain column? – Lyric

Answer 2

You could use @> operator to do this something like

SELECT info->>'name'
FROM rabbits
WHERE info->'food' @> '"carrots"';

Stayathome

This is useful when the item is null as well – Grillo

Make sure you pay attention to the ' ticks surrounding "carrots"... it breaks if you leave those out, even if you're checking for an integer. (spent 3 hours trying to find an integer, having it magically work by wrapping ' ticks around the number) – Grace

@Grace It should be json value surrounded with ' ticks to form a string, because everything is a string for SQL in JSONB type. For example, boolean: 'true', string: '"example"', integer: '123'. – Rhyton

ERROR: operator does not exist: json @> unknown LINE 3: WHERE ingredients->'ingredientId' @> '"0v3yPeZdo"'; – Cornall

The "@>" operator is only available for JSONB columns and is described as checking "Does the first JSON value contain the second?", for more details see postgresql.org/docs/current/functions-json.html "Table 9.46. Additional jsonb Operators" – Radiophotograph

Answer 3

Not smarter but simpler:

select info->>'name' from rabbits WHERE info->>'food' LIKE '%"carrots"%';

Incardinate

What about records where you have carrots as substring with other context like: {"name":"Henry", "food":["lettuce","foocarrots"]} ? – Laveta

The simplicity of this answer not only helped me, but also aligns with the PostgreSQL documentation. However, I had to remove the double-quotes ('"') to get it to work. NOTE: It appears the first character needs to be a wildcard ('%') to use them at all. ... WHERE info->>'food' LIKE '%carrots%'; – Tilsit

Hi, is there any faster version than this? It seems to be rather slow on many rows with JSON data even with an index. – Teazel

Answer 4

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[]);

Hade

Answer 5

If the array is at the root of the jsonb column, i.e. column looks like:

food
["lettuce", "carrots"]
["carrots", "zucchini"]

just use the column name directly inside the brackets:

select * from rabbits where (food)::jsonb ? 'carrots';

Noumenon

If I have an array of numbers, this does not work. I haven't found what works except converting numbers to strings like instead of storing [1,2,3] store ["1","2","3"] – Lone

Answer 6

In order to select the specific key in the JSONB, you should use ->.

select * from rabbits where (info->'food')::jsonb ? 'carrots';

Queue

Answer 7

You can do a direct type cast from jsonb to text incase you want to check the full json and not one key.

select * from table_name
where 
column_name::text ilike '%Something%';

Cheque

This is what I've been looking for. Thank you very much. Works great on my Rail 7 app. – Prepossessing

Answer 8

Not simpler but smarter:

select json_path_query(info, '$ ? (@.food[*] == "carrots")') from rabbits

Coterminous

Answer 9

This might help.

SELECT a.crops ->> 'contentFile' as contentFile
FROM ( SELECT json_array_elements('[
    {
        "cropId": 23,
        "contentFile": "/menu/wheat"
    },
    {
        "cropId": 25,
        "contentFile": "/menu/rice"
    }
]') as crops ) a
WHERE a.crops ->> 'cropId' = '23';

OUTPUT:

/menu/wheat

Karyolysis