Symfony+Doctrine下PostgreSQL JSON数组角色查询优化及跨库兼容

问题背景 #

我们基于Symfony+Doctrine ORM开发，此前使用MySQL/MariaDB，新项目计划切换到PostgreSQL。ORM适配大部分业务逻辑都没问题，但在按角色筛选用户时遇到兼容性问题：

MySQL中我们通过LIKE匹配JSON格式的角色数组：

public function findByRole(string $role): array
{
    return $this->createQueryBuilder('u')
        ->andWhere('u.roles LIKE :role')
        ->setParameter('role', '%"'.$role.'"%')
        ->orderBy('u.id', 'ASC')
        ->getQuery()
        ->getResult()
    ;
}

但该逻辑在PostgreSQL中失效。尝试用JSON_GET_TEXT但需要指定数组索引，只能写一堆OR条件凑数，急需优化方案，同时希望能找到兼容MySQL和PostgreSQL的通用写法，实现数据库无缝切换。

一、PostgreSQL专属优化方案 #

PostgreSQL对JSON/JSONB类型有原生的数组包含判断支持，推荐使用JSONB类型（性能优于JSON），以下两种方案任选：

方案1：JSONB包含操作符@> #

直接构造包含目标角色的JSON数组，用@>判断原字段是否包含该数组：

public function findByRole(string $role): array
{
    return $this->createQueryBuilder('u')
        ->andWhere('u.roles @> :roleArray')
        // 指定参数类型为jsonb，确保Doctrine正确解析
        ->setParameter('roleArray', json_encode([$role]), 'jsonb')
        ->orderBy('u.id', 'ASC')
        ->getQuery()
        ->getResult();
}

方案2：展开JSON数组匹配 #

如果字段是JSON类型（非JSONB），可以用json_array_elements展开数组后匹配：

public function findByRole(string $role): array
{
    return $this->createQueryBuilder('u')
        // 展开roles数组为临时表r
        ->join('json_array_elements(u.roles)', 'r')
        // 直接提取r的文本值匹配目标角色
        ->andWhere('r->>'' = :role')
        ->setParameter('role', $role)
        ->orderBy('u.id', 'ASC')
        ->getQuery()
        ->getResult();
}

二、跨MySQL/PostgreSQL兼容方案 #

要实现无缝切换数据库，推荐两种通用方案：

方案1：自定义DQL函数（最优雅） #

创建一个兼容多数据库的DQL函数，自动根据数据库类型生成对应SQL：

1. 编写自定义函数类：

// src/Doctrine/Function/JsonArrayContains.php
namespace App\Doctrine\Function;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class JsonArrayContains extends FunctionNode
{
    private $field;
    private $value;

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->field = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->value = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        $platform = $sqlWalker->getConnection()->getDatabasePlatform()->getName();
        $field = $this->field->dispatch($sqlWalker);
        $value = $this->value->dispatch($sqlWalker);

        switch ($platform) {
            case 'postgresql':
                return "$field @> $value";
            case 'mysql':
                return "JSON_CONTAINS($field, $value)";
            default:
                throw new \RuntimeException("不支持的数据库类型: $platform");
        }
    }
}

2. 在Doctrine配置中注册函数：

# config/packages/doctrine.yaml
doctrine:
    orm:
        dql:
            string_functions:
                JSON_ARRAY_CONTAINS: App\Doctrine\Function\JsonArrayContains

3. 在Repository中统一调用：

public function findByRole(string $role): array
{
    return $this->createQueryBuilder('u')
        ->andWhere('JSON_ARRAY_CONTAINS(u.roles, :roleArray)')
        ->setParameter('roleArray', json_encode([$role]))
        ->orderBy('u.id', 'ASC')
        ->getQuery()
        ->getResult();
}

方案2：动态判断数据库类型生成查询 #

如果不想自定义函数，可在Repository中直接判断当前数据库类型，生成对应逻辑：

public function findByRole(string $role): array
{
    $qb = $this->createQueryBuilder('u')
        ->orderBy('u.id', 'ASC');
    
    $platform = $this->getEntityManager()->getConnection()->getDatabasePlatform()->getName();
    
    switch ($platform) {
        case 'postgresql':
            $qb->andWhere('u.roles @> :roleArray')
               ->setParameter('roleArray', json_encode([$role]), 'jsonb');
            break;
        case 'mysql':
            $qb->andWhere('JSON_CONTAINS(u.roles, :roleArray)')
               ->setParameter('roleArray', json_encode([$role]));
            break;
        default:
            // 兼容其他数据库的降级方案
            $qb->andWhere('u.roles LIKE :role')
               ->setParameter('role', '%"'.$role.'"%');
    }
    
    return $qb->getQuery()->getResult();
}

内容的提问来源于stack exchange，提问作者Lilian DI ROSA