Postgresql: using Doctrine, select all rows that have a class namespace set at a given property in a jsonb field

  doctrine-orm, jsonb, php, postgresql, symfony

I’m using Symfony Messenger and the Doctrine Transport.

This means that Symfony messenger creates a table messenger_messages where it stores the messages to process.

I’ve configured the serializer to use the json format, so, in the database, I can use PostegreSQL native json functions.

Now, when a message is saved to the database, it has the column headers set with a json object that has this shape:


Now, using Doctrine, I want to select all messages of a specific type.

This is the code I’m testing:

namespace AppMyNamespaceMessenger;

use AppMyNamespaceMyMessage;
use DoctrineORMEntityManagerInterface;
use DoctrineORMQueryResultSetMapping;

class DoctrineMessageFinder
    private EntityManagerInterface $entityManager;

    public function __construct(EntityManagerInterface $entityManager)
        $this->entityManager = $entityManager;

     * @param string $message
    public function find(string $message, array $params = [])
        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('headers', 'h', 'json');
        $rsm->addScalarResult('body', 'b', 'json');

        // This query works
//        $query = Safesprintf("SELECT * FROM messenger_messages");

        // This query DOESN'T WORK
        $query = Safesprintf("SELECT * FROM messenger_messages WHERE (headers->'type')::jsonb -> '%s'", MyMessage::class);
        $nativeQuery = $this->entityManager->createNativeQuery($query, $rsm);

        dd($query, $nativeQuery->getResult());

The commented $query works well: it returns an array with all the messages and their data.

Executing the uncommented $query, instead, I receive this error:

An exception occurred while executing ‘SELECT * FROM messenger_messages WHERE (headers->’type’)::jsonb -> ‘AppMyNamespaceMessengerMessageMyMessage”:

SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: text -> unknown
LINE 1: SELECT * FROM messenger_messages WHERE (headers->’type’)::js…
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

I know I’m using wrong the jsonb functions of PostgreSQL but this is the first time I use it and, also if I read a lot of posts, I’m currently not able to fix the error, nor understanding which should be the correct syntax to use.

Any ideas about the correct query to execute?

Source: Symfony Questions