Use Postgres features like JSON functions in Laravel

Question

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\" "));

RaV · Accepted Answer · 2018-04-11 14:22:51Z

1

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

edited Apr 11, 2018 at 14:22

RaV

1,0481 gold badge9 silver badges25 bronze badges

answered Mar 13, 2017 at 6:04

Lee

3,1241 gold badge14 silver badges25 bronze badges

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

2 Comments

RaV Over a year ago

"Note: MariaDB does not have JSON columns, and PostgreSQL has JSON columns but this feature appears to not currently work on them. So consider this a MySQL 5.7+ feature for now." - according to your link

RaV Over a year ago

I have digged deeper, version 5.3 doesn't support Postgress, but apparently version 5.6 does.

tjbp · Accepted Answer · 2016-03-02 12:13:48Z

0

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.

answered Mar 2, 2016 at 12:13

tjbp

3,5573 gold badges26 silver badges35 bronze badges

Collectives™ on Stack Overflow

Use Postgres features like JSON functions in Laravel

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related