如何用Doctrine Query Builder查询JSONB列？SQL可行QB报错

问题出在Doctrine的DQL解析器不识别PostgreSQL的->> JSON操作符，直接写入会触发语法解析错误。以下是几种可行的解决方法：

方法一：调用PostgreSQL原生等价函数 #

直接在QueryBuilder中使用PostgreSQL的jsonb_extract_path_text函数（它是->>操作符的等价实现），Doctrine可以正常解析该函数：

public function testsql(): void
{
    $qb = $this->createQueryBuilder('a');
    $qb->select('a.id', 'a.location')
       ->andWhere("FUNCTION('jsonb_extract_path_text', a.location, 'title') LIKE :loc0")
       ->setParameter('loc0', '%test%'); // 注意移除参数值前后的多余空格，避免匹配偏差

    $result = $qb->getQuery()->getResult();
}

方法二：用原生SQL片段绕过DQL解析 #

如果不想用函数调用，可以直接使用原生SQL逻辑：

方式1：结合QueryBuilder表达式 #

public function testsql(): void
{
    $qb = $this->createQueryBuilder('a');
    $qb->select('a.id', 'a.location')
       ->andWhere($qb->expr()->like(
           $qb->expr()->literal("a.location->>'title'"),
           ':loc0'
       ))
       ->setParameter('loc0', '%test%');
}

方式2：直接执行原生SQL #

如果QueryBuilder限制较多，也可以直接执行原生SQL语句：

public function testsql(): void
{
    $sql = <<<SQL
        SELECT id, location
        FROM post
        WHERE location->>'title' LIKE :loc0
    SQL;

    $result = $this->getEntityManager()
                   ->getConnection()
                   ->executeQuery($sql, ['loc0' => '%test%'])
                   ->fetchAllAssociative();
}

方法三：自定义DQL函数支持->>操作符 #

通过注册自定义DQL函数，让Doctrine直接识别->>语法，一劳永逸解决同类问题：

1. 创建自定义DQL函数类

<?php

namespace App\DQL;

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

class JsonbGetTextFunction extends FunctionNode
{
    public $jsonExpr;
    public $pathExpr;

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

    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf(
            '%s->>%s',
            $this->jsonExpr->dispatch($sqlWalker),
            $this->pathExpr->dispatch($sqlWalker)
        );
    }
}

2. 在Doctrine配置中注册函数
   在Symfony项目的config/packages/doctrine.yaml中添加配置：

doctrine:
    orm:
        dql:
            string_functions:
                JSONB_GET_TEXT: App\DQL\JsonbGetTextFunction

3. 在QueryBuilder中使用自定义函数

public function testsql(): void
{
    $qb = $this->createQueryBuilder('a');
    $qb->select('a.id', 'a.location')
       ->andWhere("JSONB_GET_TEXT(a.location, 'title') LIKE :loc0")
       ->setParameter('loc0', '%test%');

    $result = $qb->getQuery()->getResult();
}

内容的提问来源于stack exchange，提问作者Sam