Issue querying a json value in laravel project

by

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.

  • the updated_value is an array of array so try to do updated_value[0]->phone_number

    – 

  • [SQL Server]Invalid column name ‘updated_value[0]’. (SQL: select top 1 * from [contact_change_logs] where json_value([updated_value[0]]], ‘$.”phone_number”‘) = 5555555555 order by [created_at] desc) – Getting this error @MAHAOUEGHLANI

    – 

  • @Zhorov Can you help me out writing the said raw query to my code? ->whereRaw(“WHERE EXISTS (SELECT 1 FROM OPENJSON(updated_value) j1 CROSS APPLY OPENJSON(j1.[value]) j2 WHERE JSON_VALUE(j2.[value], ‘$.phone_number’) = ‘5555555555’”) I know this is wrong

    – 




  • 1

    @Geethu, I think it is something like this: ->whereRaw("EXISTS (SELECT 1 FROM OPENJSON(updated_value) j1 CROSS APPLY OPENJSON(j1.[value]) j2 WHERE JSON_VALUE(j2.[value], '$.phone_number') = ?)", ['5555555555']).

    – 




  • @Zhorov Thank you It worked. Can you add this as a new comment. so I can accept this as answer

    – 

Leave a Comment