Framework: [Proposal] Schemaless JSON fields for Eloquent

[raphaelsaunier]

The Problem

Consider the following scenario. You need to store semi-structured data for one or more attributes of an Eloquent model. Since these attributes are only relevant to a subset of the rows in your database, you don’t want to create a new field for every single one of those attributes in your table.

To illustrate the problem, let's imagine we have a simple database table that stores business cards:

  first_name
  last_name
  email

If we wanted to have the same flexibility as a format like vCard, which has a multitude of fields and even allows for custom fields, we would have to create a new column for each new field:

  first_name
  last_name
  email
  x_gender
  x_twitter
  ...

These requirements would make a schemaless solution like MongoDB ideal. However, in the context of a Laravel application with existing models and in which the data is primarily relational, this isn't a viable solution.

While it is already possible to use a TEXT field and manually serialize data to JSON when saving to it (and then deserialize it when reading from it), this approach has two shortcomings:

1. It is inefficient and inelegant
2. It doesn't use the native json datatype in PostgreSQL and therefore it cannot be indexed

The Solution

The proposed solution consists in adding new methods to interact with JSON fields to Eloquent.

There are at least three aspects to consider to make this work:

1) The schema builder would need a new json field type. This field would be turned into a json datatype when using PostgreSQL and for all other databases it would simply become a TEXT field:

$table->json('social_profiles');

2) There should be a way of defining the list of JSON fields in the model (in the same way as getDates()):

getJsonFields() {
  return array('social_profiles');
}

3) Lastly and most importantly, these fields would need to provide a set of methods to interact with their underlying JSON object:

$business_card->social_profiles->set('twitter', 'laravelPHP');
// or
$business_card->social_profiles->twitter = 'laravelPHP';

$business_card->social_profiles->get('twitter');
// or
$business_card->social_profiles->twitter; // => 'laravelPHP'

$business_card->social_profiles->has('xing'); // => FALSE

The JSON field, in this case social_profiles, would be deserialized on demand when it is first accessed and serialized – if it was changed – when the model is saved.

---

I’d be interested in hearing your thoughts on the suggested implementation. Also, if you know of any similar implementations in other frameworks, I'd be happy to hear about them.

[lucasmichot]

@raphaelsaunier , depending of your DBMS, having a social TEXT field and adding the following to your model should solve this case.

/**
 * Social Attribute Accessor
 *
 * @param string $value The JSON string stored in the social field of the table.
 *
 * @return array An array containing the social profiles
 */
public function getSocialAttribute($value)
{
    return json_decode($value, true);
}

/**
 * Social Attribute Mutator
 *
 * @param array $value The array containing the social profiles
 */
public function setSocialAttribute($value)
{
    $this->attributes['social'] = json_encode($value);
}

[raphaelsaunier]

Yup, that's what I initially tried. While I was able to get it to work, here's what I meant when I said that it was inelegant and inefficient:

With the accessor, you have the problem that the output isn't cached. This means that if you want to retrieve multiple fields embedded in the same attribute ($user->social->facebook, $user->social->twitter, …), getSocialAttribute() will be called multiple times. The workaround is simple, you could simply store the output in a variable before passing it to the view but as you can imagine, this gets cumbersome when dealing with Eloquent collections.

The other shortcoming is related to how you deal with missing fields. Laravel's array_get helper comes in very handy but you still end up adding more logic than necessary to your views or controllers.

With the mutator you have a similar problem; rather than being able to just write:

$user->social->facebook = 1234

You would have to write something like this:

$social = $user->social;
$user->social = array_set($social, 'facebook', 1234);

[stayallive]

@raphaelsaunier This! :+1:

I would love to see his! It makes sense and this is very usefull for everyone!

[RSully]

:+1: Definitely interested in this as someone using pgsql.

[marcvdm]

You could do something like this

class BaseModel extends Eloquent {

    protected $cacheable = array('social');

    protected $cachedAttributes = array();

    protected function getAttributeValue($key)
    {
        if ($this->isCacheable($key) AND $this->hasCachedAttribute($key))
        {
            return $this->cachedAttributes[$key];
        }

        $value = parent::getAttributeValue($key);

        if ($this->isCacheable($key))
        {
            $this->cachedAttributes[$key] = $value;
        }

        return $value;
    }

    public function hasCachedAttribute($key)
    {
        return array_key_exists($key,$this->cachedAttributes);
    }

    public function isCacheable($key)
    {
        return ( (array)$this->cacheable == array("*") or in_array($key,(array)$this->cacheable) );
    }

}

