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

Question

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?

RickN · Accepted Answer · 2021-05-28 13:03:53Z

8

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])

edited May 28, 2021 at 13:03

answered May 28, 2021 at 12:37

RickN

13.6k4 gold badges28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RickN Over a year ago

As a side-note, it seems, according to the linked page, that MariaDB doesn't have a JSON type, rather, it stores JSON in normal text columns and has functions that work on text fields just fine. MySQL and Postgres (etc) have an actual separate type and have custom operators specifically for those types.

Maxime · Accepted Answer · 2024-01-12 14:36:35Z

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:

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related