PHPackages                             scienta/doctrine-json-functions - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. scienta/doctrine-json-functions

ActiveLibrary[Database & ORM](/categories/database)

scienta/doctrine-json-functions
===============================

A set of extensions to Doctrine that add support for json query functions.

6.5.0(2mo ago)58523.9M—1.5%51[5 issues](https://github.com/ScientaNL/DoctrineJsonFunctions/issues)20MITPHPPHP ^8.1

Since Dec 14Pushed 2mo ago14 watchersCompare

[ Source](https://github.com/ScientaNL/DoctrineJsonFunctions)[ Packagist](https://packagist.org/packages/scienta/doctrine-json-functions)[ RSS](/packages/scienta-doctrine-json-functions/feed)WikiDiscussions master Synced 1mo ago

READMEChangelog (10)Dependencies (30)Versions (36)Used By (20)

[![Latest Stable Version](https://camo.githubusercontent.com/3ff6d8b0d971d1462dc43b49d45d16db32207d84c8105df6188e7b91e996e598/68747470733a2f2f706f7365722e707567782e6f72672f736369656e74612f646f637472696e652d6a736f6e2d66756e6374696f6e732f762f737461626c653f666f726d61743d666c6174)](https://packagist.org/packages/scienta/doctrine-json-functions)[![Total Downloads](https://camo.githubusercontent.com/0c1dc3b9e24fa0aadcf113c5217544a57105b400a6e03016936b448218045ff4/68747470733a2f2f706f7365722e707567782e6f72672f736369656e74612f646f637472696e652d6a736f6e2d66756e6374696f6e732f646f776e6c6f6164733f666f726d61743d666c6174)](https://packagist.org/packages/scienta/doctrine-json-functions)[![License](https://camo.githubusercontent.com/a12545eb84d2cdf9ab6bbcd5622487a9a48f63e45cd273721ca76c0d54da15e1/68747470733a2f2f706f7365722e707567782e6f72672f736369656e74612f646f637472696e652d6a736f6e2d66756e6374696f6e732f6c6963656e7365)](https://packagist.org/packages/scienta/doctrine-json-functions)[![Unittest Coverage](https://camo.githubusercontent.com/6bc39a8f365071957ceea1d25cf4ef521223c43cff31a8e8cbe2e8667a2d674f/68747470733a2f2f696d672e736869656c64732e696f2f656e64706f696e743f75726c3d68747470733a2f2f7261772e67697468756275736572636f6e74656e742e636f6d2f536369656e74614e4c2f446f637472696e654a736f6e46756e6374696f6e732f6261646765732f636f7665726167652e6a736f6e)](https://github.com/ScientaNL/DoctrineJsonFunctions/actions/workflows/coverage.yml)[![Integrationtest Coverage](https://camo.githubusercontent.com/643f3dcd77cc3f19a2e3d170de3f4ce010adc408842ae68943f0f591d239de94/68747470733a2f2f696d672e736869656c64732e696f2f656e64706f696e743f75726c3d68747470733a2f2f7261772e67697468756275736572636f6e74656e742e636f6d2f536369656e74614e4c2f446f637472696e654a736f6e46756e6374696f6e732f6261646765732f636f7665726167652d696e746567726174696f6e2e6a736f6e)](https://github.com/ScientaNL/DoctrineJsonFunctions/actions/workflows/coverage.yml)

DoctrineJsonFunctions
=====================

[](#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
--------

[](#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

[](#supported-platforms-and-functions)

DatabaseFunctionsMySQL 5.7+ / MariaDB`JSON_ARRAY`, `JSON_ARRAY_APPEND`, `JSON_ARRAY_INSERT`, `JSON_ARRAYAGG`, `JSON_CONTAINS`, `JSON_CONTAINS_PATH`, `JSON_DEPTH`, `JSON_EXTRACT`, `JSON_INSERT`, `JSON_KEYS`, `JSON_LENGTH`, `JSON_MERGE`, `JSON_MERGE_PATCH`, `JSON_MERGE_PRESERVE`, `JSON_OBJECT`, `JSON_OBJECTAGG`, `JSON_OVERLAPS`, `JSON_PRETTY`, `JSON_QUOTE`, `JSON_REMOVE`, `JSON_REPLACE`, `JSON_SEARCH`, `JSON_SET`, `JSON_TYPE`, `JSON_UNQUOTE`, `JSON_VALID`MySQL 8.0.21+ only`JSON_VALUE`MariaDB only`JSON_COMPACT`, `JSON_DETAILED`, `JSON_EQUALS`, `JSON_EXISTS`, `JSON_LOOSE`, `JSON_NORMALIZE`, `JSON_QUERY`, `JSON_VALUE`PostgreSQL 9.3+`JSONB_CONTAINS`, `JSONB_EXISTS`, `JSONB_EXISTS_ALL`, `JSONB_EXISTS_ANY`, `JSONB_INSERT`, `JSONB_IS_CONTAINED`, `JSON_EXTRACT_PATH`, `JSON_GET`, `JSON_GET_PATH`, `JSON_GET_PATH_TEXT`, `JSON_GET_TEXT`SQLite (json1 ext.)`JSON`, `JSON_ARRAY`, `JSON_ARRAY_LENGTH`, `JSON_EXTRACT`, `JSON_GROUP_ARRAY`, `JSON_GROUP_OBJECT`, `JSON_INSERT`, `JSON_OBJECT`, `JSON_PATCH`, `JSON_QUOTE`, `JSON_REMOVE`, `JSON_REPLACE`, `JSON_SET`, `JSON_TYPE`, `JSON_VALID`SQL Server 2016+`JSON_VALUE`Table of Contents
-----------------

[](#table-of-contents)

- [Requirements](#requirements)
- [Installation](#installation)
- [Testing](#testing)
- [Registration](#registration)
    - [Doctrine ORM](#doctrine-orm)
    - [Symfony with DoctrineBundle](#symfony-with-doctrinebundle)
- [Usage](#usage)
    - [MySQL / MariaDB](#mysql--mariadb)
    - [PostgreSQL](#postgresql)
    - [SQLite](#sqlite)
    - [SQL Server](#sql-server)
- [DQL Function Reference](#dql-function-reference)
    - [MySQL 5.7+ and MariaDB (shared)](#mysql-57-and-mariadb-shared)
    - [MySQL 8.0.21+ only](#mysql-8021-only)
    - [MariaDB only](#mariadb-only)
    - [PostgreSQL 9.3+](#postgresql-93)
    - [SQLite json1 extension](#sqlite-json1-extension)
    - [SQL Server 2016+](#sql-server-2016)
- [Architecture](#architecture)
- [Extending the Library](#extending-the-library)
    - [Adding a new function](#adding-a-new-function)
    - [Adding a new platform](#adding-a-new-platform)
- [Changelog](#changelog)
- [See Also](#see-also)

Requirements
------------

[](#requirements)

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

Installation
------------

[](#installation)

Install via Composer:

```
composer require scienta/doctrine-json-functions
```

Testing
-------

[](#testing)

This repository uses PHPUnit. There are two test suites:

- **Unit tests** — mock the Doctrine infrastructure, no real database needed
- **Integration tests** — run DQL queries against real MySQL, MariaDB, PostgreSQL, and SQLite databases

### Unit tests

[](#unit-tests)

```
composer install
composer test:unit
```

Or with Docker Compose (PHP 8.4):

```
docker compose up -d --build --wait
docker compose exec php composer test:unit
```

### Code coverage

[](#code-coverage)

The Docker image includes the PCOV extension. Run the unit tests with Clover coverage output:

```
docker compose up -d --build --wait
docker compose run --rm php bash -c "composer install && composer test:coverage"
```

This writes `coverage.xml` to the project root. Coverage is also reported automatically on every PR and push to `master` via the [Coverage workflow](https://github.com/ScientaNL/DoctrineJsonFunctions/actions/workflows/coverage.yml).

### Integration test coverage

[](#integration-test-coverage)

PCOV is available inside the container. Start the database services first, then run:

```
docker compose up -d --build --wait
docker compose exec php bash -c "composer install && composer test:coverage:integration"
```

This writes `coverage-integration.xml` to the project root. Integration coverage is also reported automatically on every PR alongside unit coverage.

### Integration tests

[](#integration-tests)

Start the database containers, then run the tests inside the PHP container:

```
docker compose up -d --build --wait
docker compose exec php composer test:integration
```

Run a single platform:

```
docker compose exec php composer test:integration:mysql
docker compose exec php composer test:integration:mariadb
docker compose exec php composer test:integration:postgres
docker compose exec php composer test:integration:sqlite
docker compose exec php composer test:integration:mssql
```

**Running locally without Docker:** copy `.env.dist` to `.env`, fill in your connection URLs, then:

```
export $(grep -v '^#' .env | xargs)
composer test:integration
```

SQLite always runs in-memory and needs no configuration.

Registration
------------

[](#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](https://github.com/doctrine/orm/issues/6278)). 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

[](#doctrine-orm)

```