scienta/doctrine-json-functions

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

4.0.2 2019-03-12 08:52 UTC

README

Latest Stable Version Total Downloads License

DoctrineJsonFunctions

A set of extensions to Doctrine 2 that add support for json query functions. +Functions are available for MySQL, MariaDb and PostgreSQL.

DB Functions
MySQL JSON_APPEND, JSON_ARRAY, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_DEPTH, JSON_EXTRACT, JSON_INSERT, JSON_KEYS, JSON_LENGTH, JSON_MERGE, JSON_OBJECT, JSON_QUOTE, JSON_REMOVE, JSON_REPLACE, JSON_SEARCH, JSON_SET, JSON_TYPE, JSON_UNQUOTE, JSON_VALID
PostgreSQL JSON_EXTRACT_PATH, GT, GT_GT, SHARP_GT, SHARP_GT_GT
MariaDb JSON_VALUE, JSON_EXISTS

Table of Contents

Installation

The recommended way to install DoctrineJsonFunctions is through Composer. Add the following dependency to your composer.json

{
	"require": {
		"scienta/doctrine-json-functions": "~4.0"
	}
}

Alternatively, you can download the source code as a file and extract it.

Testing

This repository uses phpunit for testing purposes. If you just want to run the tests you can use the docker composer image to install and run phpunit. There is a docker-compose file with the correct mount but if you want to use just docker you can run this:

docker run -it -v ${PWD}:/app composer:latest /bin/bash -c "composer install && ./vendor/bin/phpunit"

Functions Registration

Doctrine 2 ORM

Doctrine 2 documentation: "DQL User Defined Functions"

<?php

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

$config = new \Doctrine\ORM\Configuration();
$config->addCustomStringFunction(DqlFunctions\JsonExtract::FUNCTION_NAME, DqlFunctions\JsonExtract::class);
$config->addCustomStringFunction(DqlFunctions\JsonSearch::FUNCTION_NAME, DqlFunctions\JsonSearch::class);

$em = EntityManager::create($dbParams, $config);
$queryBuilder = $em->createQueryBuilder();

Symfony 2 & 3 with Doctrine bundle

Symfony documentation: "DoctrineBundle Configuration"

# app/config/config.yml
doctrine:
    orm:
        entity_managers:
            some_em: # usually also "default"
                dql:
                    string_functions:
                        JSON_EXTRACT: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonExtract
                        JSON_SEARCH: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonSearch

Usage

Mind the comparison when creating the expression and escape the parameters to be valid JSON.

Using Mysql 5.7+ JSON operators

$queryBuilder
  ->select('c')
  ->from('Customer', 'c')
  ->where("JSON_CONTAINS(c.attributes, :certificates, '$.certificates') = 1");
 
$result = $q->execute(array(
  'certificates' => '"BIO"',
));

Using PostgreSQL 9.3+ JSON operators

$queryBuilder
  ->select('c')
  ->from('Customer', 'c')
  ->where("JSON_GET_TEXT(c.attributes, 'gender') = :gender");
 
 $result = $q->execute(array(
    'gender' => 'male',
 ));

DQL Functions

The library provides this set of DQL functions.

Mysql 5.7+ JSON operators

MariaDb 10.2.3 JSON operators

  • JSON_VALUE(json_doc, path)
    • Returns the scalar specified by the path. Returns NULL if there is no match.
  • JSON_EXISTS(json_doc, path)
    • Determines whether a specified JSON value exists in the given data. Returns 1 if found, 0 if not, or NULL if any of the inputs were NULL.

PostgreSQL 9.3+ JSON operators

Basic support for JSON operators is implemented. This works even with Doctrine\DBAL v2.5. Official documentation of JSON operators.

  • JSON_GET(jsondoc, path)

    • expands to jsondoc->path in case of numeric path (use with JSON arrays)
    • expands to jsondoc->'path' in case of non-numeric path (use with JSON objects)
  • JSON_GET_TEXT(jsondoc, path)

    • expands to jsondoc->>path in case of numeric path (use with JSON arrays)
    • expands to jsondoc->>'path' in case of non-numeric path (use with JSON objects)
  • JSON_GET_PATH(jsondoc, path)

    • expands to jsondoc#>'path'
  • JSON_GET_PATH_TEXT(jsondoc, path)

    • expands to jsondoc#>>'path'

Please note that chaining of JSON operators is not supported.

Extendability and Database Support

Architecture

Platform function classes naming rule is:

Scienta\DoctrineJsonFunctions\Query\AST\Functions\$platformName\$functionName

Adding a new platform

To add support of new platform you just need to create new folder Scienta\DoctrineJsonFunctions\Query\AST\Functions\$platformName and implement required function there according to naming rules

Adding a new function

If you want to add new function to this library feel free to fork it and create pull request with your implementation. Please, remember to update documentation with your new functions.

See also

dunglas/doctrine-json-odm: Serialize / deserialize plain old PHP objects into JSON columns.