Support for JSONB where clauses in PostgreSQL

[dapapko]

Is there any opportunity to find out if JSONB field contains one value or array of values using Cycle ORM ? Example for Eloquent ORM: $users = DB::table('users') ->whereJsonContains('options->languages', 'en') ->get();

$users = DB::table('users') ->whereJsonContains('options->languages', ['en', 'de']) ->get(); (https://laravel.com/docs/8.x/queries#json-where-clauses)

[wolfy-j]

Need some help or a bit of time to implement it.

[floor12]

Yes, that would be great...

[wolfy-j]

Have you tried new Expression or Fragment syntax? It now supports optional parameters that make SQL composing for some dialects easier.

[floor12]

Not yet, I've only been playing with this orm for the first few days. I will look in the near future when I will do some aggregations for jsonb columns en postgres... Thanx.

[floor12]

Have you tried new Expression or Fragment syntax? It now supports optional parameters that make SQL composing for some dialects easier.

@wolfy-j maybe you could help me build query with "WHERE json_column_name ? 'some string'"?

I tried to use expression, but with no success (

->where(new Expression("json_column_name ? 'some_value'"))
or
->where('json_column_name', '?', 'some_value')

get me

SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "$1"  
LINE 3: WHERE "output_urls" $1 '"some_value"' 

[floor12]

When i switched driver to sqlite to take a look on the result sql in this case, i got expected error

SQLSTATE[HY000]: General error: 1 near "?": syntax error 

When driver set to sqlite ? sign doesn't replace with $1. But with postgres driver the result sql query contains $1.

Escaping ? with one or few \ doesn't halps: result sql query contains \$1 in this case. Any ideas? ? sign is very helpful to work with json array of strings...

[wolfy-j]

Can you give us the full example and query you are trying to build?

[floor12]

Hello! Yesterday, after some hours in the orm and the driver source code, i found that problem was in php-pdo extension. It was fixed in 7.4.

https://bugs.php.net/bug.php?id=71885 https://wiki.php.net/rfc/pdo_escape_placeholders

"Question marks can be escaped by doubling them".

But before i found it, i fixed my case with by replacing ? with jsonb_exists postgres function.

[wolfy-j]

So I assume this issue is not an issue anymore?

[floor12]

I well check how the question mark doubling works with ORM query builder.

[floor12]

@wolfy-j

I checked escaping with doubling it - it work great with ORM too. My example is

$this->dbal->database('default')
            ->table('table')
            ->select('name')
            ->where(new Fragment(sprintf("field_name ?? '%s'", $textToFind)))
            ->fetchAll();

Thanks for your work.