How to access json attributes in MariaDB with Laravel?

Question

I have a table with a column named 'data' which consists of:

{"id":1074,"opened":true,"subscribed":true}

I want to have a query which filters by these attributes.

I tried

->whereRaw('JSON_EXTRACT(data, "$.opened")', false)

and

->where('data->opened', false)

Response:

PDOException: 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 '>'$."id"' = ? and `data`->'$."opened"' = false order by `created_at` desc' at line 1 in.....

I know this code works with MySQL 5.7, but my server is running "10.1.34-MariaDB-0" and I can't change to MySQL because it's not supported by Plesk.

Answer 1

MariaDB doesnt support Json type as of today.

If you are stuck and need it to work with MariaDB. try using a meta table.

+----+------------+------------+-------+
| id | foreign_id | name       | value |
+----+------------+------------+-------+
| 1  | 1074       | subscribed | 1     |
+----+------------+------------+-------+
| 2  | 1074       | opened     | 0     |
+----+------------+------------+-------+