php - Laravel 查询在 JSON 字段中选择列表条目

Question

我使用 Laravel v6.11.0 nova 将动态数据存储在 MySQL JSON 字段中v2.9.3 和 nova-flexible-content v0.1.13。存储的数据类似于:

[
  {
    "layout": "source",
    "key": "5ce8e0a877487fe5",
    "attributes": {
      "value": "342",
      "unit": "USD",
      "language": "en",
      "url": "http:\\/\\/google.com",
      "authority": "google.com",
      "entry_date": "2020-01-21",
      "date": "2019-12-21"
    }
  },
  {
    "layout": "source",
    "key": "a82393ce016e8c14",
    "attributes": {
      "value": "444",
      "unit": "USD",
      "language": "en",
      "entry_date": "2020-01-21",
      "url": "https:\\/\\/google.com",
      "authority": "TEST",
      "date": "2020-01-20"
    }
  }
]

我想知道是否可以构建一个 Laravel 查询来根据 authority 条目选择第二个条目？标准是:

• url 应包含 google.com AND
• authority 不应是 google.com

我找到了https://laravel.com/docs/5.8/queries#json-where-clauses ，但我正在努力将正确的查询放在一起。也许有人可以给我一些关于如何去做的指示？谢谢

Answer 1

可以引用这个discussion在 Laracast 上

$data = App\YourModel::whereRaw('JSON_CONTAINS(body->"$[*].attributes.url", "\"https://google.com\"")')
        ->orWhereRaw('JSON_CONTAINS(body->"$[*].id", "\"http://google.com\"")')
        ->whereRaw('not JSON_CONTAINS(body->"$[*].attributes.authority", "\"google.com\"")')
        ->get();

foreach ($data as $key => $row) {
    $bodyArr = json_decode($row->body);

    foreach ($bodyArr as $item) {

       if ((preg_match("/(http:\/\/www\.|https?:\/\/google.com)/i",$item->attributes->url)) && (strpos($item->attributes->authority, 'google.com') === false))  {
          dump($item);
          // YOUR CODE GOES HERE
       }
    }
}