Question 246172 in Laravel Question posted in
The official Laravel Documentation can be found here.

Issue querying a json value in laravel project

I’m trying to fetch a record from MSSQL table based on field value which contains json data. Following is the sample column value for ‘updated_value’ column:

[[{"phone_number":"5555555555","phone_type":"H","inactive_date":null,"restrictions":["SU","F"],"start_time":null,"end_time":null}]]

My query is:

$existing = ContactChangeLogs::latest()
        ->where('updated_value->phone_number', '5555555555')
        ->first();

But dd($existing) gives null result.

Tags:

3

Answers


  1. 0

    You can use whereJsonContains() instead of where().

    $existing = ContactChangeLogs::latest()
        ->whereJsonContains('updated_value->phone_number', '5555555555')
        ->first();
    Login or Signup to reply.
  2. 0

    A possible approach is to use whereRaw() method to build a raw WHERE clause for your query. You need two OPENJSON() calls to parse the nested JSON content:

    ->whereRaw(
   "EXISTS (
      SELECT 1 
      FROM OPENJSON(updated_value) j1 
      CROSS APPLY OPENJSON(j1.[value]) j2 
      WHERE JSON_VALUE(j2.[value], '$.phone_number') = ?
    )",
    ['5555555555']
)

    As an additional note, if the JSON content has a fixed structure (two nested arrays, the second one with a single item), you may simplify the statement:

    ->whereRaw(
   "JSON_VALUE(updated_value, '$[0][0].phone_number') = ?",
    ['5555555555']
)
    Login or Signup to reply.
  3. 0

    You can use the JSON_VALUE function to extract the ‘phone_number’ value from the JSON data

    $existing = DB::select(
    "SELECT TOP 1 * 
       FROM contact_change_logs 
      WHERE JSON_VALUE(updated_value, '$[0].phone_number') = '5555555555'"
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search