[Feature request/package idea?] JSON attribute casting

[sneakylenny]

The problem

I would like to be able to cast JSON attributes to data types on the database side. The reason is, when I want to sort by a JSON attribute that happens to be an int it wouldn't sort alphabetically but numerically. Which makes sense since there is no way SQL knows it's an integer because JSON doen't have defined data-types.

I really like the feature where we can query things in json columns like

Item::where('json->prop->anotherProp', true)

But as stated before, when I this:

Item::orderBy('json->points->to->int')

the rows get sorted by the attribute alphabetically not numerically. However there is a way to solve this.
Normally when running the line above it would send a query like this:

select * from `items` where 1 order by json_unquote(json_extract(`json`, '$."points"."to"."int"')) asc;

But if the value would be cast like this:

select * from `items` where 1 order by cast(json_unquote(json_extract(`json`, '$."points"."to"."int"')) as unsigned) asc;

it would be sorted based on the casted type: unsigned.

My workaround

Reason

Since I actually needed this feature I've tried to implement it myself.
You would ask: "But why not use collections and use ->orderBy()?"
Well, I've tried but it would make navigating pages in my server rendered table take about 2 seconds. Which is unacceptable, Using SQL is simply WAY faster:

What I did

1. I created a model named ModelWithJson by calling php artisan make:model ModelWithJson

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

abstract class ModelWithJson extends Model
{
   // ...
}

2. I created a trait named HasJsonColumns by calling php artisan make:model \App\Concerns\HasJsonColumns (correct me if it should be \App\Traits\... instead). This contains all the logic for obtaining a json attribute as column and cast it into a datatype if needed.

App\Concerns\HasJsonColumns

namespace App\Concerns;

use Illuminate\Contracts\Database\Eloquent\Builder;

trait HasJsonColumns
{
   /**
    * Define what columns contain json.
    *
    * @var array
    */
   protected $jsonColumns = [];

   /**
    * Define the SQL cast datatypes of json properties.
    *
    * @var array
    */
   protected $jsonCasts = [];

   /**
    * Define the default cast if intended cast was not found. (Default: character)
    *
    * @var string char|(unsigned|signed) ?int|dec (and prob more, but I couldn't find a doc where it's clearly described)
    */
   protected $defaultJsonCast = 'char';

   /**
    * A scope to include and cast a json attribute as a column
    *
    * @param Illuminate\Database\Eloquent\Builder $query
    * @param string $attribute
    * @param string|null $name
    * @return Illuminate\Database\Eloquent\Builder
    */
   public function scopeIncludeJson(Builder $query, $attribute, $name = null)
   {
       $attribute = str_replace('->', '.', $attribute);
       $path = explode('.', $attribute);
       // Skip if column isn't JSON. I could probably make use of $casts 
       // by checking if x's cast is 'array'. But I wasn't sure if that's the
       // right way, so let's leave it like this for now.
       if (in_array($path[0], $this->jsonColumns)) {
           $jsonSelector = '$.' . implode(".", array_slice($path, 1));
           $cast = $this->jsonCasts[$attribute] ?? $this->defaultJsonCast;
           return $query->selectRaw("cast(json_value(`$path[0]`, '$jsonSelector') as $cast) as `". (!empty($name) ? $name : $attribute) . "`");
       }
       return $query;
   }

}

3. Added "HasJsonColumns" trait to "ModelWithJson" model

namespace App\Models;

use App\Concerns\HasJsonColumns;
use Illuminate\Database\Eloquent\Model;

abstract class ModelWithJson extends Model
{
    use HasJsonColumns;
}

4. I extended the models that have json columns from this class.

namespace App\Models\SubFolder

use App\Models\ModelWithJson;

class ExampleUserModel extends ModelWithJson
{
    // ...
    /**
     * @inheritdoc
     */
    protected $jsonColumns = [
        'settings',
        'statistics',
    ];

    /**
     * @inheritdoc
     */
    public $jsonCasts = [
        'settings.max_notifications' => 'unsigned int',
        'statistics.data.used' => 'float',
        'statistics.posts.liked' => 'unsigned int',
        'statistics.reputation' => 'signed int',
    ];
    // ...
}

The result:

With all this in place I can now include these columns and sort by them without having to import all records or add more columns.
My simplified example:

$jsonProp = 'statistics.data.used';
$items = Model::includeJson($jsonProp)
    ->orderByRaw("`$jsonProp` desc")
    ->skip($pageIndex - 1) * $pageSize)
    ->take($pageSize)
    ->get();

The data will now be ordered descending based on the prop inside the json:

I hope someone who also has this problem stumbles across this discussion, if so, I hope this helped! :)
The best case is of course if Laravel would have a similar feature built in.