Laravelでjsonカラムの検索を行いたい

Question

前提

Laravelのwhereを使ってjsonの値を前方一致で絞り込んで取得したい

実現したいこと

testテーブル

idとhoge列があるとして

id  / hoge (型：json）
1   ['TTaaaa','TTbbb','TTbaaa']
2   ['TTvv','TTgg','TTrrb']
3   ['TTvv','TTgz','TTbr']

という上記のようなデータで
「TTg」
という文言で前方一致で検索し、sqlで言うdistinctのような形で

TTgg,
TTgz

というhogeのjson内の文字列を取得したいです

そこで以下のようにlike検索をかけました

$model->where('hogehoge', 'LIKE', '%"TTg'  );

そうすると当たり前に
2 ['TTvv','TTgg','TTrrb']
3 ['TTvv','TTgz','TTbr']

のhogeのjsonが取れてきます。
それを

$hoge = array_unique(Arr::flatten($hoge));

のようにして、

TTgg,
TTgz

の二文字を配列で取得することに成功しました。
しかしarray_uniqueなどを使わずにlaravelのクエリビルダだけで
一発で同じことを実現できたりしますでしょうか？
他にいい方法がないか知りたいです。

Answer 1

whereRaw で json の検索する以外に方法はないですよ

https://qiita.com/ukyooo/items/19da224866affa97a211

Comments

[ms5025]

ということはやはり上記と同じことしか方法がなさそうということでよかったですか？

[phper.k]

リンク先の記事読んでますかね？

[phper.k]

JSON_CONTAINS関数使って検索するのがセオリーです。
Laravel には JSON_COMTAINS をラップするメソッドはないのだから、whereRaw でSQLを組み立てるしかない。
ただし、JSON_CONTAINS では完全一致しか検索できないのだから、実現方法は、テーブル構造を用件に合わせて変更するしかないでしょう。
https://dev.mysql.com/doc/refman/8.0/ja/json-search-functions.html

[ms5025]

読みましたよ。その上でその回答をしています。
JSON_CONTAINSは完全一致しか無理でLIKE検索できなので上記の方法しかないのでは？ということを言ってるのですが
間違ってますか？

[ms5025]

また、こちらリンク先、MySQLなのですがDBはpostgresでも同じなのでしょうか？
（今回私が使用しているのはpostgresです）

[phper.k]

「同じ」とは？
SQLの話ですか？それともLaravelの書き方の意味ですか？
https://www.postgresql.jp/document/11/html/datatype-json.html

[ms5025]

JSON_CONTAINSってpostgresでも使えますか？
そもそもLIKE検索をしたいというのが先にある条件なので
JSON_CONTAINは使えないのではないですか？

[ms5025]

そうなると、
$model->where('hogehoge', 'LIKE', '%"TTg' );
として普通にjsonをテキストと見た目ててLIKE検索してから
arrayの中から適切な文言をとってくる以外の方法が自分は見当たらなかったということです。

これ以外のやり方がありますか？

[phper.k]

> 実現方法は、テーブル構造を用件に合わせて変更するしかないでしょう。

JSONカラムで定義することをやめる

[ms5025]

本当それすぎて、ぐうの音も出ないわけですが
自分だけのシステムではないため、テーブル構造は変更できません・・・。
それが一番なのですが。

[phper.k]

仕様でデッドロックがあるなら、そんなものは、政治判断するしかないでしょうが。
部分一致をやめて、完全一致検索にするか、テーブルを変更するか。
その２択しかないんですから、然るべき人に相談したら？

[ms5025]

ということで、
＞一発で同じことを実現できたりしますでしょうか？
という答えとしては
ないので、上記の方法しかないということでよかったでしょうか？

JSON_CONTAINSは完全一致しか無理でLIKE検索できなので上記の方法しかないのでは？ということを言ってるのですが

[phper.k]

Like ははなからありえないアプローチですので、問題外ですね。

[ms5025]

なるほど。
ではjsonの値 ['TTvv','TTgg','TTrrb']
を
TTvv
TTgg
TTrrb
のように三行でselectしてくる方法ってあるのでしょうか？
例えば、これができればLIKEも使えるのかと思ったのですが

[phper.k]

前提条件として、JSONの形式を厳密に定義されていないとできませんよね？
そこの説明をお願いします。

単純な配列形式だけでJSONは説明できませんよね？
KEY を含む可能性はあるのかとか
２次元配列のように階層は深くなる可能性はあるのかとか。

JSONデータの登録時に適切なバリデーションがされていて、想定外のデータが入ってくる可能性はないのかなど。

この辺の説明が不足しているので、回答としては、「Like 検索はあり得ない」よとしか回答できません。
JSONであれば、JSONとして扱える形式として想定できる形式全てを満たす回答をせざるを得ません。

[ms5025]

keyは含みません
例に挙げたように、（['TTvv','TTgg','TTrrb']）
一次元配列です

JSONデータのバリデーションとLike検索は何の関係もないと思います

[ms5025]

SELECT test.id, hoges.value hoge FROM test
CROSS JOIN LATERAL jsonb_array_elements (test.hoge) hoges

でjsonbのデータを行で取得することができました。

TTvv
TTgg
TTrrb

なのでこれに対してLike検索をかけようと思います。

[phper.k]

関係するから指摘したんですけどね
そう思うのでしたらどうぞどうぞお好きなように

やむを得ない事情があることを考慮したとしても、正規表現で実現するかな。
https://www.postgresql.jp/docs/9.2/functions-matching.html