DoctrineJsonFunctions: DQL access to native database JSON functions

Doctrine DQL has no way to call JSON operators

The gap this library fills is specific and easy to state. Doctrine ORM does not natively support database-specific JSON functions in DQL, and the library bridges that gap by registering custom DQL function nodes for each supported platform.

The problem this creates in practice is that a Doctrine user who wants to filter on a JSON column has two bad options. They can drop to a native query, which means giving up the DQL abstraction and the query builder, and losing portability. Or they can pull the JSON value into PHP and filter there, which moves a set-based operation into a row-by-row loop and changes the performance profile entirely.

The approach taken here is a third option: register each native function as a custom DQL function, so the same query text works across the platforms that have an equivalent function. The function list in the README is long, with twenty-five shared MySQL 5.7 and MariaDB functions including `JSON_ARRAY`, `JSON_ARRAYAGG`, `JSON_CONTAINS`, `JSON_EXTRACT`, `JSON_KEYS`, `JSON_MERGE_PATCH`, `JSON_OBJECTAGG`, `JSON_SEARCH`, `JSON_SET`, `JSON_TYPE` and `JSON_VALID`. PostgreSQL 9.3 and later get a different set built around its jsonb operators, `JSONB_CONTAINS`, `JSONB_EXISTS`, `JSONB_INSERT`, `JSON_EXTRACT_PATH`, `JSON_GET` and `JSON_GET_TEXT` among them. SQLite needs the json1 extension and gets `JSON`, `JSON_GROUP_ARRAY`, `JSON_GROUP_OBJECT`, `JSON_PATCH` and the rest. SQL Server 2016 and later get exactly one function, `JSON_VALUE`.

That asymmetry is the most important fact in the library, and the README makes it explicit with separate rows for MySQL 8.0.21 and later, which adds `JSON_VALUE`, and MariaDB only, which adds `JSON_COMPACT`, `JSON_DETAILED`, `JSON_EQUALS`, `JSON_EXISTS`, `JSON_LOOSE`, `JSON_NORMALIZE`, `JSON_QUERY` and its own `JSON_VALUE`.

Every function checks the platform while generating SQL

The mechanism that makes this more than a list of aliases is the platform validation, and the README describes it in one sentence. Each function validates at SQL generation time that the correct database platform is in use, so you get an early error if a function is used against the wrong database.

That distinction matters more than it might look. Without the check, a query using `JSONB_CONTAINS` against MySQL would either fail with a database-level syntax error, which arrives after the round trip, or in the case of functions that share a name across platforms with different semantics, would silently do something different from what you wrote.

The check happens during SQL generation rather than at parse time, so the failure surfaces when Doctrine assembles the statement. For a developer, that means the error arrives on the first execution of the query rather than at application startup, which is why registering only the functions you actually use is worth the effort.

The library also extends the DQL type system in a way the README calls out as a workaround. Doctrine DQL does not have a native boolean function type, as the linked upstream issue notes, so boolean-returning functions such as `JSONB_CONTAINS` and `JSON_CONTAINS` are registered as `string_functions` and compared explicitly with `= true` or `= 1` in the DQL, to avoid parser errors.

That explains the `= 1` in the usage examples. It is not a stylistic choice, it is a consequence of registering everything as string functions.

Registration is explicit, and the Symfony list is long

All functions must be registered as custom string functions in the Doctrine configuration before they can be used in DQL, and each function class exposes a `FUNCTION_NAME` constant that matches the DQL keyword you write in queries.

For a plain Doctrine ORM setup, the registration is a few lines on the configuration object:

<?php

use Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql as MysqlFunctions;

$config = new \Doctrine\ORM\Configuration();

// Register the functions you need (example: MySQL)
$config->addCustomStringFunction(MysqlFunctions\JsonExtract::FUNCTION_NAME, MysqlFunctions\JsonExtract::class);
$config->addCustomStringFunction(MysqlFunctions\JsonContains::FUNCTION_NAME, MysqlFunctions\JsonContains::class);
$config->addCustomStringFunction(MysqlFunctions\JsonUnquote::FUNCTION_NAME, MysqlFunctions\JsonUnquote::class);

$em = EntityManager::create($dbParams, $config);

Under Symfony with DoctrineBundle the same thing is expressed in YAML, and the shape of the file makes the cost obvious. Each function is a key whose value is the fully qualified class name:

# config/packages/doctrine.yaml
doctrine:
    orm:
        dql:
            string_functions:
                # MySQL / MariaDB shared
                JSON_EXTRACT:        Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonExtract
                JSON_CONTAINS:       Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonContains
                JSON_UNQUOTE:        Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonUnquote

The namespace is segmented by platform, with `Mysql`, `Postgresql` and `Mssql` sub-namespaces, so registering a PostgreSQL function alongside MySQL ones is a matter of adding lines. There is no bundled set covering every function, and the README's advice is to register the ones you need.

One detail worth noticing in the YAML example: the same DQL keyword can be registered to different classes for different platforms, which is what makes a query portable. `JSON_VALUE` exists on both MariaDB and SQL Server with different semantics, and the configuration decides which implementation backs the keyword in a given deployment.

JSON paths need single quotes, and that trips people up

The Usage section opens with the rule that catches out everyone the first time. Use the registered DQL function names directly in your DQL queries or query builders, and JSON path expressions must use single-quoted strings in DQL, not double-quoted.

