Doctrine QueryBuilder查询Postgres jsonb[]字段返回无效JSON无法解码

问题原因 #

Postgres的jsonb[]是JSONB元素构成的数组类型，Doctrine DBAL默认没有内置支持该类型的自动序列化/反序列化，因此查询返回的是Postgres数组的原始字符串表示（带大括号和转义引号），无法直接用json_decode解析。

方法1：查询时用Postgres函数转换为可解析的JSON数组 #

直接在查询中使用Postgres的array_to_json()函数，将jsonb[]类型转换为标准JSON数组字符串，PHP端即可正常解码。

修改QueryBuilder的select语句，替换select('*')为指定字段并处理tags：

$productsData = $this->connection
    ->createQueryBuilder()
    ->from('catalog.products')
    // 显式指定字段，用array_to_json处理tags
    ->select('id, name, array_to_json(tags) as tags, created_at, updated_at')
    ->setFirstResult($from)
    ->setMaxResults($to)
    ->orderBy('id', 'ASC')
    ->executeQuery()
    ->fetchAllAssociative();

// 后续处理：直接解码tags字段
foreach ($productsData as &$product) {
    $product['tags'] = json_decode($product['tags'], true);
}
unset($product);

方法2：自定义Doctrine类型实现自动转换 #

通过自定义Doctrine DBAL类型，让框架自动处理jsonb[]与PHP数组的转换，无需每次查询手动处理。

步骤1：创建自定义类型类 #

<?php

namespace App\Doctrine\Type;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform;

class JsonbArrayType extends Type
{
    public const NAME = 'jsonb_array';

    public function getSQLDeclaration(array $column, PostgreSQLPlatform $platform): string
    {
        return 'jsonb[]';
    }

    public function convertToPHPValue($value, PostgreSQLPlatform $platform): ?array
    {
        if ($value === null) {
            return null;
        }
        // 将Postgres数组字符串转为PHP数组，再逐个解码JSON元素
        $postgresArray = $platform->convertPostgresArrayToPHP($value);
        return array_map(fn($item) => json_decode($item, true), $postgresArray);
    }

    public function convertToDatabaseValue($value, PostgreSQLPlatform $platform): ?string
    {
        if ($value === null) {
            return null;
        }
        // 将PHP数组的每个元素转为JSON字符串，再转为Postgres数组格式
        $jsonItems = array_map(fn($item) => json_encode($item), $value);
        return $platform->convertPHPArrayToPostgres($jsonItems);
    }

    public function getName(): string
    {
        return self::NAME;
    }

    public function requiresSQLCommentHint(PostgreSQLPlatform $platform): bool
    {
        return true;
    }
}

步骤2：注册自定义类型 #

Symfony项目（配置文件方式）

在config/packages/doctrine.yaml中添加类型注册：

doctrine:
    dbal:
        types:
            jsonb_array: App\Doctrine\Type\JsonbArrayType
        mapping_types:
            jsonb[]: jsonb_array

通用PHP项目（代码方式）

在初始化Doctrine连接时注册：

use App\Doctrine\Type\JsonbArrayType;
use Doctrine\DBAL\Types\Type;

// 注册自定义类型
Type::addType(JsonbArrayType::NAME, JsonbArrayType::class);
// 将Postgres的jsonb[]类型映射到自定义类型
$connection->getDatabasePlatform()->registerDoctrineTypeMapping('jsonb[]', JsonbArrayType::NAME);

使用效果 #

之后用QueryBuilder查询时，tags字段会自动转为PHP数组，无需手动解码：

$productsData = $this->connection
    ->createQueryBuilder()
    ->from('catalog.products')
    ->select('*')
    ->setFirstResult($from)
    ->setMaxResults($to)
    ->orderBy('id', 'ASC')
    ->executeQuery()
    ->fetchAllAssociative();

// $productsData中的每个item的tags已经是PHP数组

方法3：手动处理返回结果（不推荐） #

如果临时需要快速解决，可手动解析Postgres数组字符串，但此方法对复杂JSON元素兼容性差（比如JSON中包含逗号、引号时会出错）：

$productsData = $this->connection
    ->createQueryBuilder()
    ->from('catalog.products')
    ->select('*')
    ->setFirstResult($from)
    ->setMaxResults($to)
    ->orderBy('id', 'ASC')
    ->executeQuery()
    ->fetchAllAssociative();

foreach ($productsData as &$product) {
    $tagsStr = trim($product['tags'], '{}');
    // 分割数组元素（注意：若JSON含逗号会出错）
    $tagItems = array_map(fn($item) => trim($item, '"'), explode(',"', $tagsStr));
    $product['tags'] = array_map(fn($item) => json_decode($item, true), $tagItems);
}
unset($product);

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