Using Laravel 6.x eloquent (or similar method) to get row by JSON column data-CodePudding

I have the following Laravel 6.x collection output when I run first() on that particular Model from a table called workflow_conflicts. As you can see the conflicts column is a JSON column and has data stored in this manner below.

Models\WorkflowConflict {
     id: 2190,
     created_at: "2022-02-10 12:18:28",
     updated_at: "2022-02-10 12:22:36",
     action: "approved",
     conflicts: "[{"text": "validatorword 1218" "word": "validatorword", "indicies": {"end": 13, "start": 0}, "workflow_id": 4334}]",
     user: Models\User {
       id: 17843,
       suspended: 0,
     },
   }

I am trying to figure out how would I use the Laravel Eloquent (I am using Tinker) how would I perform a search on the conflicts json values such as the values from text and word.

For instance - the following below would return the object fine in Tinker, but I am unsure how to perform a similar search on the conflicts row which is JSON data.

WorkflowConflict::where('action', 'approved')->first();

-- Expected result from within Tinker --

WorkflowConflict::where('conflicts->text', 'validatorword 1218')->first();
=> Models\WorkflowConflict {
     id: 2190,
     created_at: "2022-02-10 12:18:28",
     updated_at: "2022-02-10 12:22:36",
     action: "approved",
     conflicts: "[{"text": "validatorword 1218" "word": "validatorword", "indicies": {"end": 13, "start": 0}, "workflow_id": 4334}]",
     user: Models\User {
       id: 17843,
       suspended: 0,
     },
   }

-- Actual result from within Tinker --

WorkflowConflict::where('conflicts->text', 'validatorword 1218')->first();
=> null

TLDR - trying to search using the JSON column values in Laravel

CodePudding user response：

Since your data is wrapped by an array, you could use a whereRaw:

WorkflowConflict::whereRaw('json_contains(conflicts, json_object("text", "validatorword 1218"))')->first();