mariaDB JSON support in Laravel

Question

I'm trying to create a json database in XAMP, while using the phpmyAdmin it showed me that I'm using mariaDB but in my xamp-control panel v3.2.2 it shows running mySQL on port 3306. I'm using Laravel 5.4 framework to create the database, following is my migration which I'm trying to execute:

Schema::connection('newPortal')->create('pages', function (Blueprint $table){
    $table->increments('id');
    $table->string('title');
    $table->string('slug')->unique()->index();
    $table->json('styles')->nullable();
    $table->json('content')->nullable();
    $table->json('scripts')->nullable();
    $table->softDeletes();
    $table->timestamps();
});

Now while executing this I'm getting following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json null, content json null, scripts json null, deleted_at timestamp null' at line 1 (SQL: create table pages (id int unsigned not null auto_increment primary key, title varchar(191) not null, slug varchar(191) not null, styles json null, content json null, scripts json null, deleted_at timestamp null, created_at timestamp null, updated_at timestamp null) default character set utf8mb4 collate utf8mb4_unicode_ci)

Even if I keep not null it throws the same error. I want to have json formatted data, I checked the supported version and as per the documentation json format support started from the version MariaDB 10.0.16. and I'm using 10.1.21-MariaDB

Help me out in this.

Answer 1

Since MariaDB version 10.2.7; theJSON data-type is an alias for LONGTEXT.

If you are having issues with the JSON data-type in MariaDB, simply just change it to LONGTEXT. ;-)

Or add MariaDB JSON to Laravel with this package

Answer 2

Note that the 1064 complained about the datatype "json". Such is not (yet) implemented in MariaDB.

You can get close with Dynamic Columns, which at least has a way of fetching them into JSON syntax.

Another thing (probably what you are referring to) is CONNECT being able to have a JSON table type. (Not column type.)

MySQL 5.7 has a datatype called JSON, plus a bunch of functions to manipulate such.

Answer 3

Figured out a simple workaround (not recommended for production) -

As per mariadb version 10.1.32 and lower it seems like mariadb does not support json data type I am still unsure if it is available in version 10.2.7+.

but here's a simple workaround to get through this.

change json data type into text and then run your migration again.

(https://user-images.githubusercontent.com/27993070/41234555-19c5d1d8-6dbf-11e8-9a4b-0644b03aecfc.png)

source- https://github.com/laravel/framework/issues/13622

Answer 4

Add MariaDB JSON support to Laravel by running this command using composer:

composer require ybr-nx/laravel-mariadb

If you are using Larvel 5.3 and 5.4 do these two items:

• Include MariaDBServiceProvider in config/app.php by adding this line to providers:

'providers' => [
    // other exist providers
    YbrNX\MariaDB\MariaDBServiceProvider::class,
]

• Set default connection in database configuration to mariadb:

'defaultconnection' => [
    'driver' => 'mariadb',

Adding package is done and then you can use functionalities.

In Migrations:

$table->json('field') //CHECK (JSON_VALID(field))
$table->json('field')->nullable() //CHECK (field IS NULL OR JSON_VALID(field))

For Query builder:

$query->where('somejson->something->somethingelse', 2)
DB::table('sometable')->select('sometable.somedata', 'sometable.somejson->somedata as somejsondata')

Also, JSON_SET() works in MariaDB as in MySQL 5.7:

DB::table('sometable')->where('somejson->somedata', $id)->update(['somejson->otherdata' => 'newvalue']);

Note 1: MariaDB has an alias for JSON datatype since version 10.2.7

Note 2: There is bug in MariaDB < 10.2.8 JSON_EXTRACT() behaviour function. It's fixed in MariaDB 10.2.8

retrieved from