JSON search on Postgresql

Log into pgsql, run command:

$ \doS+

You will get function names corresponding to json(b) and other operators, for example #>> operator is corresponding to function json_extract_path_text

Now register selected function(s) for use in PHQL at startup/bootstrap of Your App, like this example for jsonb_exists and jsonb_contains functions:

$dialect = new \Phalcon\Db\Dialect\Postgresql();
$dialect->registerCustomFunction(
    'jsonb_exists',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "jsonb_exists(%s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);
$dialect->registerCustomFunction(
    'jsonb_contains',
    function($dialect, $expression) {
        $arguments = $expression['arguments'];
        return sprintf(
            "jsonb_contains(%s, %s)",
            $dialect->getSqlExpression($arguments[0]),
            $dialect->getSqlExpression($arguments[1])
        );
    }
);

now you can use jsonb_exists and jsonb_contains functions inside PHQL

Sorry taking too long to answer, I had to do some tests, but it was a very helpful suggestion.

Just for other readers, the syntax used whas %s->%s->>%s = %s and the quotes you can put, or not, on the caller.

The above is specific syntax to Postgresql which is not supported in PHQL (hence the errors you see).

You might be able to work something out by creating a custom dialect : https://docs.phalcon.io/4.0/en/db-layer#custom-1

Say you can create a custom function for you i.e. JSON_SEARCH which will use %s->'%s'->>'%s' = '%s' as the template, or something similar - you know the syntax better.

Alternatively you can send directly a query in the db service and get the results that way.

Glad you got it sorted out.

If it interests you, you could share your implementation in the documentation for this page: https://docs.phalcon.io/4.0/en/db-layer#custom-1

You will need to issue a Pull Request for this file: https://github.com/phalcon/docs/blob/4.0/en/db-layer.md