CODEWITHSUNDEEP
phpmysqllaravelmariadb
Prajwol
Prajwol

Reputation: 125

How to query a JSON column using the -> operator in MariaDB

I'm trying to get schedules comparing the JSON column data with following code in a Laravel project:

$schedules = Schedule::where('schedule_with->company_person', $contact_company_person->id)->get();

This generates SQL query like below:

select * from `schedules` where `schedule_with`->'$."company_person"' = 1;

While this works for MYSQL 5.7 and above but not working for MARIADB 10.5. But MARIADB already supports JSON column from 10.2 onward. For MARIADB, following query works:

select * from schedules where JSON_Value(schedule_with, "$.company_person") = 3;

Is there some config changes required in Laravel to make it work?

I know it can be achieved with raw query, I'm curious about what am I missing?

Upvotes: 3

Views: 3043

Answers (2)

Maxime
Maxime

Reputation: 8989

The problem has nothing to do with Laravel or your configuration.

MariaDB does not support this feature as of today in version 11.6.

The feature request was created in 2017: MDEV-13594

You can view compatibilities and difference between MySQL and MariaDB for each version (as I write this, the documentation stops at compatibilities and differences between MariaDB 11.3 and MySQL 8.0).

That being said, it is possible to do the same as -> or ->> as they simply are aliases for JSON_EXTRACT, and combination of JSON_EXTRACT and JSON_UNQUOTE.

-- MySQL
SELECT title->fr FROM movies;

-- MariaDB
SELECT json_extract(title, '$.fr') FROM movies;


-- MySQL
SELECT title->>fr FROM movies;

-- MariaDB
SELECT json_unquote(json_extract(title, '$.fr')) FROM movies;

If you want to know more about JSON functions, see the following:

Upvotes: 2

RickN
RickN

Reputation: 13500

No, there is no configuration change you can do to enable this.

To quote MariaDB's documentation on differences between 10.5 and MySQL (8):

MariaDB 10.5 does not support MySQL's JSON operators (-> and ->>).

"No ifs, no buts".

I guess you could do it with a regular expression replacement that edits the SQL that Laravel generates just before the SQL is executed, but that is both rather hard to execute and it seems too hacky to be worth it. A raw where query, by comparison, is not that unsightly:

$table->whereRaw('JSON_VALUE(schedule_with, ?) = ?', ['$.company_person', 3])

Upvotes: 8

Related Questions