October CMS resources and help articles

Simple and to the point. Optimized by the community.

Filter `jsonable` fields using Eloquent

5
by OFFLINE, last modified on August 12th, 2019

Fields that are declared as jsonable are stored as JSON string in a single database column.

If you declare this column as JSON type in your database you can use Laravel's JSON where clauses to query the structured data.

You need to be using a database that supports JSON columns (MySQL 5.7+ or MariaDB 10.2+).

// In your migration
Schema::create('your_table', function (Blueprint $table) {
    $table->increments('id');
    $table->json('jsonable_field'); // Declare as JSON
    // ...
});

Basic queries are supported on Laravel 5.5.

$users = DB::table('users')
           ->where('preferences->dining->meal', 'salad')
           ->get();

If you need advanced JSON features you have to fall back to raw queries.

// Filter data like {"subkey": [10]}
$users = DB::table('users')
           ->whereRaw('JSON_EXTRACT(array_of_values, ?) >= ?', ['$."subkey"[0]'])
           ->get();

// Order by JSON value like {"value_name": "abc"}
$users = DB::table('users')
           ->orderByRaw('JSON_EXTRACT(field_name, ?) ASC', ['$."value_name"'])
           ->get();

Discussion

0 comments

We use cookies to measure the performance of this website. Do you want to accept these cookies?