"MEMBER OF" support for JSON columns

[bpolaszek]

Hello there,

I recently stubbled upon Tobias Petry's tweet about using MEMBER OF syntax performance over JSON_CONTAINS for json columns in MySQL, and it looks promising.

The problem is I don't manage to make it work with the DQL translation.

Consider:

#[ORM\Entity]
class Foo {

    // ...

    /**
     * @var array<int>
     */
    #[ORM\Column(type: 'json')]
    public array $tags;
}

Doing this

$em
    ->createQueryBuilder()
    ->select('f')
    ->from(Foo::class, 'f')
    ->setMaxResults(1)
    ->where(':tag MEMBER OF f.tags')
    ->setParameter('tag', 2)
    ->getQuery()
    ->getOneOrNullResult();

Produces the following error:

  [Doctrine\ORM\Query\QueryException]
  [Semantical Error] line 0, col 71 near 'data': Error: Invalid PathExpression. Must be a CollectionValuedAssociationField.

It looks that Doctrine's MEMBER OF implementation only works with collections, or did I miss something?

I currently use scienta/doctrine-json-functions with JSON_CONTAINS as json functions aren't natively implemented (I'm fine with that) but one could not implement that MEMBER OF syntax as it seems already implemented by Doctrine, except it doesn't work with a JSON field.

[derrabus]

You are correct. DQL does not support inspecting JSON fields. Part of the problem is not every database supported by the ORM implements a native JSON data type. And those who do usually use a self-invented sytax for JSON operations.

It is certainly doable to build JSON support into DQL. But I don't expect it to be an easy task. Do you want to work on it?

[bpolaszek]

Hi @derrabus,

Thanks for your quick answer. Before considering that it didn't work out of the box, I mostly wanted to make sure I didn't forget or misused something into my query builder.

Thanks for your proposal, but unfortunately I'm already involved in a few projects and my knowledge about internal Doctrine code is close to 0, so I can't offer my help right now 🙁

[bpolaszek]

Hey again! 👋

Just asking a little help as I tried to implement this as a DQL function instead, i.e:

• DQL: JSON_MEMBER_OF(field, value)
• -> MySQL equivalent: value MEMBER OF (field)

Looks pretty simple by doing this:

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Node;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

final class JsonMemberOf extends FunctionNode
{
    /**
     * @var array<Node>
     */
    protected array $expressions = [];

    public function parse(Parser $parser): void
    {
        $lexer = $parser->getLexer();

        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->expressions[] = $parser->ArithmeticPrimary(); // @phpstan-ignore-line

        // @phpstan-ignore-next-line
        while (\count($this->expressions) < 2 && Lexer::T_CLOSE_PARENTHESIS !== $lexer->lookahead['type']) {
            $parser->match(Lexer::T_COMMA);

            $this->expressions[] = $parser->ArithmeticPrimary(); // @phpstan-ignore-line
        }

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker): string
    {
        $arguments = [];
        foreach ($this->expressions as $expression) {
            $arguments[] = $expression->dispatch($sqlWalker);
        }

        $value = $arguments[1];
        $field = $arguments[0];

        return \sprintf('%d MEMBER OF (%s)', $value, $field);
    }
}

It seems to work, except I'm kinda enforced to write ->where('JSON_MEMBER_OF(field, : value)') = TRUE instead of just ->where('JSON_MEMBER_OF(field, : value)') (without the right-hand side) or I get the following error:

In vendor/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php line 2631:
[ErrorException]
Warning: Trying to access array offset on value of type null

The problem is, even if I get the same results, MySQL conditions written as (value MEMBER OF (field)) = 1 seems to perform way worse than just value MEMBER OF (field) (an EXPLAIN confirms that it does something different).
Do you know how could I write a custom function without Doctrine looking to compare its result against a right-hand value? There's very little documentation about that.

Thanks!

[Falko05]

Hi!
Are there any updates for this feature?

[whataboutpereira]

The problem is, even if I get the same results, MySQL conditions written as (value MEMBER OF (field)) = 1 seems to perform way worse than just value MEMBER OF (field) (an EXPLAIN confirms that it does something different). Do you know how could I write a custom function without Doctrine looking to compare its result against a right-hand value? There's very little documentation about that.

Did you manage to solve this?

[bpolaszek]

Unfortunately, no.