DoctrineJsonFunctions

A set of extensions to Doctrine ORM that add support for JSON functions in DQL (Doctrine Query Language). Supports MySQL, MariaDB, PostgreSQL, SQLite, and SQL Server.

Overview

Doctrine ORM does not natively support database-specific JSON functions in DQL. This library bridges that gap by registering custom DQL function nodes for each supported platform. 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.

Supported Platforms and Functions

Table of Contents

• Requirements
• Installation
• Registration
  • Doctrine ORM
  • Symfony with DoctrineBundle
• Usage
  • MySQL / MariaDB
  • PostgreSQL
  • SQLite
  • SQL Server
• DQL Function Reference
  • MySQL 5.7+ and MariaDB (shared)
  • MySQL 8.0.21+ only
  • MariaDB only
  • PostgreSQL 9.3+
  • SQLite json1 extension
  • SQL Server 2016+
• Contributing
• Changelog
• See Also

Requirements

• PHP 8.1+
• doctrine/orm: ^2.19 or ^3
• doctrine/dbal: ^3.2 or ^4
• doctrine/lexer: ^2.0 or ^3.0

Installation

Install via Composer:

composer require scienta/doctrine-json-functions

Registration

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

Note on boolean functions: Doctrine DQL does not have a native boolean function type (upstream issue). Register boolean-returning functions (e.g., JSONB_CONTAINS, JSON_CONTAINS) as string_functions and compare them explicitly with = true or = 1 in your DQL to avoid parser errors.

Doctrine ORM

<?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);

Symfony with DoctrineBundle

# 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
                JSON_SEARCH:         Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonSearch
                # PostgreSQL
                JSONB_CONTAINS:      Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonbContains
                JSONB_EXISTS:        Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonbExists
                JSON_GET:            Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonGet
                JSON_GET_TEXT:       Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonGetText
                # SQL Server
                JSON_VALUE:          Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mssql\JsonValue

Usage

Use the registered DQL function names directly in your DQL queries or query builders. JSON path expressions must use single-quoted strings in DQL (not double-quoted).

MySQL / MariaDB

// 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();

// 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();

// Use JSON_SEARCH to find a path
$q = $queryBuilder
    ->select('c')
    ->from('App\Entity\Customer', 'c')
    ->where("JSON_SEARCH(c.attributes, 'one', :cert, null, '$.certificates') IS NOT NULL")
    ->setParameter('cert', 'BIO');

PostgreSQL

PostgreSQL operators (->, ->>, @>, ?, etc.) are exposed as named DQL functions because Doctrine DQL does not support custom operators.

// Get a JSON object field as text
$results = $queryBuilder
    ->select('c')
    ->from('App\Entity\Customer', 'c')
    ->where("JSON_GET_TEXT(c.attributes, 'country') = :country")
    ->setParameter('country', 'NL')
    ->getQuery()->getResult();

// Check JSONB containment (boolean — must compare with = true)
$results = $queryBuilder
    ->select('c')
    ->from('App\Entity\Customer', 'c')
    ->andWhere('JSONB_CONTAINS(c.roles, :role) = true')
    ->setParameter('role', '"ROLE_ADMIN"')
    ->getQuery()->getResult();

// Check if a key exists in a JSONB column
$results = $queryBuilder
    ->select('c')
    ->from('App\Entity\Customer', 'c')
    ->andWhere('JSONB_EXISTS(c.data, :key) = true')
    ->setParameter('key', 'active')
    ->getQuery()->getResult();

PostgreSQL operator chaining (e.g., col->'a'->'b') is not supported. Use JSON_GET_PATH (works on both json and jsonb) or JSON_EXTRACT_PATH (json columns only) instead.

SQLite

// Extract a field from a JSON column
$results = $queryBuilder
    ->select('c')
    ->from('App\Entity\Customer', 'c')
    ->where("JSON_EXTRACT(c.attributes, '$.country') = :country")
    ->setParameter('country', 'NL')
    ->getQuery()->getResult();

SQL Server

// Extract a scalar value from a JSON column
$results = $queryBuilder
    ->select('c')
    ->from('App\Entity\Customer', 'c')
    ->where("JSON_VALUE(c.attributes, '$.country') = :country")
    ->setParameter('country', 'NL')
    ->getQuery()->getResult();

To apply type conversion, use CAST outside the function: CAST(JSON_VALUE(c.attributes, '$.score') AS DECIMAL(4,2))

DQL Function Reference

MySQL 5.7+ and MariaDB (shared)

All functions in the Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql namespace unless noted. Functions marked as shared also work on MariaDB (they are registered under the MySQL namespace but validate against AbstractMySQLPlatform / MariaDBPlatform).

MySQL functions that also apply to MariaDB use MysqlAndMariadbJsonFunctionNode as their base, which validates against AbstractMySQLPlatform (DBAL 3.3+) or MySQLPlatform (older DBAL).

MySQL 8.0.21+ only

This function uses MysqlJsonFunctionNode and only works on MySQL (not MariaDB).

MariaDB only

Namespace: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mariadb

MySQL operators like JSON_EXTRACT are also available on MariaDB — register them from the Mysql namespace.

PostgreSQL 9.3+

Namespace: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql

PostgreSQL JSON operators are wrapped as named functions. SQL output uses the native operators.

Boolean functions (JSONB_CONTAINS, JSONB_EXISTS, JSONB_EXISTS_ALL, JSONB_EXISTS_ANY, JSONB_IS_CONTAINED) must be compared with = true due to the Doctrine DQL boolean function limitation:

->andWhere('JSONB_CONTAINS(e.data, :val) = true')

SQLite json1 extension

Namespace: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Sqlite

SQLite must have the json1 extension enabled (it is compiled in by default since SQLite 3.38.0).

Scalar functions

Aggregate functions

SQL Server 2016+

Namespace: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mssql

SQL Server does not support inline type conversion inside JSON_VALUE. Use CAST(JSON_VALUE(...) AS type) in your DQL for type conversion.

Contributing

See CONTRIBUTING.md for how to run the test suites, how the function nodes are layered, and how to add a function or a platform.

Domain terms are defined in CONTEXT.md.

Changelog

Changes per release are documented in GitHub releases.

See Also

• dunglas/doctrine-json-odm — serialize/deserialize plain PHP objects as JSON columns using Doctrine ORM
• Doctrine DQL User Defined Functions
• MySQL JSON function reference
• PostgreSQL JSON function reference
• MariaDB JSON function reference
• SQLite json1 extension reference