Skip to content

Latest commit

 

History

History
338 lines (273 loc) · 18.2 KB

README.md

File metadata and controls

338 lines (273 loc) · 18.2 KB
Raw

Latest Stable Version Total Downloads License

DoctrineJsonFunctions

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

DB Functions
MySQL JSON_APPEND, JSON_ARRAY, JSON_ARRAYAGG, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT, JSON_CONTAINS, JSON_CONTAINS_PATH, JSON_DEPTH, JSON_EXTRACT, JSON_OVERLAPS, JSON_INSERT, JSON_KEYS, JSON_LENGTH, JSON_MERGE, JSON_MERGE_PRESERVE, JSON_MERGE_PATCH, JSON_OBJECT, JSON_OBJECTAGG, JSON_PRETTY, JSON_QUOTE, JSON_REMOVE, JSON_REPLACE, JSON_SEARCH, JSON_SET, JSON_TYPE, JSON_UNQUOTE, JSON_VALID
PostgreSQL @> (JSONB_CONTAINS), ? (JSONB_EXISTS), ?& (JSONB_EXISTS_ALL), ?| (JSONB_EXISTS_ANY), <@ (JSONB_IS_CONTAINED), JSONB_INSERT, JSON_EXTRACT_PATH, -> (JSON_GET), #> (JSON_GET_PATH), #>> (JSON_GET_PATH_TEXT), ->> (JSON_GET_TEXT)
MariaDb JSON_VALUE, JSON_EXISTS, JSON_QUERY, JSON_COMPACT, JSON_DETAILED, JSON_LOOSE, JSON_EQUALS, JSON_NORMALIZE
SQLite 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

Table of Contents

Changelog

Changes per release are documented with each github release. You can find an overview here: https://github.com/ScientaNL/DoctrineJsonFunctions/releases

Installation

The recommended way to install DoctrineJsonFunctions is through Composer.

Run the following command to install the package:

composer require scienta/doctrine-json-functions

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:

php8

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

Functions Registration

Doctrine ORM

Doctrine 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 with Doctrine bundle

Symfony documentation: "DoctrineBundle Configuration"

# config/packages/doctrine.yaml
doctrine:
    orm:
        dql:
            string_functions:
                JSON_EXTRACT: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonExtract
                JSON_SEARCH: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql\JsonSearch

Note that doctrine is missing a boolean_functions entry. You can register boolean functions as string_functions and need to compare them with = true to avoid DQL parser errors. For example, to check for existence of an element in a JSONB array, use andWhere('JSONB_EXISTS(u.roles, :role) = true).

Usage

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

Using Mysql 5.7+ JSON operators

$q = $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

Note that you need to use the function names. This library does not add support for custom operators like @>.

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

 $result = $q->execute(array(
    'gender' => 'male',
 ));

Boolean functions need to be registered as string functions and compared with true because Doctrine DQL does not know about boolean functions.

$q = $queryBuilder
  ->select('c')
  ->from('Customer', 'c')
  ->where('JSONB_CONTAINS(c.roles, :role) = true');

 $result = $q->execute(array(
    'role' => 'ROLE_ADMIN',
 ));

Using SQLite JSON operators

$q = $queryBuilder
  ->select('c')
  ->from('Customer', 'c')
  ->where("JSON_EXTRACT(c.attributes, '$.gender') = :gender");

 $result = $q->execute();

DQL Functions

The library provides this set of DQL functions.

Mysql 5.7+ JSON operators

Note that you can use MySQL Operators with MariaDb database if compatible.

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.
  • JSON_QUERY(json_doc, path)
    • Given a JSON document, returns an object or array specified by the path. Returns NULL if not given a valid JSON document, or if there is no match.

MariaDb 10.2.4 JSON operators

MariaDb 10.7.0 JSON operators

  • JSON_EQUALS(json_doc, json_doc)
    • Checks if there is equality between two json objects. Returns 1 if it there is, 0 if not, or NULL if any of the arguments are null.
  • JSON_NORMALIZE(json_doc)
    • Recursively sorts keys and removes spaces, allowing comparison of json documents for equality.

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.

  • JSONB_CONTAINS(jsonb, jsonb)
    • expands to jsonb @> jsonb
  • JSONB_EXISTS(jsonb, text)
    • executed as JSONB_EXISTS(jsonb, text), equivalent to jsonb ? text
  • JSONB_EXISTS_ALL(jsonb, array)
    • executed as JSONB_EXISTS_ALL(jsonb, array), equivalent to jsonb ?& array
  • JSONB_EXISTS_ANY(jsonb, array)
    • executed as JSONB_EXISTS_ANY(jsonb, array), equivalent to jsonb ?| array
  • JSONB_IS_CONTAINED(jsonb, jsonb)
    • expands to jsonb <@ jsonb
  • JSONB_INSERT
    • executed as is
  • JSON_EXTRACT_PATH
    • executed as is
  • 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.

SQLite JSON1 Extension operators

Support for all the scalar and aggregate functions as seen in the JSON1 Extension documentation.

Scalar functions

  • JSON(json)
    • Verifies that its argument is a valid JSON string and returns a minified version of that JSON string.
  • JSON_ARRAY([val[, val] ...])
    • Accepts zero or more arguments and returns a well-formed JSON array that is composed from those arguments.
  • JSON_ARRAY_LENGTH(json[, path])
    • Returns the number of elements in the JSON array json, or 0 if json is some kind of JSON value other than an array.
  • JSON_EXTRACT(json, path[, path ], ...)
    • Extracts and returns one or more values from the well-formed JSON.
  • JSON_INSERT(json[, path, value],...)
    • Given zero or more sets of paths and values, it inserts (without overwriting) each value at its corresponding path of the json.
  • JSON_OBJECT(label, value[, label, value], ...)
    • Accepts zero or more pairs of arguments and returns a well-formed JSON object that is composed from those arguments.
  • JSON_PATCH(target, patch)
    • Applies a patch to target.
  • JSON_QUOTE(value)
    • Converts the SQL value (a number or a string) into its corresponding JSON representation.
  • JSON_REMOVE(json[, path], ...)
    • Removes the values at each given path.
  • JSON_REPLACE(json[, path, value],...)
    • Given zero or more sets of paths and values, it overwrites each value at its corresponding path of the json.
  • JSON_SET(json[, path, value],...)
    • Given zero or more sets of paths and values, it inserts or overwrites each value at its corresponding path of the json.
  • JSON_TYPE(json[, path])
    • Returns the type of the outermost element of json or of the value at path.
  • JSON_VALID(json)
    • Returns 1 if the argument json is well-formed JSON or 0 otherwise.

Aggregate functions

  • JSON_GROUP_ARRAY(value)
    • Returns a JSON array comprised of all value in the aggregation
  • JSON_GROUP_OBJECT(name, value)
    • Returns a JSON object comprised of all name/value pairs in the aggregation.

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.