PHP code example of scienta / doctrine-json-functions

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

scienta / doctrine-json-functions example snippets

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

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

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

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

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

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

declare(strict_types=1);

namespace Scienta\DoctrineJsonFunctions\Query\AST\Functions\Mysql;

class JsonMyNewFunction extends MysqlAndMariadbJsonFunctionNode
{
    public const FUNCTION_NAME = 'JSON_MY_NEW_FUNCTION';

    protected $

$config->addCustomStringFunction(JsonMyNewFunction::FUNCTION_NAME, JsonMyNewFunction::class);

declare(strict_types=1);

namespace Scienta\DoctrineJsonFunctions\Query\AST\Functions\MyNewDb;

use Doctrine\DBAL\Exception;
use Doctrine\DBAL\Platforms\MyNewDbPlatform;
use Doctrine\ORM\Query\SqlWalker;
use Scienta\DoctrineJsonFunctions\Query\AST\Functions\AbstractJsonFunctionNode;

abstract class MyNewDbJsonFunctionNode extends AbstractJsonFunctionNode
{
    protected function validatePlatform(SqlWalker $sqlWalker): void
    {
        if (!$sqlWalker->getConnection()->getDatabasePlatform() instanceof MyNewDbPlatform) {
            throw new Exception("Platform not supported");
        }
    }
}

bash
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