How To check If mysql DB column has json array of object and contains specific string

Question

I have saved the json data in mysql database column which looks like below

[{"one":"mg road plot number 711 xyz"},{"two":"dc road plot number 711 xyz"}]

json data like above is being stored for all the records with different address. So what I am trying to do is , I want to search all the customer who are having address of mg road.

So I have tried JSON_SEARCH and JSON_CONTAINS but it always returning me no records found even though there are 2 records in a table. So where I am going wrong.

SELECT * FROM `tbl_clients_data` WHERE JSON_SEARCH(reci_address,'all', "mg road")


SELECT * FROM `tbl_clients_data` WHERE JSON_CONTAINS(reci_address, 'mg road', '$.one')

Prabhjot Singh Kainth · Accepted Answer · 2019-12-23 10:48:45Z

1

Just try this code:

SELECT * FROM `tbl_clients_data` WHERE JSON_EXTRACT(reci_address, '$[*].one') 
LIKE 'mg road%' OR JSON_EXTRACT(reci_address, '$[*].two') LIKE 'mg road%'

JSON_EXTRACT Extracts data from a JSON document. The extracted data is selected from the parts matching the path arguments. Returns all matched values; either as a single matched value, or, if the arguments could return multiple values, a result autowrapped as an array in the matching order.

edited Dec 23, 2019 at 10:48

answered Dec 23, 2019 at 8:52

Prabhjot Singh Kainth

1,8712 gold badges19 silver badges27 bronze badges

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

4 Comments

user10384418 Over a year ago

i just tried it returns 0 rows and shows error like ===Warning: #4038 Syntax error in JSON text in argument 1 to function 'json_extract' at position 31

user10384418 Over a year ago

it;s still the same with 0 rows and same warning/error

Prabhjot Singh Kainth Over a year ago

The error should have not been there , here is a reference to that error stackoverflow.com/questions/35735454/…

user10384418 Over a year ago

thank you for the link..but still its giving empty result,,

Collectives™ on Stack Overflow

How To check If mysql DB column has json array of object and contains specific string

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related