Postgresql, how use `?` in query?

[m1roff]

What steps will reproduce the problem?

Create query with AR using jsonb operators ?, ?|, ?& (doc: https://www.postgresql.org/docs/9.4/static/functions-json.html)

Example:

$_arr = [9];
$q = Profile::find();
$q->andWhere(['?|', 'relationship', new Expression("array['$_arr']")]);
// $q->andWhere("$colName ?| array['$_arr']"); // even this type
$q->all();

What is the expected result?

Execution of SQL query like

SELECT * FROM "profile" WHERE "relationship" ?| array['9']

What do you get instead?

Execution of SQL query like

SELECT * FROM "profile" WHERE "relationship" $1| array['9']

Additional info

Q	A
Yii version	2.0.15-dev
PHP version	7.1
Operating system	ubuntu16.04.1

[lav45]

All operators in PostgreSQL have an alias function

SELECT oprname, oprcode  FROM pg_operator WHERE oprname IN ('?', '?|', '?&')  AND oprrest = 'contsel'::regproc;

oprname	| oprcode
------------------------------
?	| jsonb_exists
?|	| jsonb_exists_any
?&	| jsonb_exists_all

You can rewrite your query this way

-- SELECT * FROM "profile" WHERE "relationship" ?| array['9']
SELECT * FROM "profile" WHERE jsonb_exists_any("relationship"::jsonb, array['9']);

[m1roff]

@lav45 thanks. I tried to search it docs, but didnt find )))

[paulzi]

@lav45 jsonb_exists does not use index
It is not solution.

Bitmap Heap Scan on user_hit h  (cost=24.03..34.55 rows=3 width=36)
  Recheck Cond: (((route)::text = 'product/index'::text) AND (params ? 'q'::text))
  ->  Bitmap Index Scan on user_hit_route_params_idx  (cost=0.00..24.03 rows=3 width=0)
        Index Cond: (((route)::text = 'product/index'::text) AND (params ? 'q'::text))

Bitmap Heap Scan on user_hit h  (cost=43.20..199.28 rows=1020 width=36)
  Recheck Cond: ((route)::text = 'product/index'::text)
  Filter: jsonb_exists(params, 'q'::text)
  ->  Bitmap Index Scan on user_hit_route_params_idx  (cost=0.00..42.94 rows=3059 width=0)
        Index Cond: ((route)::text = 'product/index'::text)

Related issue php/php-src#1967

[Zegarek0]

Just a heads-up: the remark from @lav45 is good on its own and if you need to use the index like @paulzi, there's just one added step: define a wrapper function around the operator and use that instead of either the operator or its actual underlying function:

create function _jsonb_exists_(jsonb,text)
  returns boolean return $1 ? $2;
create function _jsonb_exists_any_(jsonb,text[])
  returns boolean return $1 ?| $2;

The reason this works is that Postgres runs things through a query rewrite system before passing them to planning, optimisation then execution. Bodies of simple enough functions get inlined into the query on that stage. This means a

select x from test where _jsonb_exists_(jsonb_column,'target_key')

automagically becomes

select x from test where jsonb_column ? 'target_key'

I stumbled here when looking into https://stackoverflow.com/q/79412290/5298879. The whole PostGIS index support is built on this workaround, all its index-backed functions are wrappers with an operator in them.

If it's already mentioned or better yet, already implemented somewhere here, I apologise for the noise.