Search column of JSON data in PostgreSQL table

Question

I need to store some JSON data in PostgreSQL table to use it as dynamic route generator. The migration is simple:

Schema::create($this->tablename, function (Blueprint $table)
{
    $table->increments('id');
    $table->string("uri", 123);
    $table->json("middleware")->nullable();
    $table->string("as", 123)->nullable();
}

I store the data this way:

$a = new Route();
$a->uri = "/test1";
$a->middleware=json_encode(["auth","web"]);
$a->as = "TestController@test";
$a->save();

So let's say that I need to filter all the routes that have auth middleware. How can I do it?

When I try

Route::where('middleware', 'AS', 'auth')->get();

...I get an error. Is it possible to use it like that?

I use Laravel 5.2 and PostgreSQL 9.3.12.

Edit

If you are using PostgreSQL 9.4 or later and have Laravel framework with version bigger than 5.2.29 you can use this syntax:

Route::where('middleware','@>','"auth"')->get();

WhSol · Accepted Answer · 2016-04-18 20:57:59Z

1

Change where to whereRaw and query for json field

Route::whereRaw("middleware->> 'auth'")->get();

Or

Route::whereRaw("middleware::json->> 'auth'")->get();

answered Apr 18, 2016 at 20:57

WhSol

1163 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

melanholly Over a year ago

Ok I got an error Datatype mismatch: 7 ERROR: argument of WHERE must be type boolean, not type text if I cast the output to boolean it works just fine Route::whereRaw("(middleware->>'auth')::boolean")->get();

melanholly Over a year ago

Actually I you can use this syntax: Route::where('middleware','@>','"auth"')->get(); since laravel this commit

Collectives™ on Stack Overflow

Search column of JSON data in PostgreSQL table

Edit

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related