CODEWITHSUNDEEP
phpjsonpostgresqllaraveleloquent
Mahmood Kohansal
Mahmood Kohansal

Reputation: 1041

Use Postgres features like JSON functions in Laravel

Postgresql has some function and operations for using JSON data. As it describes in postgresql site, it's possible to save JSON data in a column and get JSON array element with -> operator. Everything is ok in plain php, but I want to use it on Laravel framework and specially with Eloquent or Query Builder.

Is there any standard solution for this functionality in Laravel? I developed plain sql like bellow code and it works but I'm looking for a better solution.

$host = env('DB_HOST', 'localhost');
$database = env('DB_DATABASE', 'aaa');
$user = env('DB_USERNAME', '22222');
$pass = env('DB_PASSWORD', '11111');
$table = 'table';
$column = 'json';
$connection = pg_connect("host=$host dbname=$database user=$user password=$pass");
$result = pg_fetch_all(pg_query($connection, "SELECT $column->>'name' FROM \"$table\" "));

Upvotes: 1

Views: 2948

Answers (2)

Lee
Lee

Reputation: 3104

As of Laravel 5.3, this is possible, but only for MySQL. Version 5.6 supports also Postgress. Assume you have the following user with profile as JSON datatype:

    "id": 1,
    "name": "Mahmood",
    "profile": {
        "bio": "Lorem ipsum",
        "phone": "1234"
    }

You can query as such:

$phone = DB::table('users')
    ->where('profile->phone', '1234')
    ->get();

Reference: https://mattstauffer.co/blog/new-json-column-where-and-update-syntax-in-laravel-5-3

Upvotes: 1

tjbp
tjbp

Reputation: 3517

This isn't currently supported in Laravel. You can see SQL grammar unique to Postgresql support in Laravel here.

You can however use raw statements with Eloquent, which should handle what you need.

Upvotes: 0

Related Questions