[Proposal] JSON support for MariaDB (and maybe SQLite)

[ybr-nx]

I've brought MariaDB JSON support to Laravel with my package
https://github.com/ybr-nx/laravel-mariadb

As JSON_EXTRACT() is standard function and it works also on MySQL 5.7, then I think it would be better to use that function instead of field->field->field alias.

Changes are quite simple:
https://github.com/ybr-nx/laravel-mariadb/blob/master/src/QueryGrammar.php
wrap() method includes dirty hack there as I tried to extend original code as much. Changes in the core wouldn't be that bad.

Schema grammar implementation is not mandatory as JSON is handled by framework itself. As MariaDB json field is alias of longtext, then it just adds JSON_VALID() check:
https://github.com/ybr-nx/laravel-mariadb/blob/master/src/SchemaGrammar.php

Similar implementation can bring JSON support for SQLite using JSON1 Extension
https://sqlite.org/json1.html

I'm up to code it myself

[ghost]

+1 to this. I'm in a SQLite environment and support of JSON columns would be a huge plus !

[tomschlick]

+1, I would imagine this would create a "mariadb" db driver that splits from the main MySql one but could extend it in many parts... correct?

[michaeldyrynda]

Further to what Tom suggested, I think it's time to consider splitting Maria and MySQL anyway, as their development starts to diverge, it might make sense to split them apart

[bvbilsen]

+1

[taylorotwell]

Open to PRs.

[hackel]

The lack of JSON support for SQLite is really complicating my in-memory DB tests, and I end up having to check which driver is being used at several places in my code and use a raw query to get my tests to function the same as MySQL. Would really love to see the migration from the -> shortcut to json_extract as shown in laravel/framework#24546, which most of the time is all that is needed for SQLite & MariaDB compatibility.

[staudenmeir]

Laravel 5.6.35 supports JSON queries on SQLite (laravel/framework#25328).

[staudenmeir]

Laravel 5.8 will support JSON queries on MariaDB (laravel/framework#25517).