Laravel – 從資料庫讀取 Json 欄位資料 (第一次接觸 Json 欄位)

由於使用了 spatie/laravel-activitylog 套件做為測試實驗站台的紀錄功能，總不能每次要讀紀錄都跑去資料庫中找，於是做一個簡單的資料讀取功能，結果卻發現怪異的現象，平常正常情況下 Laravel 應該很容易讀取資料。

# 正常情況下讀取
ActivityEloquent::where('description ','create')->where('properties','like','%title%')->get();

結果這次卻發生怪異，怎撈不到資料，明明條件都正確，查看資料庫也確實都有兩個條件的資料，於是直接在資料庫下語法去找，結果…一樣沒資料…冏

# 輸入MySQL語法直接去資料庫查詢
SELECT * from activity_log where description = 'created' and properties like '%title%';

可是若拆開單獨各自尋找又很正常，都各自找的到各自的資料，只要放在一起就讀不到資料，於是就開始去尋找問題…

先看 activity_log 的 create_table, 其中 properties 是 json 類型.

<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateActivityLogTable extends Migration
{
    /**
     * Run the migrations.
     */
    public function up()
    {
        Schema::connection(config('activitylog.database_connection'))->create(config('activitylog.table_name'), function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('log_name')->nullable();
            $table->text('description');
            $table->nullableMorphs('subject', 'subject');
            $table->nullableMorphs('causer', 'causer');
            $table->json('properties')->nullable();
            $table->string('ip')->nullable();
            $table->timestamps();
            $table->index('log_name');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down()
    {
        Schema::connection(config('activitylog.database_connection'))->dropIfExists(config('activitylog.table_name'));
    }
}

然後去看 MariaDB 卻看到 properties 欄位寫的 類型 longtext 與編碼 utf8mb4_bin，這不是文字模式嗎?? 那怎會讀不到資料?? why?? 難道是編碼問題?? 於是乎將 utf8mb4_bin 直接改成 utf8_unicode_ci，結果…噹噹…竟然可以讀到資料…

可是感覺怪怪的，總不能每次重刷要建立 activity_log 資料表都還得手動去改欄位類型，於是用 Docker 開啟 MySQL 測試機去測試，MySQL 建立出來的類型是 json 無誤，一開始一度以為是 MariaDB 不支援?? 去 MariaDB 官方查確實有支援，欄位類型就是 longtext。

這時候反過來想，套件既然會選擇用 Json 方式一定有它的道理，不應該去亂改資料庫設定，於是開始嘗試去找語法如何讀取 Json 欄位資料，我還真的是第一次碰到Json欄位，完全丈二金剛摸不著頭緒要怎下語法，最後終於找到答案. 用JSON_EXTRACT 來處理 JSON 欄位的查找.

SELECT * FROM `activity_log` WHERE JSON_EXTRACT(`properties` , '$.attributes.title') like '%Q%'

透過這樣操作就可以延伸一起找出其他欄位資料，例如: 要找 description 欄位等於 created 且 properties 的 JSON資料 中 attributes 陣列裡 title 與 name 的 內容含有 Q 與 廣場的資料

SELECT * FROM `activity_log` WHERE description = 'created' and JSON_EXTRACT(`properties` , '$.attributes.title') like '%Q%' OR description = 'created' and JSON_EXTRACT(`properties` , '$.attributes.name') like '%廣場%'

Laravel 寫法

//查詢 description 與 properties 兩個欄位時，且要查詢 properties 的資料內有兩種 title 與 name 需要用 orWhere 來串聯
$logs = ActivityEloquent::where('description','created')
        ->where(function($query){
            $query->where('properties->attributes->title','like',"%Q%")
                ->orWhere('properties->attributes->name','like',"%廣場%");
        });