? and ?| operators

[tacman]

if attributes is a jsonb column containing attributes {"author": ["mark_twain"]}, how to I write this query in doctrine?

select code, attributes from instance where (attributes->'author')::jsonb ? 'mark_twain';

CONTAINS maps to @>, but I think I need the ? operator (actually, I think I need the ?| operator, since I'll have multiple elements, e.g. mark_twain or walt_whitman)

Thanks

[tacman]

I attempted to customize a function, but couldn't figure out how to handle the ? since doctrine uses ? for placeholders.

What about creating a custom function that "wraps" the ? and ?| operators, then creating a custom doctrine function that calls those custom functions? It's hackish, but the ? and ?| operators are the one I think I need for my data.

There is also a json_exists function in postgres 15 (https://www.depesz.com/2022/04/01/waiting-for-postgresql-15-sql-json-query-functions/) maybe simply writing a function what calls it would be easier.

[tacman]

I thought the solution was going to be to use json_exists, but it appears that the functions don't use the GIN indexes, only the operators. That seems crazy, and I hope I'm wrong on that. Details at https://dba.stackexchange.com/questions/90002/postgresql-operator-uses-index-but-underlying-function-does-not

If so, though, a solution is also offered there, to create a custom function that wraps the operator call:

CREATE OR REPLACE FUNCTION jb_contains(jsonb, text)
  RETURNS bool AS
'SELECT $1 ? $2' LANGUAGE sql IMMUTABLE;

Then I guess add a doctrine function using this library that calls jb_contains. It feels hackish, I'll do some testing to see if this is even necessary and if the solution proposed here works.

[ellmout]

Hey,
I was having the same problem with "?|".
Just found out, you can escape a question mark by adding another one "??|".
My class "JsonAnyOf" is working great now :

<?php

declare(strict_types=1);

namespace App\ORM;

use MartinGeorgiev\Doctrine\ORM\Query\AST\Functions\BaseFunction;

class JsonAnyOf extends BaseFunction
{
    protected function customiseFunction(): void
    {
        $this->setFunctionPrototype('(%s ??| %s)');
        $this->addNodeMapping('StringPrimary');
        $this->addNodeMapping('StringPrimary');
    }
}

[martin-georgiev]

Support for ?, ?| and some other operators got added with #170