Doctrine Json Functions Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require scienta/doctrine-json-functions
   

2. Register Functions (Symfony example):

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

3. First Use Case: Query a JSON column in a Customer entity:

   $query = $entityManager->createQuery(
       'SELECT c FROM App\Entity\Customer c
        WHERE JSON_UNQUOTE(JSON_EXTRACT(c.metadata, \'$.country\')) = :country'
   )->setParameter('country', 'NL');
   $results = $query->getResult();
   

Implementation Patterns

Core Workflows

1. JSON Extraction:

   // Extract nested JSON value
   $queryBuilder->andWhere('JSON_EXTRACT(c.data, \'$.user.profile.name\') = :name');
   

2. JSON Containment Checks:

   // Check if a JSON array contains a value
   $queryBuilder->andWhere('JSON_CONTAINS(c.tags, :tag) = 1');
   

3. PostgreSQL-Specific Queries:

   // Use JSONB functions (note boolean comparison)
   $queryBuilder->andWhere('JSONB_CONTAINS(c.settings, :setting) = true');
   

4. Aggregations:

   // Group by JSON object keys
   $queryBuilder->select('JSON_OBJECTAGG(c.id, c.name) as users')
                ->from('App\Entity\Customer', 'c');
   

Integration Tips

• Parameter Binding: Always bind JSON strings as parameters to avoid SQL injection:

  $queryBuilder->andWhere('JSON_CONTAINS(c.data, :jsonValue)')
               ->setParameter('jsonValue', '{"key": "value"}');
  

• Database-Specific Logic: Use Doctrine\DBAL\Platforms\AbstractPlatform to check the platform before writing queries:

  if ($entityManager->getConnection()->getDatabasePlatform() === 'postgresql') {
      $queryBuilder->andWhere('JSONB_EXISTS(c.data, \'$.key\') = true');
  }
  

• QueryBuilder vs. DQL: Prefer QueryBuilder for dynamic conditions:

  $qb = $entityManager->createQueryBuilder();
  $qb->select('c')
     ->from('App\Entity\Customer', 'c')
     ->where($qb->expr()->eq(
         'JSON_EXTRACT(c.data, \'$.status\')',
         ':status'
     ));
  

Gotchas and Tips

Pitfalls

1. Boolean Functions in DQL:

   • PostgreSQL boolean functions (e.g., JSONB_CONTAINS) must be compared explicitly with = true or = 1:

     // ❌ Fails: "JSONB_CONTAINS(...) = true" is required
     $qb->andWhere('JSONB_CONTAINS(c.data, :value)');
     

   • Fix: Always include the comparison:

     $qb->andWhere('JSONB_CONTAINS(c.data, :value) = true');
     

2. Path Syntax:

   • MySQL/MariaDB: Use single quotes for paths ('$.key.subkey').
   • PostgreSQL: Use JSON_GET_PATH for nested paths (avoids operator chaining issues):

     // ✅ Works
     $qb->andWhere('JSON_GET_PATH(c.data, \'$.key.subkey\') = :value');
     

3. SQLite Limitations:

   • Only works with the json1 extension enabled. Test with:

     SELECT sqlite_source('json1');
     

4. SQL Server Type Casting:

   • Explicitly cast JSON values to avoid type mismatches:

     $qb->andWhere('CAST(JSON_VALUE(c.data, \'$.score\') AS DECIMAL(4,2)) > 100');
     

Debugging Tips

• Platform Mismatch Errors:

  • If you get Unsupported function "JSON_EXTRACT" on PostgreSQL, ensure you registered the correct platform-specific function (e.g., JsonGet for PostgreSQL).
  • Fix: Check the platform in your config:

    $platform = $entityManager->getConnection()->getDatabasePlatform();
    if ($platform === 'postgresql') {
        $config->addCustomStringFunction('JSON_GET', \Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonGet::class);
    }
    

• Query Validation:

  • Use Doctrine\ORM\Query\QueryException to debug invalid DQL:

    try {
        $query->getResult();
    } catch (\Doctrine\ORM\Query\QueryException $e) {
        echo $query->getSQL(); // Inspect the generated SQL
    }
    

Extension Points

1. Adding Custom Functions:

   • Extend Scienta\DoctrineJsonFunctions\Query\AST\Functions\AbstractFunction:

     class CustomJsonFunction extends AbstractFunction {
         public const FUNCTION_NAME = 'CUSTOM_JSON_FUNC';
         public function parse(\Doctrine\ORM\Query\Parser $parser) { ... }
         public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) { ... }
     }
     

   • Register it in your Doctrine config.

2. Platform-Specific Logic:

   • Override supportsFunction() in your custom function to restrict usage to specific platforms:

     public function supportsFunction(\Doctrine\DBAL\Platforms\PlatformInterface $platform) {
         return $platform instanceof \Doctrine\DBAL\Platforms\PostgreSQLPlatform;
     }
     

3. Performance:

   • For complex JSON queries, consider adding database indexes on JSON columns (e.g., MySQL GENERATED ALWAYS AS or PostgreSQL GIN indexes). Example:

     -- MySQL
     ALTER TABLE customers ADD COLUMN country_index JSON GENERATED ALWAYS AS (JSON_EXTRACT(metadata, '$.country')) STORED;
     CREATE INDEX idx_country ON customers(country_index);