Back

Eloquent JSON field, selecting attribute produce extra quote


Robert Girhiny posted 1 week ago

This code:

$translation = Translation::where('language_id', 2)
            ->whereNotNull('data->navigation_login')
            ->select('data->navigation_login as navigation_login')
            ->first()
            ->toArray();
dd($translation);

Produce this result: array(1) {["navigation_login"]=> string(7) ""Login"" }

The problem is the extra double quote arround the login string: ""Login""

How can I eliminate this?

If I run the aboe code without select:

$translation = Translation::where('language_id', 2)
            ->whereNotNull('data->navigation_login')
            ->first()
            ->toArray();
dd($translation);

No double quotes:

["data"]=>
  array(3) {
    ["navigation_login"]=>
    string(5) "Login"
    ["navigation_order"]=>
    string(5) "Order"
    ["navigation_registration"]=>
    string(7) "Sign up"
  }

Here is a snippet from the model:

...
class Translation extends Model {

    protected $casts = [
        'data' => 'array',
    ];
...

Here is a snippet from the schema:

...
$table->json('data')->nullable();
...

Here is the row from the database:

{"navigation_login": "Login", "navigation_order": "Order", "navigation_registration": "Sign up"}
Robert Girhiny replied 1 week ago

The solution:

MySQL 5.7.13 and later

Using the unquoting extraction operator ->>

->select('data->>navigation_login as navigation_login')

Or older mysql version 5.7 < MYSQL < 5.7.13

->select(\DB::raw("JSON_UNQUOTE(JSON_EXTRACT(data, '$.navigation_login')) as navigation_login"))

Sign in to participate in this thread!



We'd like to thank these amazing companies for supporting us