在mysql json列中搜索[重复]

Question

This question already has an answer here:

• How to search SQL column containing JSON array 1 answer

I have a column contain json data,

{
"city":"\u0628\u063a\u062f\u0627\u062f",
"town":"\u0627\u0644\u0627\u0639\u0638\u0645\u064a\u0629",
"queue":"316",
"lane":"22",
"home":"15"
}

how can I search on the object (town) in the table? I have tried

$query ="SELECT * FROM tabel WHERE JSON_CONTAINS(colom_name,'town') like  '%$Word%'  order by id DESC";

Also There are no results at all

</div>

Answer 1

We are using JSON fields in one of our projects, and for example, we have everything related to a user in one column, called simply JSON. So in order to search easier for a specific user, we are using JSON_EXTRACT. So for your example, you could do it like:

SELECT * FROM Table_Name WHERE JSON_EXTRACT(<json_column>,'$.town') LIKE '%something%'

You could even get town name, or city, by doing it like this:

SELECT JSON_EXTRACT(<json_column>,'$.<filed_from_json>') FROM Table_Name WHERE JSON_EXTRACT(<json_column>,'$.town') LIKE '%something%'

BR