The reason is DQL's own string handling, and the rule is not negotiable, so it is worth understanding rather than memorising. In a JSON path such as `$.country`, the path is a string, and if you write it with double quotes the DQL parser treats those quotes as an identifier rather than a literal, which produces a parse error rather than a wrong result.

The usage examples show the shape of a real query. Extracting a value from a JSON column and comparing it against a bound parameter looks like this:

// Extract a value from a JSON column
$results = $em->createQuery(
    "SELECT c FROM App\Entity\Customer c
     WHERE JSON_UNQUOTE(JSON_EXTRACT(c.attributes, '$.country')) = :country"
)->setParameter('country', 'NL')->getResult();

The double wrapping of `JSON_UNQUOTE` around `JSON_EXTRACT` is not incidental. `JSON_EXTRACT` on a string value returns a JSON-quoted string, so comparing it directly to `'NL'` would fail, and the unquote is what makes the comparison work. The same applies to a path that returns a number, where the extracted value comes back as a string.

For array containment, the example uses the query builder and compares to `1` rather than `true`, which is the boolean workaround described above, and it binds the role as a JSON string literal including its quotes:

// Check if a JSON array contains a value
$results = $queryBuilder
    ->select('c')
    ->from('App\Entity\Customer', 'c')
    ->where("JSON_CONTAINS(c.roles, :role) = 1")
    ->setParameter('role', '"admin"')
    ->getQuery()->getResult();

The parameter value in the README is `"admin"` with the double quotes included, because the JSON array stores a string value and the containment check is a textual match against the JSON representation.

The test matrix runs four databases at once

The repository layout shows the kind of project this is, with `src/`, `tests/`, `docs/`, a `docker/` directory, `docker-compose.yml`, `phpunit.xml.dist`, `phpstan.neon.dist`, `psalm.xml` and `phpcs.xml.dist` at the top level, plus a `composer.json` and a `.env.dist`.

The compose file is the interesting part, because it defines a service per supported database. The `php` service builds from `docker/php/Dockerfile`, mounts the working directory at `/app`, and depends on `mysql`, `mariadb`, `postgres` and `mssql` each with the condition `service_healthy`. The four connection strings are then exported as environment variables, `MYSQL_URL`, `MARIADB_URL`, `POSTGRES_URL` and `MSSQL_URL`, all pointing at a database named `doctrine_json_test`.

The database images are pinned to specific versions: `mysql:8`, `mariadb:11`, `postgres:16` and `mcr.microsoft.com/mssql/server:2022-latest`. Each has a healthcheck with a five second interval and ten retries, and the health check is what the PHP service waits on before starting.

The consequence is that the integration test suite runs the same DQL against four database engines in one pass, which is exactly the property that makes the platform-validation claim meaningful rather than aspirational. If a function's SQL generation differed between MySQL and PostgreSQL, the test run would show it.

The two coverage badges in the README, one for unit tests and one for integration tests, are both fed from the same workflow file, `coverage.yml`, which suggests the unit tests and the integration tests are run as separate jobs in the same pipeline.

The release history is worth noting too, since this is a library with a real compatibility surface. The listed versions are 6.5.0 on 2026-03-10, 6.4.0 on 2026-03-06 and 6.3.0 on 2024-11-08, and the last push was on 2026-09-17 with the repository not archived. The requirements are PHP 8.1 or newer, with `doctrine/orm` at `^2.19` or `^3`, `doctrine/dbal` at `^3.2` or `^4`, and `doctrine/lexer` at `^2.0` or `^3.0`, so the package tracks both major lines of the ORM it extends.

Compared with native queries and with a JSON column type in the ORM

Two other approaches exist for the same problem, and the difference in approach is about where the JSON knowledge lives.

A native query, which in Doctrine means reaching for the platform's query builder or a raw SQL string, gives you every function the database offers with no library in between. The cost is that you lose the DQL abstraction at exactly the point where your schema is most likely to be non-standard, which is the case for a JSON column. You also lose portability, and the platform validation this library offers is exactly the check you would have to do by hand.

The second alternative is to model the JSON as a first-class type. Some ORMs and some database extensions can map a JSON column to a PHP object or array, with the serialization handled by the persistence layer, and then you query the fields as if they were columns. The benefit is that application code never writes a JSON path. The cost is that the mapping is database-specific in practice, that indexes on JSON paths are harder to reason about, and that the queries become less like the SQL a DBA would write.

The approach here sits between the two. You keep the column as a JSON column in the entity, you keep writing DQL, and you call the database's own functions by name. The database does the work, so its JSON indexes and its type semantics apply unchanged, and the DQL layer keeps its own conventions.

The trade-off is the registration cost and the uneven function set. You have to declare, per deployment, which functions exist, and a query written against MySQL's `JSON_SEARCH` has no direct equivalent on PostgreSQL. The library does not paper over that, and the README's platform table is the honest answer to it.

Editorial conclusion

Adopt DoctrineJsonFunctions if you already use Doctrine ORM, your database has a native JSON type, and you want those operators usable in DQL and in query builders instead of raw SQL fragments. Do not adopt it for a schema without a JSON column type, since the whole library assumes one exists, and do not expect the function set to be uniform, because the supported list differs per platform and even per engine version. Verify first which functions your target database actually offers, using the platform table rather than assuming a MySQL function exists on PostgreSQL or SQL Server.

Frequently asked questions

Official sources

1. Issues
2. License: MIT
3. README
4. Releases
5. ScientaNL/DoctrineJsonFunctions on GitHub