MYSQL Query on JSON for specific KEY:VALUE

Question

Im trying to get a matching record within a json. Its only ever once within the json with that specific key and it looks like this.

{"bannerpage":["569"],"menulinkage":"569","clickurl":false,}

Im trying to find the matching value of 569 with key as bannerpage. However there's no luck. Just wont play nice.

Here is what i have:

$sql->select("* FROM #__banners WHERE state=1 AND params LIKE '%"bannerpage":["$menu"]%'");

Using the following snippet works but it checks both bannerpage and menulinkage - i only want the value of bannerpage.:

AND params LIKE '%{$menu}%'

I really hope that make sense.

To break it down even simpler:

How do i write a mysql query where i check for a matching number against this json value:

"bannerpage":["569"]

Thanks to anyone for your help Jonny

@Shri i worked it out lol. LIKE '%\"bannerpage\":[\"$menu\"]%' I had to escape the inverted commas. The query was breaking because of that. — jonnypixel
– jonnypixel, Commented Nov 11, 2014 at 9:36

jonnypixel · Accepted Answer · 2014-11-11 10:12:16Z

1

I dont normally do this but in this case i will just answer it.

I needed to escape the inverted commas like so. Once i did that the mysql query worked fine.

LIKE '%\"bannerpage\":[\"$menu\"]%'

answered Nov 11, 2014 at 10:12

jonnypixel

3276 silver badges27 bronze badges

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

1 Answer 1