1

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

Muhammad Bilal
  • 497
  • 1
  • 5
  • 12
nasseer
  • 13
  • 3

2 Answers2

2

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

JureW
  • 641
  • 1
  • 6
  • 15
  • I tried the proposed solution but I get the error (Syntax error, unexpected '$') when I use $query ="SELECT * FROM Table_Name WHERE JSON_EXTRACT(,"$.town") LIKE '%something%' order by id DESC LIMIT 5"; I think it's wrong because of Quotations marks The error will be due to the MariaDB server version? – nasseer Jul 06 '19 at 14:21
  • What version of MariaDB do you have? And also, use single quotes in query, not double ones so instead of this: $query ="SELECT * FROM Table_Name WHERE JSON_EXTRACT(,"$.town") LIKE '%something%' order by id DESC LIMIT 5"; use this: $query ="SELECT * FROM Table_Name WHERE JSON_EXTRACT(,'$.town') LIKE '%something%' order by id DESC LIMIT 5"; – JureW Jul 06 '19 at 14:22
  • Sorry for the delay? Your answer is correct but working on the MySql version 5.7, is there a syntax working on the MySql version 5.1 ?Thanks for everything – nasseer Jul 13 '19 at 20:06
  • I dont think it is possible to use JSON functions in mysql 5.1... JSON fields were introduced to MySQL in 5.7, so previous versions cant use JSON functions. Maybe you could use something like: SELECT * FROM tabel WHERE replace(colom_name , ' ','') LIKE '%"town":"Word"%' order by id DESC"; With replace() you remove all spaces and than you do a normal LIKE, where you actually input JSON like key:value and search for that. I hope this helps. – JureW Jul 15 '19 at 06:57
-1

You dont have to use JSON_CONTAINS function.
In the documentation:

JSON_CONTAINS() function tests whether or not a specified value is found in the given JSON document or, optionally, at the specified path within the document

You do not have a json document, but a normal column.
Your SQL query should be:

$query ="SELECT * FROM tabel WHERE town like '%$Word%' order by id DESC";
Giacomo M
  • 4,450
  • 7
  • 28
  • 57