How to find JSON in MySQL? [duplicate]

Question

How can I find a value within a JSON array in MySQL? I am trying this option

https://sqlize.online/sql/mysql80/df842fcbe7d246262b65740c80668c4e/

create table tbl (id int, home json);

insert into tbl values 
(1, '[
    "1",
    "2",
    "3"
]');

select * 
from tbl
where json_contains(home, '1');

but there are no results.

You're missing a double quote ("") around 1. This should work: select * from tbl where json_contains(home, '"1"'); — Procrastinator
– Procrastinator, Commented Sep 22, 2023 at 12:49

SelVazi · Accepted Answer · 2023-09-22 12:44:16Z

1

You can do it as follows using JSON_CONTAINS :

select * 
from tbl
where JSON_CONTAINS(home, '"1"', '$');

PS : "1" is a string so you have to use double quotes.

This is an other way to do it using JSON_SEARCH :

select *
from tbl 
where JSON_SEARCH(home, 'one', "1") is not null

edited Sep 22, 2023 at 12:44

answered Sep 22, 2023 at 12:40

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

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

4 Comments

SelVazi Over a year ago

Glad to hear I could help :)

Taras PHP Over a year ago

Please tell me more, for some reason it does not work in PHP through mysqli_query.

Taras PHP Over a year ago

Error: $result = $mysqli->query('SELECT * from products where JSON_SEARCH(json, "one", "361")');

SelVazi Over a year ago

You will need to escape double quotes

Collectives™ on Stack Overflow

How to find JSON in MySQL? [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related