如何在Laravel中编写CROSS APPLY OPENJSON查询？

背景说明 #

数据库opf_details表的column_name字段存储的JSON数据格式如下：

[
    {
        "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"
    }
]

需要转换的SQL查询：

原SQL语句 #

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

中文释义 #

从opf_details表中查询customername字段（别名为customerName），通过CROSS APPLY解析column_name字段的JSON数组，筛选出JSON对象中Name字段包含nilesh1的记录。

Laravel Eloquent实现写法 #

可以通过查询构造器结合原生SQL片段实现，以下是几种可行写法：

写法一：使用crossJoinSub模拟CROSS APPLY #

use Illuminate\Support\Facades\DB;

$results = DB::table('opf_details')
    ->select('customername as customerName')
    ->crossJoinSub(
        DB::raw("OPENJSON(column_name, '$')"),
        'json_data'
    )
    ->whereRaw("JSON_VALUE(json_data.value, '$.Name') LIKE ?", ['%nilesh1%'])
    ->get();

写法二：使用EXISTS子查询简化逻辑 #

use Illuminate\Support\Facades\DB;

$results = DB::table('opf_details')
    ->select('customername as customerName')
    ->whereRaw("EXISTS (
        SELECT 1 
        FROM OPENJSON(column_name, '$')
        WHERE JSON_VALUE(value, '$.Name') LIKE '%nilesh1%'
    )")
    ->get();

写法三：通过模型查询（若存在对应模型） #

假设已有对应的数据模型App\Models\OpfDetail，可直接用模型调用：

use App\Models\OpfDetail;

$results = OpfDetail::select('customername as customerName')
    ->whereRaw("EXISTS (
        SELECT 1 
        FROM OPENJSON(column_name, '$')
        WHERE JSON_VALUE(value, '$.Name') LIKE '%nilesh1%'
    )")
    ->get();

内容的提问来源于stack exchange，提问作者Developer Nilesh