Laravel JSONB型について - [´・[ェ]・] <もももっもっ

前提

ドキュメントを横断的に見ないとLaravelでDBのJSON/JSONB型に関するまとまった情報はない。なので、この記事を書いている。

DBに保存するならちゃんとカラムを設定するのが王道だが、ちょっとした小さなデータならJSON/JSONB型を検討しても良いとは思う。

PostgreSQLのドキュメントは、JSON/JSONB型について以下のように述べている。

8.14.2. JSONドキュメントの設計

JSONデータは従来のリレーショナルデータモデルよりもかなり柔軟に表現することができます。そのため、要件が変わりやすい環境では説得力があります。そして、それは同じアプリケーション内で、両方のアプローチが共存し相互に補完することが可能です。しかし、最大の柔軟性が要求されるアプリケーションのためでもJSONドキュメントには、まだいくらかの固定構造を持つことを推奨します。構造は（いくつかのビジネスルールを強制することは宣言的に可能であるが）、一般的に強制されないですが、テーブル内の「ドキュメント」（データ）セットをまとめて予測可能な構造にすることで、簡単に問い合わせを記述することができます。

JSONデータはテーブルに格納するとき、他のデータ型と同一の同時実行制御の対象となります。大きな文章を保存することは実行可能ですが、すべての更新が行レベルロックを取得することに留意してください。更新トランザクション間のロックの競合を減少させるために、管理可能なサイズにJSONドキュメントを制限することを検討してください。理想的には、JSONドキュメントはビジネス・ルール上、独立して変更することができない単位までデータを分割すべきです。

8.14. JSONデータ型

要約すると巨大なJSONをカラムに格納するより、意味が紐づいている小さなカラムに小分けにした方が効率が良いという事。

小規模でマシンリソースが豊富な環境なら多分気にならないだろうが、大規模であったりリソースが制限されている環境では気をつけたい。

この記事では、特記がない限りテストコードを書いて外部的に挙動を確認している。また、JSONB型で試しているので、JSON型の場合は挙動が違う場合がありうる。

基本

'カラム名->キー名'でアクセスする。

キー名はネスト可能。

$users = DB::table('users')
                ->whereJsonContains('options->languages', 'en')
                ->get();

更新も同様。

$affected = DB::table('users')
              ->where('id', 1)
              ->update(['options->enabled' => true]);

モデルの設定

$casts

Eloquent：ミューテタ／キャスト 9.x （翻訳中）Laravel

キャストなしだとモデルを新規登録する時とかにPHPの配列/連想配列ではなく、json_encodeでJSONに変換した文字列としてデータを入れる必要がある。

キャストを事前に設定しておくと配列/連想配列を格納可能。

use Illuminate\Database\Eloquent\Casts\AsArrayObject;

protected $casts = [
    'options' => AsArrayObject::class,
];

'options' => 'array'でも大体の場合は問題がないが、

AsArrayObjectにすると以下のような部分的な更新が行える。

$user = User::find(1);
$user->options['key'] = $value;

$fillable

Eloquentの準備 9.x （翻訳中）Laravel

/**
 * 複数代入可能な属性
 *
 * @var array
 */
protected $fillable = [
    'options->enabled',
];

上記のように指定した場合、キーは以下のように書かないといけない。

Model::create([

'options->enabled' =>

]);

下記のような書き方は反映されない

Model::create([

'options' => [

'enabled' =>

]

]);

下記のように書くとJSONを格納出来るが特定の構造を持つ事は出来ない。

protected $fillable = [
    'options',
];

下記の場合は、カラムの方が優先っぽい。ここら辺の挙動は詳しく調べてないと怖い目にあいそう。

protected $fillable = [
    'options',
    'options->enabled',
 ];

Model::create([

'options->enabled' => ['enabled'],

'options' => ['options'] //こちらが格納される

]);

whereによる検索

データベース：クエリビルダ 9.x （翻訳中）Laravel

'options->enabled'でキー名を指定出来るし、ネストも出来るが、メソッドによって挙動が変わる。

EloquentやQueryBuilderでのカラムとキー名の指定は->のみ有効で、SQLの演算子として->を使うかどうかはメソッドによって異なる。

メソッド名	機能
where	数値、文字列、真偽値を検索可能。 nullは明示的にnullを付けた場合とキーが存在しない場合のnullを区別しない。 'options->enabled'をSQL変換時に'options->>enabled'として扱う。キー名は->でネスト可能だが、最後のキーが自動的に->>になる。通常のwhereと同じでLIKE等使えるが、キーの中身がオブジェクトだった場合JSONに対してLIKEを使う事になるので注意。例) 以下のJSONを格納している状態で、where('column->key1', 'LIKE', 'key%')を使うと'{ 'key2' : false }'という文字列で曖昧検索を行う { 'key1' : { 'key2' : false } }
whereJsonContains	数値、文字列、真偽値、配列の中身を検索可能。 nullは明示的にnullと指定したものだけを検索できる。 whereとは違い、SQL変換時は最後まで->になる。また、値の存在チェックには専用の演算子(@>)が使用される。ソースコードを読む限り、PostgreSQLの場合は第二引数をjson_encode()で変換するので、キー名と値の完全一致を取得する場合はPHPの配列を使う必要がある。また、キー名の存在を確認する?演算子はこのメソッドでは使えない(JSONのWHEREは@>でハードコーディングされている) 例) 以下のJSONを格納している状態で、キーと値の完全一致を検索する場合はwhereJsonContains('column->key1', ['key2'=>false])とするとヒットする。'{"key2":false}'はオブジェクトではなく文字列扱いになるので(そういう文字列を検索したい場合でなければ)意図通りには動かない { 'key1' : { 'key2' : false } }

メソッド名

機能

where

数値、文字列、真偽値を検索可能。

nullは明示的にnullを付けた場合とキーが存在しない場合のnullを区別しない。

'options->enabled'をSQL変換時に'options->>enabled'として扱う。キー名は->でネスト可能だが、最後のキーが自動的に->>になる。

通常のwhereと同じでLIKE等使えるが、キーの中身がオブジェクトだった場合JSONに対してLIKEを使う事になるので注意。

例)

以下のJSONを格納している状態で、where('column->key1', 'LIKE', 'key%')を使うと'{ 'key2' : false }'という文字列で曖昧検索を行う

{

'key1' : { 'key2' : false }

}

whereJsonContains

数値、文字列、真偽値、配列の中身を検索可能。

nullは明示的にnullと指定したものだけを検索できる。

whereとは違い、SQL変換時は最後まで->になる。また、値の存在チェックには専用の演算子(@>)が使用される。

ソースコードを読む限り、PostgreSQLの場合は第二引数をjson_encode()で変換するので、キー名と値の完全一致を取得する場合はPHPの配列を使う必要がある。

また、キー名の存在を確認する?演算子はこのメソッドでは使えない(JSONのWHEREは@>でハードコーディングされている)

例)

以下のJSONを格納している状態で、キーと値の完全一致を検索する場合はwhereJsonContains('column->key1', ['key2'=>false])とするとヒットする。'{"key2":false}'はオブジェクトではなく文字列扱いになるので(そういう文字列を検索したい場合でなければ)意図通りには動かない

{

'key1' : { 'key2' : false }

}