如何用MySQL查询与Laravel Eloquent检索JSON数组列中符合条件的记录？

目标JSON结构 #

tours列存储的JSON数据结构如下：

[
  {
    "widgets": [
      {
        "name": "Calendar",
        "uuid": "db7308b5-ee0a-46d2-80bb-63dcb57f1152",
        "value": "2023-12-23",
        "system_name": "Calendar"
      },
      {
        "name": "Number",
        "uuid": "7cbdf159-7368-4db8-83e0-775cdd223131",
        "value": 3
      },
      {
        "name": "Number",
        "uuid": "c8caa756-8563-4811-ac86-9dc0860832b4",
        "value": 0
      }
    ]
  }
]

需求 #

检索tours字段中，存在widgets数组元素满足name="Calendar"且value介于2023-12-01和2023-12-31之间的记录。

原生SQL查询示例 #

MySQL（5.7+） #

利用JSON_TABLE展开JSON数组，筛选符合条件的记录：

SELECT *
FROM your_table_name
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        tours,
        '$[*].widgets[*]' COLUMNS(
            name VARCHAR(255) PATH '$.name',
            value DATE PATH '$.value'
        )
    ) AS jt
    WHERE jt.name = 'Calendar'
      AND jt.value BETWEEN '2023-12-01' AND '2023-12-31'
);

PostgreSQL（9.4+） #

使用jsonb_array_elements展开数组，结合条件筛选：

SELECT DISTINCT t.*
FROM your_table_name t,
     jsonb_array_elements(t.tours::jsonb) AS tour,
     jsonb_array_elements(tour->'widgets') AS widget
WHERE (widget->>'name') = 'Calendar'
  AND (widget->>'value')::DATE BETWEEN '2023-12-01' AND '2023-12-31';

Laravel Eloquent查询构造器示例 #

MySQL适配写法 #

use Illuminate\Support\Facades\DB;

$records = YourModel::whereExists(function ($query) {
    $query->select(DB::raw(1))
          ->fromRaw(
              "JSON_TABLE(tours, '$[*].widgets[*]' COLUMNS(name VARCHAR(255) PATH '$.name', value DATE PATH '$.value')) AS jt"
          )
          ->where('jt.name', 'Calendar')
          ->whereBetween('jt.value', ['2023-12-01', '2023-12-31']);
})->get();

PostgreSQL适配写法 #

$records = YourModel::select('*')
    ->crossJoin(DB::raw("jsonb_array_elements(tours::jsonb) AS tour"))
    ->crossJoin(DB::raw("jsonb_array_elements(tour->'widgets') AS widget"))
    ->whereRaw("(widget->>'name') = 'Calendar'")
    ->whereRaw("(widget->>'value')::DATE BETWEEN '2023-12-01' AND '2023-12-31'")
    ->distinct()
    ->get();

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