We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

JSONB support for Query\Builder using Postgresql

Hey all! I'm trying to use Postgresq's JSONB features inside of my Query\Builder.

I have the following code:

(new Builder())
        ->addFrom('MyModel', 'mm')
        ->columns([
            'name' => 'mm.name',
           'data' => 'mm.data->0'
        ])
        ->getQuery()->execute();

Wich results in the following exception to be thrown:

Syntax error, unexpected token >, near to '0 AS [data] FROM [MyModel] AS [mm]', when parsing: SELECT mm.name AS [name], mm.data->0 AS [data] FROM [MyModel] AS [mm]

When using raw SQL everything works as it's supposed to.

Di::getDefault()->get('db')->query(
     'SELECT mm.name AS name, mm.data->0 AS data FROM public.MyModel AS mm'
)->fetch();

I rather not write raw SQL queries every time I need to use Postgresql's JSONB features. Any good solution or workaround for this?