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

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:

{
   "type":"AppMyNamespaceMessageMyMessage",
   "X-Message-Stamp-SymfonyComponentMessengerStampBusNameStamp":"[{"busName":"messenger.bus.default"}]",
   "Content-Type":"application/json"
}

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

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *