Search word within JSON field

I am storing values in a JSON field in below format

{
   "1":{
      "value":"John"
   },
   "2":{
      "value":"Canada"
   },
   "3":{
      "value":[
         "Java",
         "Dot Net"
      ]
   },   
   "5":{
      "value":"Male"
   },
   "6":{
      "value":[
         "Sports",
         "Dancing"
      ]
   }
}

How can I search for a word "Male" or "Toronto" or use wild card operators with the query?

I tried but didnt worked.

Customer::whereRaw('JSON_CONTAINS(attributes->"$[*]", "male")')->get();
Customer::whereRaw('JSON_CONTAINS(attributes->"$[*].value", "male")')->get();

Below worked but needs the key to be dynamic or can use wildcard with values.

Customer::whereJsonContains('attributes->1->value"', 'John')->get();

aurawindsurfing

6 years ago

Level 50

Hi @mihirpatel83

Maybe it will be easier to convert that json to laravel object first and then work on it?

json_decode($your_json)

Hope it helps!

mihirpatel83

6 years ago

Level 1

@AURAWINDSURFING - That is not going to help as I have to search for Customer and list them out based on the searched string. so it should be the mysql query only that will help. Thank you for suggestion.

mihirpatel83

6 years ago

Level 1

Below query works at the moment as per my requirement. "attributes" is the name of the JSON field and I had numeric keys within it.

Customer::select(DB::raw('CONCAT(id,"_","Customer") AS iid'), DB::raw('attributes->>"$.\"1\".value" AS text'))->whereRaw('JSON_SEARCH(attributes, "all", "John") IS NOT NULL')->get();

Also, some of the query example that worked and might be helpful to someone

Customer::whereJsonContains('attributes->1->value', 'John')->get();

Customer::whereRaw('JSON_CONTAINS(attributes->"$.\"1\".value", \'"John"\')')->get();

Please or to participate in this conversation.