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
– David S, Commented Nov 12, 2013 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
– Snowball, Commented Nov 13, 2013 at 0: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
– Veloxigami, Commented Aug 31, 2023 at 13:07

Snowball · Accepted Answer · 2016-09-24 05:16:30Z

374

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, 2016 at 5:16

answered Nov 26, 2014 at 8:16

Snowball

11.8k3 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Bravo Over a year ago

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

Snowball Over a year ago

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

Rotareti Over a year ago

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

Snowball Over a year ago

@Rotareti 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.

virgo47 Over a year ago

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).

|

gori · Accepted Answer · 2016-07-12 12:29:40Z

71

You could use @> operator to do this something like

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

answered Jul 12, 2016 at 12:29

gori

1,3081 gold badge12 silver badges14 bronze badges

6 Comments

Lucio Over a year ago

This is useful when the item is null as well

skplunkerin Over a year ago

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)

1valdis Over a year ago

@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'.

Oliver Dixon Over a year ago

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

AlbinoDrought Over a year ago

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"

|

chrmod · Accepted Answer · 2013-11-15 10:36:51Z

32

Not smarter but simpler:

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

answered Nov 15, 2013 at 10:36

chrmod

1,45512 silver badges19 bronze badges

3 Comments

qdev Over a year ago

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

A-Diddy Over a year ago

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

Baradé Over a year ago

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

GottZ · Accepted Answer · 2016-07-30 02:25:50Z

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, 2016 at 2:25

GottZ

4,9771 gold badge39 silver badges48 bronze badges

answered Apr 10, 2014 at 9:23

makasprzak

5,2103 gold badges33 silver badges51 bronze badges

Comments

lmb · Accepted Answer · 2022-02-08 16:27:15Z

18

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 8, 2022 at 16:27

lmb

3612 silver badges8 bronze badges

1 Comment

iPhoney Over a year ago

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"]

martinkaburu · Accepted Answer · 2022-11-09 13:15:53Z

14

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 9, 2022 at 13:15

martinkaburu

5947 silver badges21 bronze badges

1 Comment

blyscuit Over a year ago

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

bmusonda · Accepted Answer · 2023-01-25 07:21:41Z

9

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

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

answered Jan 25, 2023 at 7:21

bmusonda

7216 silver badges9 bronze badges

Comments

Sam Hughes · Accepted Answer · 2021-08-07 03:13:53Z

2

Not simpler but smarter:

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

edited Aug 7, 2021 at 3:13

answered Aug 7, 2021 at 3:05

Sam Hughes

7958 silver badges14 bronze badges

Comments

M. Hamza Rajput · Accepted Answer · 2022-02-27 18:13:31Z

1

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

answered Feb 27, 2022 at 18:13

M. Hamza Rajput

10.5k3 gold badges53 silver badges42 bronze badges

Comments

Hadi Salehy · Accepted Answer · 2024-12-14 14:41:41Z

0

You can use the POSITION() function or the LIKE operator with wildcard characters.

SELECT * FROM your_table WHERE (POSITION( "Feild1" IN "Feild2") > 0) ;

or

SELECT * FROM your_table WHERE "Feild2" like  '%' ||  "Feild1" || '%';

answered Dec 14, 2024 at 14:41

Hadi Salehy

3592 silver badges6 bronze badges

Collectives™ on Stack Overflow

Check if a Postgres JSON array contains a string

10 Answers 10

9 Comments

6 Comments

3 Comments

Comments

1 Comment

1 Comment

Comments

Comments

This might help.

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

9 Comments

6 Comments

3 Comments

Comments

1 Comment

1 Comment

Comments

Comments

This might help.

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related