How to select based on fields in JSON column using Doctrine query builder

Question

I have a table with a JSON column of type longtext (DC2Type:json). The table has an entity in the Doctrine ORM in my Symfony project. I want to query based on fields within a JSON column using Doctrine query builder in variable $qb

what should I do? Everything I've found online says to install a 3rd party package to enable this feature. Is there a way to do it using just Doctrine's query builder without installing another package?

One (perhaps stupid) workaround I've tried is to treat the column as a string and then...

$qb->andWhere("my_data LIKE "%id:\"1,%"");

For example, if I want to query the JSON column my_data to find blobs that contain id":1, in their string. This fails with a very strange syntax error, And it's not correct to query a JSON field anyway. However, executing the LIKE query directly in the SQL client works the way I want, so I don't know why this fails in Doctrine either.

EDIT: This is MySQL/MariaDB.

Answer 1

Doctrine query language is very limited. It only covers the most basic/common SQL functions, which is enough for 99% of use cases, but not all.

If your version of MariaDB natively supports JSON (such as 10.2 or higher), you can use native functions to process JSON data. (If you don't, then your workaround is the only option and may require some additional filtering in the application).

In order to be able to use these functions in DQL, you need to define them yourself or indeed use a third-party library such as scienta/doctrine-json-functions (note that there are Documentation on how to use it with Symfony, and it's pretty simple).

If you only need one extra function, and for some reason don't need the entire package, you can copy that single class and use it as your own.

Alternatively, you could ditch DQL and write SQL directly, but then you won't be able to merge directly into objects and use other Doctrine magic to manipulate the data. But for simple use cases this is enough.