MySQL Query Using JSON_CONTAINS

Question

I have a MySQL 5.7 table which includes a column of type VARCHAR named 'area' which contains JSON data. For example ["BS20","BS21"]

I need to search on that table to find the first row containing a particular string. The string is contained in a variable like

$area = BS20;

and I have tried this for my query

SELECT * FROM aedv2_admin WHERE JSON_CONTAINS(area,'$area')=1

and I get an error

Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0.12

For testing I have tried hard coding argument 2 as BS20 and 'BS20' but neither helps. What am I doing wrong?

and I have tried this for my query Provide query text, not a part cut off the PHP code. — Akina
– Akina, Commented May 4, 2021 at 17:42
Use JSON_SEACRCH(). Or '$area' after the substitution must give '"BS20"'. dbfiddle.uk/… — Akina
– Akina, Commented May 4, 2021 at 17:49

TrapezeArtist · Accepted Answer · 2021-05-05 09:39:56Z

1

Phew! That was a bit painful, but with thanks to Akina my final code ended up like this

$area = '\"'.$_POST['area'].'\"';

$query_sponsor = "SELECT * FROM aedv2_admin WHERE JSON_CONTAINS(area,'$area')";

answered May 5, 2021 at 9:39

TrapezeArtist

9071 gold badge15 silver badges41 bronze badges

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

1 Answer 1