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

What is the CROSS APPLY OPENJSON query for the laravel?

I am using this query.

In database structure column_name into Json format like this :

[
    {
        "Date": "2022-11-14T10:45:05.424+01:00",
        "Name": "nilesh1",
        "Level": "1"
    },
    {
        "Date": "2022-11-14T10:54:15.776+01:00",
        "Name": "nilesh2",
        "Level": "2"
    }
]

I have to use below query with Laravel eloquent:

select [customername] as [customerName]
from [opf_details] 
CROSS APPLY OPENJSON (column_name,'$') where JSON_VALUE(value,'$.Name') LIKE '%nilesh1%'
Tags:

2

Answers


  1. 0

    you can use whereRaw() with the crossJoin()

    $results = DB::table('opf_details')
    ->select('customername as customerName')
    ->whereRaw("JSON_VALUE(column_name, '$.Name') LIKE '%nilesh1%'")
    ->crossJoin(DB::raw("OPENJSON(column_name, '$')"))
    ->get();
    1. crossJoin()
    2. whereRaw()

    Edit

    $customerName = DB::table('opf_details')
    ->selectRaw('customername as customerName')
    ->crossJoin(DB::raw("OPENJSON (column_name,'$')"))
    ->whereRaw("JSON_VALUE(value,'$.Name') LIKE '%nilesh1%'")
    ->get();
    Login or Signup to reply.
  2. 0

    I have done like this:

    myModel::whereRaw('JSON_CONTAINS(`column_name`, '{"Name":"nilesh2"}')')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search