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?

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 00:53
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}}` — Veloxigami, Aug 31 '23 at 13:07

Snowball · Accepted Answer · 2016-09-24T05:16:30.987

341

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

edited Sep 24 '16 at 05:16

answered Nov 26 '14 at 08:16

Snowball

11,102
3
34
51

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 – Bravo Sep 14 '17 at 23:54
5

@Bravo `select * from rabbits where info->'food' != '[]';` – Snowball Sep 15 '17 at 05:21
5

Does anyone know how this works in case you need to select an integer instead of a string/text? – Rotareti Nov 16 '18 at 01:34
9

@Rotareti You can use the [@> operator](https://www.postgresql.org/docs/9.4/functions-json.html#FUNCTIONS-JSONB-OP-TABLE): `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. – Snowball Nov 16 '18 at 04:26
@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 ? – Milan Apr 11 '19 at 11:10
For people using hibernates and needs to use the function instead of the operator, `?` corresponds to the function `jsonb_exists` – Ser Aug 27 '19 at 09:08
4

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). – virgo47 Jun 04 '21 at 12:38
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](https://stackoverflow.com/a/38328942/155193) to avoid the issue. – Lance Oct 28 '21 at 11:21
does ? only accept constant values as argument, or can it be used when joining `rabbits` table with another using a certain column? – Desperado Feb 04 '23 at 16:00

score 63 · Answer 2 · answered Jul 12 '16 at 12:29

63

You could use @> operator to do this something like

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

answered Jul 12 '16 at 12:29

gori

1,164
1
11
14

1

This is useful when the item is null as well – Lucio Nov 08 '17 at 20:33
4

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) – skplunkerin Sep 19 '18 at 22:54
@skplunkerin 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'`. – 1valdis Feb 25 '19 at 12:02
```ERROR: operator does not exist: json @> unknown LINE 3: WHERE ingredients->'ingredientId' @> '"0v3yPeZdo"';``` – Oliver Dixon Oct 05 '22 at 17:41
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 https://www.postgresql.org/docs/current/functions-json.html "Table 9.46. Additional jsonb Operators" – AlbinoDrought Nov 15 '22 at 22:27

score 29 · Answer 3 · answered Nov 15 '13 at 10:36

29

Not smarter but simpler:

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

answered Nov 15 '13 at 10:36

chrmod

1,415
12
19

2

What about records where you have carrots as substring with other context like: `{"name":"Henry", "food":["lettuce","foocarrots"]}` ? – qdev Mar 18 '21 at 18:10
1

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%';` – A-Diddy Jul 01 '21 at 03:49
Hi, is there any faster version than this? It seems to be rather slow on many rows with JSON data even with an index. – Baradé Feb 28 '22 at 16:44

score 20 · Answer 4 · edited Jul 30 '16 at 02:25

20

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

edited Jul 30 '16 at 02:25

GottZ

4,824
1
36
46

answered Apr 10 '14 at 09:23

makasprzak

5,082
3
30
49

score 14 · Answer 5 · answered Feb 08 '22 at 16:27

14

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

answered Feb 08 '22 at 16:27

lmb

321
2
7

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"] – iPhoney Sep 02 '22 at 14:09

score 4 · Answer 6 · answered Nov 09 '22 at 13:15

4

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%';

answered Nov 09 '22 at 13:15

martinkaburu

487
6
18

1

This is what I've been looking for. Thank you very much. Works great on my Rail 7 app. – blyscuit Aug 23 '23 at 10:20

score 3 · Answer 7 · answered Jan 25 '23 at 07:21

3

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

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

answered Jan 25 '23 at 07:21

cbenitez

301
1
8

Sam Hughes · Answer 8 · 2021-08-07T03:13:53.780

1

Not simpler but smarter:

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

edited Aug 07 '21 at 03:13

answered Aug 07 '21 at 03:05

Sam Hughes

665
8
10

score 1 · Answer 9 · answered Feb 27 '22 at 18:13

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

Check if a Postgres JSON array contains a string

9 Answers9

This might help.

Linked