Symfony Doctrine: orderBy JSONb field?

Question

Is there a way to order by specific jsonb field in doctrine?

Table example:

id	person_data	salary
1	{'name': 'John Smith', 'number': 13}	10000
2	{'name': 'John Doe', 'number': 192}	11000
2	{'name': 'Don John', 'number': 87}	12000

How to order by person_data->>'name'?

PostgreSQL style code not working. ->addOrderBy("person_data->>'name'")

Throws error: Error: Expected Literal, got '>'

Answer 1

I have now found a solution to this problem. There is exists a third-party library that provides a set of JSON functions from PostgreSQL for Doctrine. Among the supported JSON functions for PostgreSQL is also JsonGet.

To make things work, install first the library

composer require scienta/doctrine-json-functions

By configuring this Doctrine DQL function for the entity manager:

orm:
    entity_managers:
        default:
            dql:
                string_functions:
                    JSON_GET: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonGet

The following `orderBy`` statement can be written with the Doctrine QueryBuilder:

$qb = $this->em->createQueryBuilder();
// ...
$qb->orderBy("JSON_GET(YourAlias.YourField, 'PropertyOfTheJson')");

The QueryBuilder translates this statement as follows for PostgreSQL:

SELECT * FROM yourtable ORDER BY yourfield->'PropertyOfTheJson'

This worked for me