Then make your models extend the BaseModel and implement the accesors

[taylorotwell]

As a Postgres user I do find better ways of working with JSON and Array type fields interesting, since I do it all manually now. I'll give some more thought to it and hopefully we can get something cool in place.

[vespakoen]

I was doing it manually for HSTORE in a Laravel 3 application, I used, and you should definitely check out:

https://github.com/chanmix51/Pomm/tree/master/Pomm/Converter

[Anahkiasen]

+1 for this

[anlutro]

Maybe a protected $jsonFields array similar to $dates?

[taylorotwell]

Closing this as no action planned for this in near future.

[hooch]

Is this something that might become possible with Laravel 4.3?

[younes0]

JSON type is a very useful feature not to mention JsonB (binary) coming in 9.4.

I made a similar @marcvdm's implementation, but I encounter another pitfall:
trying $eloquent::firstOrCreate(['json_field', json_encode($array)]) will results in error:

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. (SQL: select count(*) as aggregate fr
  om "mytable" where "json_field" = {"mykey":121}
  )

Since firstOrCreate uses Query Builder, comparing json values would require this :

$fluent->whereRaw(' "json_field"::text = \'{"mykey":121}\'  ')

[piotr-cz]

My BaseModel class, I think it should be possible to prepare a JsonAttributesTrait

abstract class BaseModel extends Eloquent {

    /**
     * List of JSON attribtues
     *
     * @var  array
     */
    protected $jsonAttributes = [];

    /**
     * JSON wrapper
     *
     * {@inheritdoc}
     */
    protected function getAttributeValue($key)
    {
        $value = parent::getAttributeValue($key);

        // Get as JSON if possible
        if (in_array($key, $this->jsonAttributes))
        {
            return $this->getJsonAttribute($value);
        }

        return $value;
    }

    /**
     * JSON wrapper
     *
     * {@inheritdoc}
     */
    public function setAttribute($key, $value)
    {
        // Set as JSON if possible
        if (in_array($key, $this->jsonAttributes))
        {
            return $this->setJsonAttribute($key, $value);
        }

        return parent::setAttribute($key, $value);
    }

/// JSON Accessor and Mutator

    /**
     * Decode
     *
     * @param  mixed  $value
     * @return  mixed  Decoded value
     */
    protected function getJsonAttribute($value)
    {
        return ($value === null) ? null : json_decode($value);
    }

    /**
     * Encode
     *
     * @param   string  $key
     * @param   boolean|integer|string|array|stdClass|JsonSerializable  $value
     * @return  Model instance
     */
    protected function setJsonAttribute($key, $value)
    {
        $this->attributes[$key] = ($value === null) ? null : json_encode($value);

        return $this;
    }
}

[piotr-cz]

There's a problem with my solution: assigned data are read-only.

Explaination here and here

// Problem: using Array
$order->products = [];
$order->products[] = 'foo'; // ErrorException: Indirect modification of overloaded property Order::$products has no effect.

// Problem: using Object
$order->product = new \stdClass;
$order->product->foo = 'bar';
dd($order->product); // object (stdClass)

// Workaround
$product = new \stdClass;
$product->foo = 'bar';
$order->product = $product;
dd($order->product) // object(stdClass) public 'foo' => string 'bar'

[Ralla]

See https://github.com/darrylkuhn/dialect

[adrianb93]

Unfortunately darrylkuhn/dialect conflicts with some other packages I use due to what I presume is the altered Eloquent results.

It'd be ideal if querying JSON fields was possible with Eloquent out of the box, my short lived use of darrylkuhn/dialect did drastically simplify my code, it's a shame it conflicts too much with the other needs of my application.

I hope this feature is considered in the near future for Laravel 5.3 onwards.

[raphaelsaunier]

@AdrianB93 do you have any specific examples of packages that are conflicting with dialect?

[adrianb93]

@raphaelsaunier, VentureCraft/revisionable is the most notable example, there were other conflicts but I don't remember which ones they were. I ended up choosing to make an accessor in my model for some JSON field values using json_decode and placing it in a Collection.

[giannoulakis]

You can already use JSON fields, casting them as an array. Eloquent does json_encode/json_decode for you.
https://laravel.com/docs/master/eloquent-mutators#attribute-casting

[Anahkiasen]

You can already use JSON fields, casting them as an array. Eloquent does json_encode/json_decode for you.

That wasn't a feature yet when this issue was opened though :p

[thosewords]

:+1