Support where <json_column> -> '<key>' = 'value' for Qual type

[kavirajk]

Problem

Currently if I make SQL with where clause involving JSONB key matcher, &[Qual] is getting empty in begin_scan method.

Example
Let's say we have table like below

poloki=# \d streams;
                           Foreign table "public.streams"
 Column |            Type             | Collation | Nullable | Default | FDW options 
--------+-----------------------------+-----------+----------+---------+-------------
 labels | jsonb                       |           |          |         | 
 ts     | timestamp without time zone |           |          |         | 
 line   | text                        |           |          |         | 
Server: loki

Following query returns &[Qual] empty as shown below even the actual query contains a valid where clause.

poloki=# explain select * from streams where labels->'key1' = '"value1"';
                        QUERY PLAN                         
-----------------------------------------------------------
 Foreign Scan on streams  (cost=0.00..1.00 rows=1 width=0)
   Filter: ((labels -> 'key1'::text) = '"value1"'::jsonb)
   Wrappers: quals = []
   Wrappers: tgts = ["labels", "ts", "line"]
   Wrappers: sorts = []
   Wrappers: limit = None
(6 rows)

Current workaround is to match with complete JSONB object. Which is highly verbose and impossible sometimes.

poloki=# explain select * from streams where labels='{"key1": "value1"}'::jsonb;
                                                                QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on streams  (cost=0.00..1.00 rows=1 width=0)
   Filter: (labels = '{"key1": "value1"}'::jsonb)
   Wrappers: quals = [Qual { field: "labels", operator: "=", value: Cell(Json(JsonB(Object {"key1": String("value1")}))), use_or: false }]
   Wrappers: tgts = ["labels", "ts", "line"]
   Wrappers: sorts = []
   Wrappers: limit = None
(6 rows)

Proposal:

Support where <json_column> -> 'key' = 'value' for Qual type. As a first step I think we should at least fail explicitly for such unsupported operations.

[burmecia]

Thanks for the proposal, json query conditions are not supported in Qual at this moment, we will soon add explicit failure for that case.

[burmecia]

Explicit failure may not a good idea, as the json attribute conditions can still be applied locally, even though it cannot be pushed down to Qual. I will update this restriction in docs and leave current code as it is.