SET datestyle = 'ISO';
CREATE SERVER binary_json_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'json_test', driver 'binary');
CREATE SERVER http_json_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'json_test', driver 'http');
CREATE USER MAPPING FOR CURRENT_USER SERVER binary_json_loopback;
CREATE USER MAPPING FOR CURRENT_USER SERVER http_json_loopback;
SELECT clickhouse_raw_query('DROP DATABASE IF EXISTS json_test');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query('CREATE DATABASE json_test');
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query($$
    CREATE TABLE json_test.things (
        id   Int32 NOT NULL,
        data JSON NOT NULL
    ) ENGINE = MergeTree PARTITION BY id ORDER BY (id);
$$);
 clickhouse_raw_query 
----------------------
 
(1 row)

CREATE SCHEMA json_bin;
CREATE SCHEMA json_http;
IMPORT FOREIGN SCHEMA "json_test" FROM SERVER binary_json_loopback INTO json_bin;
\d json_bin.things
                 Foreign table "json_bin.things"
 Column |  Type   | Collation | Nullable | Default | FDW options 
--------+---------+-----------+----------+---------+-------------
 id     | integer |           | not null |         | 
 data   | jsonb   |           | not null |         | 
Server: binary_json_loopback
FDW options: (database 'json_test', table_name 'things', engine 'MergeTree')

IMPORT FOREIGN SCHEMA "json_test" FROM SERVER http_json_loopback INTO json_http;
\d json_http.things
                Foreign table "json_http.things"
 Column |  Type   | Collation | Nullable | Default | FDW options 
--------+---------+-----------+----------+---------+-------------
 id     | integer |           | not null |         | 
 data   | jsonb   |           | not null |         | 
Server: http_json_loopback
FDW options: (database 'json_test', table_name 'things', engine 'MergeTree')

INSERT INTO json_bin.things VALUES
    (1, '{"id": 1, "name": "widget", "size": "large", "stocked": true}'),
    (2, '{"id": 2, "name": "sprocket", "size": "small", "stocked": true}')
;
ERROR:  pg_clickhouse: could not finish INSERT - DB::Exception: Invalid version for Object structure serialization.
INSERT INTO json_http.things VALUES
    (3, '{"id": 3, "name": "gizmo", "size": "medium", "stocked": true}'),
    (4, '{"id": 4, "name": "doodad", "size": "large", "stocked": false}')
;
SELECT * FROM json_bin.things ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY id ASC NULLS LAST
SELECT * FROM json_http.things ORDER BY id;
 id |                               data                               
----+------------------------------------------------------------------
  3 | {"id": "3", "name": "gizmo", "size": "medium", "stocked": true}
  4 | {"id": "4", "name": "doodad", "size": "large", "stocked": false}
(2 rows)

-- Should also support JSON mapping.
CREATE FOREIGN TABLE json_bin.json_things (
    id   integer NOT NULL,
    data json    NOT NULL
) SERVER binary_json_loopback OPTIONS (table_name 'things');
CREATE FOREIGN TABLE json_http.json_things (
    id   integer NOT NULL,
    data json    NOT NULL
) SERVER http_json_loopback OPTIONS (table_name 'things');
INSERT INTO json_bin.json_things
VALUES (5, '{"id": 5, "name": "bauble", "size": "small", "stocked": true}');
ERROR:  pg_clickhouse: could not finish INSERT - DB::Exception: Invalid version for Object structure serialization.
INSERT INTO json_http.json_things
VALUES (6, '{"id": 6, "name": "curio", "size": "medium", "stocked": false}');
SELECT * FROM json_bin.json_things ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY id ASC NULLS LAST
SELECT * FROM json_http.json_things ORDER BY id;
 id |                           data                            
----+-----------------------------------------------------------
  3 | {"id":"3","name":"gizmo","size":"medium","stocked":true}
  4 | {"id":"4","name":"doodad","size":"large","stocked":false}
  6 | {"id":"6","name":"curio","size":"medium","stocked":false}
(3 rows)

-- Subscript access on JSON columns must not be pushed down to ClickHouse.
-- ClickHouse JSON does not support the jsonb `column['key']` syntax (it
-- requires dot notation), so subscripts must be evaluated locally by
-- PostgreSQL.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT data['name'] FROM json_http.things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_http.things
   Output: data['name'::text]
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data['name'] FROM json_http.things ORDER BY id;
   data   
----------
 "gizmo"
 "doodad"
 "curio"
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT data['name'] FROM json_bin.things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_bin.things
   Output: data['name'::text]
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data['name'] FROM json_bin.things ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY id ASC NULLS LAST
-- DISTINCT forces an ORDER BY or HashAgg; the subscript must stay local.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT DISTINCT data['size'] FROM json_http.things;
                      QUERY PLAN                       
-------------------------------------------------------
 HashAggregate
   Output: (data['size'::text])
   Group Key: things.data['size'::text]
   ->  Foreign Scan on json_http.things
         Output: data['size'::text]
         Remote SQL: SELECT data FROM json_test.things
(6 rows)

SELECT DISTINCT data['size'] FROM json_http.things;
   data   
----------
 "medium"
 "large"
(2 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT DISTINCT data['size'] FROM json_bin.things;
                      QUERY PLAN                       
-------------------------------------------------------
 HashAggregate
   Output: (data['size'::text])
   Group Key: things.data['size'::text]
   ->  Foreign Scan on json_bin.things
         Output: data['size'::text]
         Remote SQL: SELECT data FROM json_test.things
(6 rows)

SELECT DISTINCT data['size'] FROM json_bin.things;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT data FROM json_test.things
-- GROUP BY with a JSON subscript expression.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT data['size'], count(*) FROM json_http.things GROUP BY data['size'];
                      QUERY PLAN                       
-------------------------------------------------------
 HashAggregate
   Output: (data['size'::text]), count(*)
   Group Key: things.data['size'::text]
   ->  Foreign Scan on json_http.things
         Output: data['size'::text]
         Remote SQL: SELECT data FROM json_test.things
(6 rows)

SELECT data['size'], count(*) FROM json_http.things GROUP BY data['size'];
   data   | count 
----------+-------
 "medium" |     2
 "large"  |     1
(2 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT data['size'], count(*) FROM json_bin.things GROUP BY data['size'];
                      QUERY PLAN                       
-------------------------------------------------------
 HashAggregate
   Output: (data['size'::text]), count(*)
   Group Key: things.data['size'::text]
   ->  Foreign Scan on json_bin.things
         Output: data['size'::text]
         Remote SQL: SELECT data FROM json_test.things
(6 rows)

SELECT data['size'], count(*) FROM json_bin.things GROUP BY data['size'];
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT data FROM json_test.things
-- The jsonb ->> operator runs locally when used in SELECT target lists.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT data ->> 'name' FROM json_http.things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_http.things
   Output: (data ->> 'name'::text)
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data ->> 'name' FROM json_http.things ORDER BY id;
 ?column? 
----------
 gizmo
 doodad
 curio
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT data ->> 'name' FROM json_bin.things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_bin.things
   Output: (data ->> 'name'::text)
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data ->> 'name' FROM json_bin.things ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY id ASC NULLS LAST
-- The json ->> operator runs locally when used in SELECT target lists.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT data ->> 'name' FROM json_http.json_things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_http.json_things
   Output: (data ->> 'name'::text)
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data ->> 'name' FROM json_http.json_things ORDER BY id;
 ?column? 
----------
 gizmo
 doodad
 curio
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT data ->> 'name' FROM json_bin.json_things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_bin.json_things
   Output: (data ->> 'name'::text)
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data ->> 'name' FROM json_bin.json_things ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY id ASC NULLS LAST
-- WHERE clause with jsonb ->> equality must be pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.things WHERE data ->> 'name' = 'widget';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Foreign Scan on json_http.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.name = 'widget'))
(3 rows)

SELECT * FROM json_http.things WHERE data ->> 'name' = 'widget';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.things WHERE data ->> 'name' = 'widget';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Foreign Scan on json_bin.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.name = 'widget'))
(3 rows)

SELECT * FROM json_bin.things WHERE data ->> 'name' = 'widget';
 id | data 
----+------
(0 rows)

-- WHERE clause with json ->> equality must be pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_things WHERE data ->> 'name' = 'widget';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.name = 'widget'))
(3 rows)

SELECT * FROM json_http.json_things WHERE data ->> 'name' = 'widget';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_things WHERE data ->> 'name' = 'widget';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.name = 'widget'))
(3 rows)

SELECT * FROM json_bin.json_things WHERE data ->> 'name' = 'widget';
 id | data 
----+------
(0 rows)

-- WHERE clause with jsonb ->> and LIKE.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.things WHERE data ->> 'name' LIKE 'wid%';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Foreign Scan on json_http.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.name LIKE 'wid%'))
(3 rows)

SELECT * FROM json_http.things WHERE data ->> 'name' LIKE 'wid%';
ERROR:  pg_clickhouse: Code: 43. DB::Exception: Illegal type Dynamic of argument of function like: In scope SELECT id, data FROM json_test.things WHERE data.name LIKE 'wid%'. (ILLEGAL_TYPE_OF_ARGUMENT)
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE ((data.name LIKE 'wid%'))
CONTEXT:  HTTP status code: 500
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.things WHERE data ->> 'name' LIKE 'wid%';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Foreign Scan on json_bin.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.name LIKE 'wid%'))
(3 rows)

SELECT * FROM json_bin.things WHERE data ->> 'name' LIKE 'wid%';
ERROR:  pg_clickhouse: DB::Exception: Illegal type Dynamic of argument of function like: In scope SELECT id, data FROM json_test.things WHERE data.name LIKE 'wid%'
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE ((data.name LIKE 'wid%'))
-- WHERE clause with json ->> and LIKE.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_things WHERE data ->> 'name' LIKE 'wid%';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.name LIKE 'wid%'))
(3 rows)

SELECT * FROM json_http.json_things WHERE data ->> 'name' LIKE 'wid%';
ERROR:  pg_clickhouse: Code: 43. DB::Exception: Illegal type Dynamic of argument of function like: In scope SELECT id, data FROM json_test.things WHERE data.name LIKE 'wid%'. (ILLEGAL_TYPE_OF_ARGUMENT)
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE ((data.name LIKE 'wid%'))
CONTEXT:  HTTP status code: 500
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_things WHERE data ->> 'name' LIKE 'wid%';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.name LIKE 'wid%'))
(3 rows)

SELECT * FROM json_bin.json_things WHERE data ->> 'name' LIKE 'wid%';
ERROR:  pg_clickhouse: DB::Exception: Illegal type Dynamic of argument of function like: In scope SELECT id, data FROM json_test.things WHERE data.name LIKE 'wid%'
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE ((data.name LIKE 'wid%'))
-- WHERE with multiple jsonb ->> conditions (AND).
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.things
  WHERE data ->> 'size' = 'large' AND data ->> 'stocked' = 'true';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.size = 'large')) AND ((data.stocked = 'true'))
(3 rows)

SELECT * FROM json_http.things
  WHERE data ->> 'size' = 'large' AND data ->> 'stocked' = 'true';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.things
  WHERE data ->> 'size' = 'large' AND data ->> 'stocked' = 'true';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.size = 'large')) AND ((data.stocked = 'true'))
(3 rows)

SELECT * FROM json_bin.things
  WHERE data ->> 'size' = 'large' AND data ->> 'stocked' = 'true';
 id | data 
----+------
(0 rows)

-- WHERE with multiple json ->> conditions (AND).
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_things
  WHERE data ->> 'size' = 'large' AND data ->> 'stocked' = 'true';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.size = 'large')) AND ((data.stocked = 'true'))
(3 rows)

SELECT * FROM json_http.json_things
  WHERE data ->> 'size' = 'large' AND data ->> 'stocked' = 'true';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_things
  WHERE data ->> 'size' = 'large' AND data ->> 'stocked' = 'true';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((data.size = 'large')) AND ((data.stocked = 'true'))
(3 rows)

SELECT * FROM json_bin.json_things
  WHERE data ->> 'size' = 'large' AND data ->> 'stocked' = 'true';
 id | data 
----+------
(0 rows)

-- WHERE with jsonb ->> in an OR condition.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.things
  WHERE data ->> 'name' = 'widget' OR data ->> 'name' = 'gizmo'
  ORDER BY id;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE (((data.name = 'widget') OR (data.name = 'gizmo'))) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT * FROM json_http.things
  WHERE data ->> 'name' = 'widget' OR data ->> 'name' = 'gizmo'
  ORDER BY id;
 id |                              data                               
----+-----------------------------------------------------------------
  3 | {"id": "3", "name": "gizmo", "size": "medium", "stocked": true}
(1 row)

SELECT * FROM json_bin.things
  WHERE data ->> 'name' = 'widget' OR data ->> 'name' = 'gizmo'
  ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE (((data.name = 'widget') OR (data.name = 'gizmo'))) ORDER BY id ASC NULLS LAST
SELECT * FROM json_bin.things
  WHERE data ->> 'name' = 'widget' OR data ->> 'name' = 'gizmo'
  ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE (((data.name = 'widget') OR (data.name = 'gizmo'))) ORDER BY id ASC NULLS LAST
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_things
  WHERE data ->> 'name' = 'widget' OR data ->> 'name' = 'gizmo'
  ORDER BY id;
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE (((data.name = 'widget') OR (data.name = 'gizmo'))) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT * FROM json_http.json_things
  WHERE data ->> 'name' = 'widget' OR data ->> 'name' = 'gizmo'
  ORDER BY id;
 id |                           data                           
----+----------------------------------------------------------
  3 | {"id":"3","name":"gizmo","size":"medium","stocked":true}
(1 row)

SELECT * FROM json_bin.json_things
  WHERE data ->> 'name' = 'widget' OR data ->> 'name' = 'gizmo'
  ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE (((data.name = 'widget') OR (data.name = 'gizmo'))) ORDER BY id ASC NULLS LAST
SELECT * FROM json_bin.json_things
  WHERE data ->> 'name' = 'widget' OR data ->> 'name' = 'gizmo'
  ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE (((data.name = 'widget') OR (data.name = 'gizmo'))) ORDER BY id ASC NULLS LAST
-- ORDER BY with jsonb ->> pushdown.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.things ORDER BY data ->> 'name';
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Foreign Scan on json_http.things
   Output: id, data, (data ->> 'name'::text)
   Remote SQL: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST
(3 rows)

SELECT * FROM json_http.things ORDER BY data ->> 'name';
 id |                               data                               
----+------------------------------------------------------------------
  6 | {"id": "6", "name": "curio", "size": "medium", "stocked": false}
  4 | {"id": "4", "name": "doodad", "size": "large", "stocked": false}
  3 | {"id": "3", "name": "gizmo", "size": "medium", "stocked": true}
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.things ORDER BY data ->> 'name';
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Foreign Scan on json_bin.things
   Output: id, data, (data ->> 'name'::text)
   Remote SQL: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST
(3 rows)

SELECT * FROM json_bin.things ORDER BY data ->> 'name';
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST
SET pg_clickhouse.session_settings TO 'allow_suspicious_types_in_order_by 1';
SELECT * FROM json_http.things ORDER BY data ->> 'name' LIMIT 2;
ERROR:  pg_clickhouse: Code: 115. DB::Exception: Setting allow_suspicious_types_in_order_by is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings. (UNKNOWN_SETTING)
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST LIMIT 2
CONTEXT:  HTTP status code: 404
SELECT * FROM json_bin.things ORDER BY data ->> 'name' LIMIT 2;
ERROR:  pg_clickhouse: DB::Exception: Unknown setting 'allow_suspicious_types_in_order_by'
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST LIMIT 2
SET pg_clickhouse.session_settings TO '';
-- ORDER BY with json ->> pushdown.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_things ORDER BY data ->> 'name';
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_things
   Output: id, data, (data ->> 'name'::text)
   Remote SQL: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST
(3 rows)

SELECT * FROM json_http.json_things ORDER BY data ->> 'name';
 id |                           data                            
----+-----------------------------------------------------------
  6 | {"id":"6","name":"curio","size":"medium","stocked":false}
  4 | {"id":"4","name":"doodad","size":"large","stocked":false}
  3 | {"id":"3","name":"gizmo","size":"medium","stocked":true}
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_things ORDER BY data ->> 'name';
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_things
   Output: id, data, (data ->> 'name'::text)
   Remote SQL: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST
(3 rows)

SELECT * FROM json_bin.json_things ORDER BY data ->> 'name';
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST
SET pg_clickhouse.session_settings TO 'allow_suspicious_types_in_order_by 1';
SELECT * FROM json_http.json_things ORDER BY data ->> 'name' LIMIT 2;
ERROR:  pg_clickhouse: Code: 115. DB::Exception: Setting allow_suspicious_types_in_order_by is neither a builtin setting nor started with the prefix 'SQL_' registered for user-defined settings. (UNKNOWN_SETTING)
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST LIMIT 2
CONTEXT:  HTTP status code: 404
SELECT * FROM json_bin.json_things ORDER BY data ->> 'name' LIMIT 2;
ERROR:  pg_clickhouse: DB::Exception: Unknown setting 'allow_suspicious_types_in_order_by'
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY data.name ASC NULLS LAST LIMIT 2
SET pg_clickhouse.session_settings TO '';
-- The jsonb -> operator runs locally when used in SELECT target lists.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT data -> 'name' FROM json_http.things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_http.things
   Output: (data -> 'name'::text)
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data -> 'name' FROM json_http.things ORDER BY id;
 ?column? 
----------
 "gizmo"
 "doodad"
 "curio"
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT data -> 'name' FROM json_bin.things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_bin.things
   Output: (data -> 'name'::text)
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data -> 'name' FROM json_bin.things ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY id ASC NULLS LAST
EXPLAIN (VERBOSE, COSTS OFF)
SELECT data -> 'name' FROM json_http.json_things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_http.json_things
   Output: (data -> 'name'::text)
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data -> 'name' FROM json_http.json_things ORDER BY id;
 ?column? 
----------
 "gizmo"
 "doodad"
 "curio"
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT data -> 'name' FROM json_bin.json_things;
                   QUERY PLAN                    
-------------------------------------------------
 Foreign Scan on json_bin.json_things
   Output: (data -> 'name'::text)
   Remote SQL: SELECT data FROM json_test.things
(3 rows)

SELECT data -> 'name' FROM json_bin.json_things ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things ORDER BY id ASC NULLS LAST
-- WHERE clause with jsonb -> equality must be pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.things WHERE data -> 'name' = '"widget"';
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((toJSONString(data.name) = '"widget"'))
(3 rows)

SELECT * FROM json_http.things WHERE data -> 'name' = '"widget"';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.things WHERE data -> 'name' = '"widget"';
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((toJSONString(data.name) = '"widget"'))
(3 rows)

SELECT * FROM json_bin.things WHERE data -> 'name' = '"widget"';
 id | data 
----+------
(0 rows)

-- WHERE clause with json -> cast to text must be pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_things WHERE (data -> 'name')::text = '"widget"';
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((CAST(toJSONString(data.name) AS String) = '"widget"'))
(3 rows)

SELECT * FROM json_http.json_things WHERE (data -> 'name')::text = '"widget"';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_things WHERE (data -> 'name')::text = '"widget"';
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((CAST(toJSONString(data.name) AS String) = '"widget"'))
(3 rows)

SELECT * FROM json_bin.json_things WHERE (data -> 'name')::text = '"widget"';
 id | data 
----+------
(0 rows)

-- WHERE clause with jsonb -> JSON boolean literal must push down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.things WHERE data -> 'stocked' = 'true'::jsonb;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((toJSONString(data.stocked) = 'true'))
(3 rows)

SELECT * FROM json_http.things WHERE data -> 'stocked' = 'true'::jsonb ORDER BY id;
 id |                              data                               
----+-----------------------------------------------------------------
  3 | {"id": "3", "name": "gizmo", "size": "medium", "stocked": true}
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.things WHERE data -> 'stocked' = 'true'::jsonb;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((toJSONString(data.stocked) = 'true'))
(3 rows)

SELECT * FROM json_bin.things WHERE data -> 'stocked' = 'true'::jsonb ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE ((toJSONString(data.stocked) = 'true')) ORDER BY id ASC NULLS LAST
-- WHERE clause with json -> JSON boolean literal must push down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_things WHERE (data -> 'stocked')::text = 'true';
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((CAST(toJSONString(data.stocked) AS String) = 'true'))
(3 rows)

SELECT * FROM json_http.json_things WHERE (data -> 'stocked')::text = 'true' ORDER BY id;
 id |                           data                           
----+----------------------------------------------------------
  3 | {"id":"3","name":"gizmo","size":"medium","stocked":true}
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_things WHERE (data -> 'stocked')::text = 'true';
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((CAST(toJSONString(data.stocked) AS String) = 'true'))
(3 rows)

SELECT * FROM json_bin.json_things WHERE (data -> 'stocked')::text = 'true' ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.things WHERE ((CAST(toJSONString(data.stocked) AS String) = 'true')) ORDER BY id ASC NULLS LAST
-- WHERE clause with jsonb -> wraps the dot notation in toJSONString() so the
-- result is a proper JSON value (->, unlike ->>, returns jsonb).
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.things WHERE data -> 'name' = '"widget"'::jsonb;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((toJSONString(data.name) = '"widget"'))
(3 rows)

SELECT * FROM json_http.things WHERE data -> 'name' = '"widget"'::jsonb;
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.things WHERE data -> 'name' = '"widget"'::jsonb;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((toJSONString(data.name) = '"widget"'))
(3 rows)

SELECT * FROM json_bin.things WHERE data -> 'name' = '"widget"'::jsonb;
 id | data 
----+------
(0 rows)

-- WHERE clause with json -> wraps the dot notation in toJSONString() so the
-- result is a proper JSON value (->, unlike ->>, returns json).
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.things WHERE (data -> 'name')::text = '"widget"';
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((CAST(toJSONString(data.name) AS String) = '"widget"'))
(3 rows)

SELECT * FROM json_http.things WHERE (data -> 'name')::text = '"widget"';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.things WHERE (data -> 'name')::text = '"widget"';
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.things
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.things WHERE ((CAST(toJSONString(data.name) AS String) = '"widget"'))
(3 rows)

SELECT * FROM json_bin.things WHERE (data -> 'name')::text = '"widget"';
 id | data 
----+------
(0 rows)

-- Edge cases: JSON keys that require identifier quoting.
SELECT clickhouse_raw_query($$
    CREATE TABLE json_test.special_keys (
        id   Int32 NOT NULL,
        data JSON NOT NULL
    ) ENGINE = MergeTree ORDER BY (id);
$$);
 clickhouse_raw_query 
----------------------
 
(1 row)

CREATE FOREIGN TABLE json_http.special_keys (id integer NOT NULL, data jsonb NOT NULL)
  SERVER http_json_loopback OPTIONS (database 'json_test', table_name 'special_keys');
CREATE FOREIGN TABLE json_bin.special_keys (id integer NOT NULL, data jsonb NOT NULL)
  SERVER binary_json_loopback OPTIONS (database 'json_test', table_name 'special_keys');
INSERT INTO json_http.special_keys
VALUES (1, '{"my field": "hello", "CamelCase": "world", "select": "reserved"}');
INSERT INTO json_bin.special_keys
VALUES (2, E'{"The \\"meaning\\" of life": 42, "back\\\\slash": "bs", "dotted.key": "dot", "it''s": "apos", "key/with!special@chars#": "special", "123numeric": "num"}');
ERROR:  pg_clickhouse: could not finish INSERT - DB::Exception: Invalid version for Object structure serialization.
CREATE FOREIGN TABLE json_http.json_special_keys (id integer NOT NULL, data json NOT NULL)
  SERVER http_json_loopback OPTIONS (database 'json_test', table_name 'special_keys');
CREATE FOREIGN TABLE json_bin.json_special_keys (id integer NOT NULL, data json NOT NULL)
  SERVER binary_json_loopback OPTIONS (database 'json_test', table_name 'special_keys');
-- Key with a space: must be quoted in the remote SQL.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.special_keys WHERE data ->> 'my field' = 'hello';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_http.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."my field" = 'hello'))
(3 rows)

SELECT * FROM json_http.special_keys WHERE data ->> 'my field' = 'hello';
 id |                               data                                
----+-------------------------------------------------------------------
  1 | {"select": "reserved", "my field": "hello", "CamelCase": "world"}
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.special_keys WHERE data ->> 'my field' = 'hello';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."my field" = 'hello'))
(3 rows)

SELECT * FROM json_bin.special_keys WHERE data ->> 'my field' = 'hello';
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.special_keys WHERE ((data."my field" = 'hello'))
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_special_keys WHERE data ->> 'my field' = 'hello';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."my field" = 'hello'))
(3 rows)

SELECT * FROM json_http.json_special_keys WHERE data ->> 'my field' = 'hello';
 id |                             data                             
----+--------------------------------------------------------------
  1 | {"CamelCase":"world","my field":"hello","select":"reserved"}
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_special_keys WHERE data ->> 'my field' = 'hello';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."my field" = 'hello'))
(3 rows)

SELECT * FROM json_bin.json_special_keys WHERE data ->> 'my field' = 'hello';
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.special_keys WHERE ((data."my field" = 'hello'))
-- Key with mixed case.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.special_keys WHERE data ->> 'CamelCase' = 'world';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."CamelCase" = 'world'))
(3 rows)

SELECT * FROM json_http.special_keys WHERE data ->> 'CamelCase' = 'world';
 id |                               data                                
----+-------------------------------------------------------------------
  1 | {"select": "reserved", "my field": "hello", "CamelCase": "world"}
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.special_keys WHERE data ->> 'CamelCase' = 'world';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."CamelCase" = 'world'))
(3 rows)

SELECT * FROM json_bin.special_keys WHERE data ->> 'CamelCase' = 'world';
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.special_keys WHERE ((data."CamelCase" = 'world'))
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_special_keys WHERE data ->> 'CamelCase' = 'world';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."CamelCase" = 'world'))
(3 rows)

SELECT * FROM json_http.json_special_keys WHERE data ->> 'CamelCase' = 'world';
 id |                             data                             
----+--------------------------------------------------------------
  1 | {"CamelCase":"world","my field":"hello","select":"reserved"}
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_special_keys WHERE data ->> 'CamelCase' = 'world';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."CamelCase" = 'world'))
(3 rows)

SELECT * FROM json_bin.json_special_keys WHERE data ->> 'CamelCase' = 'world';
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.special_keys WHERE ((data."CamelCase" = 'world'))
-- Key that is a SQL reserved word.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.special_keys WHERE data ->> 'select' = 'reserved';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."select" = 'reserved'))
(3 rows)

SELECT * FROM json_http.special_keys WHERE data ->> 'select' = 'reserved';
 id |                               data                                
----+-------------------------------------------------------------------
  1 | {"select": "reserved", "my field": "hello", "CamelCase": "world"}
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.special_keys WHERE data ->> 'select' = 'reserved';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."select" = 'reserved'))
(3 rows)

SELECT * FROM json_bin.special_keys WHERE data ->> 'select' = 'reserved';
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.special_keys WHERE ((data."select" = 'reserved'))
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_special_keys WHERE data ->> 'select' = 'reserved';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."select" = 'reserved'))
(3 rows)

SELECT * FROM json_http.json_special_keys WHERE data ->> 'select' = 'reserved';
 id |                             data                             
----+--------------------------------------------------------------
  1 | {"CamelCase":"world","my field":"hello","select":"reserved"}
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_special_keys WHERE data ->> 'select' = 'reserved';
                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."select" = 'reserved'))
(3 rows)

SELECT * FROM json_bin.json_special_keys WHERE data ->> 'select' = 'reserved';
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, data FROM json_test.special_keys WHERE ((data."select" = 'reserved'))
-- Key containing embedded double quotes.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.special_keys WHERE data ->> 'The "meaning" of life' = '42';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."The ""meaning"" of life" = '42'))
(3 rows)

SELECT * FROM json_http.special_keys WHERE data ->> 'The "meaning" of life' = '42';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.special_keys WHERE data ->> 'The "meaning" of life' = '42';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."The ""meaning"" of life" = '42'))
(3 rows)

SELECT * FROM json_bin.special_keys WHERE data ->> 'The "meaning" of life' = '42';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_special_keys WHERE data ->> 'The "meaning" of life' = '42';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."The ""meaning"" of life" = '42'))
(3 rows)

SELECT * FROM json_http.json_special_keys WHERE data ->> 'The "meaning" of life' = '42';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_special_keys WHERE data ->> 'The "meaning" of life' = '42';
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."The ""meaning"" of life" = '42'))
(3 rows)

SELECT * FROM json_bin.json_special_keys WHERE data ->> 'The "meaning" of life' = '42';
 id | data 
----+------
(0 rows)

-- Key containing a backslash.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.special_keys WHERE data ->> 'back\slash' = 'bs';
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Foreign Scan on json_http.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."back\slash" = 'bs'))
(3 rows)

SELECT * FROM json_http.special_keys WHERE data ->> 'back\slash' = 'bs';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.special_keys WHERE data ->> 'back\slash' = 'bs';
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."back\slash" = 'bs'))
(3 rows)

SELECT * FROM json_bin.special_keys WHERE data ->> 'back\slash' = 'bs';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_special_keys WHERE data ->> 'back\slash' = 'bs';
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."back\slash" = 'bs'))
(3 rows)

SELECT * FROM json_http.json_special_keys WHERE data ->> 'back\slash' = 'bs';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_special_keys WHERE data ->> 'back\slash' = 'bs';
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."back\slash" = 'bs'))
(3 rows)

SELECT * FROM json_bin.json_special_keys WHERE data ->> 'back\slash' = 'bs';
 id | data 
----+------
(0 rows)

-- Key containing a dot (must not be confused with nested access).
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.special_keys WHERE data ->> 'dotted.key' = 'dot';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_http.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."dotted.key" = 'dot'))
(3 rows)

SELECT * FROM json_http.special_keys WHERE data ->> 'dotted.key' = 'dot';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.special_keys WHERE data ->> 'dotted.key' = 'dot';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."dotted.key" = 'dot'))
(3 rows)

SELECT * FROM json_bin.special_keys WHERE data ->> 'dotted.key' = 'dot';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_special_keys WHERE data ->> 'dotted.key' = 'dot';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."dotted.key" = 'dot'))
(3 rows)

SELECT * FROM json_http.json_special_keys WHERE data ->> 'dotted.key' = 'dot';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_special_keys WHERE data ->> 'dotted.key' = 'dot';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."dotted.key" = 'dot'))
(3 rows)

SELECT * FROM json_bin.json_special_keys WHERE data ->> 'dotted.key' = 'dot';
 id | data 
----+------
(0 rows)

-- Key containing an apostrophe / single quote.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.special_keys WHERE data ->> 'it''s' = 'apos';
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Foreign Scan on json_http.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."it's" = 'apos'))
(3 rows)

SELECT * FROM json_http.special_keys WHERE data ->> 'it''s' = 'apos';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.special_keys WHERE data ->> 'it''s' = 'apos';
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."it's" = 'apos'))
(3 rows)

SELECT * FROM json_bin.special_keys WHERE data ->> 'it''s' = 'apos';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_special_keys WHERE data ->> 'it''s' = 'apos';
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."it's" = 'apos'))
(3 rows)

SELECT * FROM json_http.json_special_keys WHERE data ->> 'it''s' = 'apos';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_special_keys WHERE data ->> 'it''s' = 'apos';
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."it's" = 'apos'))
(3 rows)

SELECT * FROM json_bin.json_special_keys WHERE data ->> 'it''s' = 'apos';
 id | data 
----+------
(0 rows)

-- Key with slashes, bangs, at-signs, etc.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.special_keys WHERE data ->> 'key/with!special@chars#' = 'special';
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."key/with!special@chars#" = 'special'))
(3 rows)

SELECT * FROM json_http.special_keys WHERE data ->> 'key/with!special@chars#' = 'special';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.special_keys WHERE data ->> 'key/with!special@chars#' = 'special';
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."key/with!special@chars#" = 'special'))
(3 rows)

SELECT * FROM json_bin.special_keys WHERE data ->> 'key/with!special@chars#' = 'special';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_special_keys WHERE data ->> 'key/with!special@chars#' = 'special';
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."key/with!special@chars#" = 'special'))
(3 rows)

SELECT * FROM json_http.json_special_keys WHERE data ->> 'key/with!special@chars#' = 'special';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_special_keys WHERE data ->> 'key/with!special@chars#' = 'special';
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."key/with!special@chars#" = 'special'))
(3 rows)

SELECT * FROM json_bin.json_special_keys WHERE data ->> 'key/with!special@chars#' = 'special';
 id | data 
----+------
(0 rows)

-- Key that starts with a digit.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.special_keys WHERE data ->> '123numeric' = 'num';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_http.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."123numeric" = 'num'))
(3 rows)

SELECT * FROM json_http.special_keys WHERE data ->> '123numeric' = 'num';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.special_keys WHERE data ->> '123numeric' = 'num';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."123numeric" = 'num'))
(3 rows)

SELECT * FROM json_bin.special_keys WHERE data ->> '123numeric' = 'num';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_http.json_special_keys WHERE data ->> '123numeric' = 'num';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."123numeric" = 'num'))
(3 rows)

SELECT * FROM json_http.json_special_keys WHERE data ->> '123numeric' = 'num';
 id | data 
----+------
(0 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM json_bin.json_special_keys WHERE data ->> '123numeric' = 'num';
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_special_keys
   Output: id, data
   Remote SQL: SELECT id, data FROM json_test.special_keys WHERE ((data."123numeric" = 'num'))
(3 rows)

SELECT * FROM json_bin.json_special_keys WHERE data ->> '123numeric' = 'num';
 id | data 
----+------
(0 rows)

-- =======================================================================
-- jsonb_extract_path_text / jsonb_extract_path pushdown
-- =======================================================================
-- Create a table with nested JSON for multi-level path tests.
SELECT clickhouse_raw_query($$
    CREATE TABLE json_test.events (
        id         UInt32,
        event_name String,
        props      JSON
    ) ENGINE = MergeTree ORDER BY (event_name, id);
$$);
 clickhouse_raw_query 
----------------------
 
(1 row)

SELECT clickhouse_raw_query($$
    INSERT INTO json_test.events VALUES
        (1, 'order', '{"customerId": "C100", "address": {"city": "Paris", "zip": "75001"}}'),
        (2, 'order', '{"customerId": "C200", "address": {"city": "London", "zip": "SW1A"}}');
$$);
 clickhouse_raw_query 
----------------------
 
(1 row)

CREATE FOREIGN TABLE json_http.events (
    id         integer,
    event_name text,
    props      jsonb
) SERVER http_json_loopback OPTIONS (table_name 'events');
CREATE FOREIGN TABLE json_bin.events (
    id         integer,
    event_name text,
    props      jsonb
) SERVER binary_json_loopback OPTIONS (table_name 'events');
-- Target-list: jsonb_extract_path_text is evaluated locally (like -> / ->>).
EXPLAIN (VERBOSE, COSTS OFF)
SELECT jsonb_extract_path_text(props, 'customerId') FROM json_http.events;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Foreign Scan on json_http.events
   Output: jsonb_extract_path_text(props, VARIADIC '{customerId}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT jsonb_extract_path_text(props, 'customerId') FROM json_http.events ORDER BY id;
 jsonb_extract_path_text 
-------------------------
 C100
 C200
(2 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT jsonb_extract_path_text(props, 'customerId') FROM json_bin.events;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Foreign Scan on json_bin.events
   Output: jsonb_extract_path_text(props, VARIADIC '{customerId}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT jsonb_extract_path_text(props, 'customerId') FROM json_bin.events ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, props FROM json_test.events ORDER BY id ASC NULLS LAST
-- Target-list: multi-level path, still evaluated locally.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT jsonb_extract_path_text(props, 'address', 'city') FROM json_http.events;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Foreign Scan on json_http.events
   Output: jsonb_extract_path_text(props, VARIADIC '{address,city}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT jsonb_extract_path_text(props, 'address', 'city') FROM json_http.events ORDER BY id;
 jsonb_extract_path_text 
-------------------------
 Paris
 London
(2 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT jsonb_extract_path_text(props, 'address', 'city') FROM json_bin.events;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Foreign Scan on json_bin.events
   Output: jsonb_extract_path_text(props, VARIADIC '{address,city}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT jsonb_extract_path_text(props, 'address', 'city') FROM json_bin.events ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, props FROM json_test.events ORDER BY id ASC NULLS LAST
-- Target-list: jsonb_extract_path (returns jsonb, not text), evaluated locally.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT jsonb_extract_path(props, 'address') FROM json_http.events;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Foreign Scan on json_http.events
   Output: jsonb_extract_path(props, VARIADIC '{address}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT jsonb_extract_path(props, 'address') FROM json_http.events;
        jsonb_extract_path         
-----------------------------------
 {"zip": "75001", "city": "Paris"}
 {"zip": "SW1A", "city": "London"}
(2 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT jsonb_extract_path(props, 'address') FROM json_bin.events;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Foreign Scan on json_bin.events
   Output: jsonb_extract_path(props, VARIADIC '{address}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT jsonb_extract_path(props, 'address') FROM json_bin.events;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT props FROM json_test.events
-- WHERE: single-level jsonb_extract_path_text pushes down as dot notation.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_http.events WHERE jsonb_extract_path_text(props, 'customerId') = 'C100';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Foreign Scan on json_http.events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((props."customerId" = 'C100'))
(3 rows)

SELECT id FROM json_http.events WHERE jsonb_extract_path_text(props, 'customerId') = 'C100';
 id 
----
  1
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_bin.events WHERE jsonb_extract_path_text(props, 'customerId') = 'C100';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Foreign Scan on json_bin.events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((props."customerId" = 'C100'))
(3 rows)

SELECT id FROM json_bin.events WHERE jsonb_extract_path_text(props, 'customerId') = 'C100';
 id 
----
  1
(1 row)

-- WHERE: multi-level jsonb_extract_path_text pushes down as dot notation.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_http.events WHERE jsonb_extract_path_text(props, 'address', 'city') = 'Paris';
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Foreign Scan on json_http.events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((props.address.city = 'Paris'))
(3 rows)

SELECT id FROM json_http.events WHERE jsonb_extract_path_text(props, 'address', 'city') = 'Paris';
 id 
----
  1
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_bin.events WHERE jsonb_extract_path_text(props, 'address', 'city') = 'Paris';
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Foreign Scan on json_bin.events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((props.address.city = 'Paris'))
(3 rows)

SELECT id FROM json_bin.events WHERE jsonb_extract_path_text(props, 'address', 'city') = 'Paris';
 id 
----
  1
(1 row)

-- WHERE: jsonb_extract_path pushes down with toJSONString wrapping.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_http.events WHERE jsonb_extract_path(props, 'address', 'city') = '"Paris"'::jsonb;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((toJSONString(props.address.city) = '"Paris"'))
(3 rows)

SELECT id FROM json_http.events WHERE jsonb_extract_path(props, 'address', 'city') = '"Paris"'::jsonb;
 id 
----
  1
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_bin.events WHERE jsonb_extract_path(props, 'address', 'city') = '"Paris"'::jsonb;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((toJSONString(props.address.city) = '"Paris"'))
(3 rows)

SELECT id FROM json_bin.events WHERE jsonb_extract_path(props, 'address', 'city') = '"Paris"'::jsonb;
 id 
----
  1
(1 row)

-- =======================================================================
-- json_extract_path_text / json_extract_path pushdown
-- =======================================================================
CREATE FOREIGN TABLE json_http.json_events (
    id         integer,
    event_name text,
    props      json
) SERVER http_json_loopback OPTIONS (table_name 'events');
CREATE FOREIGN TABLE json_bin.json_events (
    id         integer,
    event_name text,
    props      json
) SERVER binary_json_loopback OPTIONS (table_name 'events');
-- Target-list: json_extract_path_text is evaluated locally (like -> / ->>).
EXPLAIN (VERBOSE, COSTS OFF)
SELECT json_extract_path_text(props, 'customerId') FROM json_http.json_events;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Foreign Scan on json_http.json_events
   Output: json_extract_path_text(props, VARIADIC '{customerId}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT json_extract_path_text(props, 'customerId') FROM json_http.json_events ORDER BY id;
 json_extract_path_text 
------------------------
 C100
 C200
(2 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT json_extract_path_text(props, 'customerId') FROM json_bin.json_events;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Foreign Scan on json_bin.json_events
   Output: json_extract_path_text(props, VARIADIC '{customerId}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT json_extract_path_text(props, 'customerId') FROM json_bin.json_events ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, props FROM json_test.events ORDER BY id ASC NULLS LAST
-- Target-list: multi-level path, still evaluated locally.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT json_extract_path_text(props, 'address', 'city') FROM json_http.json_events;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Foreign Scan on json_http.json_events
   Output: json_extract_path_text(props, VARIADIC '{address,city}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT json_extract_path_text(props, 'address', 'city') FROM json_http.json_events ORDER BY id;
 json_extract_path_text 
------------------------
 Paris
 London
(2 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT json_extract_path_text(props, 'address', 'city') FROM json_bin.json_events;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Foreign Scan on json_bin.json_events
   Output: json_extract_path_text(props, VARIADIC '{address,city}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT json_extract_path_text(props, 'address', 'city') FROM json_bin.json_events ORDER BY id;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT id, props FROM json_test.events ORDER BY id ASC NULLS LAST
-- Target-list: json_extract_path (returns json, not text), evaluated locally.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT json_extract_path(props, 'address') FROM json_http.json_events;
                            QUERY PLAN                            
------------------------------------------------------------------
 Foreign Scan on json_http.json_events
   Output: json_extract_path(props, VARIADIC '{address}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT json_extract_path(props, 'address') FROM json_http.json_events;
       json_extract_path        
--------------------------------
 {"city":"Paris","zip":"75001"}
 {"city":"London","zip":"SW1A"}
(2 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT json_extract_path(props, 'address') FROM json_bin.json_events;
                            QUERY PLAN                            
------------------------------------------------------------------
 Foreign Scan on json_bin.json_events
   Output: json_extract_path(props, VARIADIC '{address}'::text[])
   Remote SQL: SELECT props FROM json_test.events
(3 rows)

SELECT json_extract_path(props, 'address') FROM json_bin.json_events;
ERROR:  pg_clickhouse: Unsupported JSON serialization version. Make sure output_format_native_write_json_as_string=1 is set.
DETAIL:  Remote Query: SELECT props FROM json_test.events
-- WHERE: single-level json_extract_path_text pushes down as dot notation.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_http.json_events WHERE json_extract_path_text(props, 'customerId') = 'C100';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((props."customerId" = 'C100'))
(3 rows)

SELECT id FROM json_http.json_events WHERE json_extract_path_text(props, 'customerId') = 'C100';
 id 
----
  1
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_bin.json_events WHERE json_extract_path_text(props, 'customerId') = 'C100';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((props."customerId" = 'C100'))
(3 rows)

SELECT id FROM json_bin.json_events WHERE json_extract_path_text(props, 'customerId') = 'C100';
 id 
----
  1
(1 row)

-- WHERE: multi-level json_extract_path_text pushes down as dot notation.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_http.json_events WHERE json_extract_path_text(props, 'address', 'city') = 'Paris';
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((props.address.city = 'Paris'))
(3 rows)

SELECT id FROM json_http.json_events WHERE json_extract_path_text(props, 'address', 'city') = 'Paris';
 id 
----
  1
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_bin.json_events WHERE json_extract_path_text(props, 'address', 'city') = 'Paris';
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((props.address.city = 'Paris'))
(3 rows)

SELECT id FROM json_bin.json_events WHERE json_extract_path_text(props, 'address', 'city') = 'Paris';
 id 
----
  1
(1 row)

-- WHERE: json_extract_path pushes down with toJSONString wrapping.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_http.json_events WHERE json_extract_path(props, 'address', 'city')::text = '"Paris"';
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_http.json_events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((CAST(toJSONString(props.address.city) AS String) = '"Paris"'))
(3 rows)

SELECT id FROM json_http.json_events WHERE json_extract_path(props, 'address', 'city')::text = '"Paris"';
 id 
----
  1
(1 row)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id FROM json_bin.json_events WHERE json_extract_path(props, 'address', 'city')::text = '"Paris"';
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Foreign Scan on json_bin.json_events
   Output: id
   Remote SQL: SELECT id FROM json_test.events WHERE ((CAST(toJSONString(props.address.city) AS String) = '"Paris"'))
(3 rows)

SELECT id FROM json_bin.json_events WHERE json_extract_path(props, 'address', 'city')::text = '"Paris"';
 id 
----
  1
(1 row)

SELECT clickhouse_raw_query('DROP DATABASE json_test');
 clickhouse_raw_query 
----------------------
 
(1 row)

DROP USER MAPPING FOR CURRENT_USER SERVER binary_json_loopback;
DROP USER MAPPING FOR CURRENT_USER SERVER http_json_loopback;
DROP SERVER binary_json_loopback CASCADE;
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to foreign table json_bin.things
drop cascades to foreign table json_bin.json_things
drop cascades to foreign table json_bin.special_keys
drop cascades to foreign table json_bin.json_special_keys
drop cascades to foreign table json_bin.events
drop cascades to foreign table json_bin.json_events
DROP SERVER http_json_loopback CASCADE;
NOTICE:  drop cascades to 6 other objects
DETAIL:  drop cascades to foreign table json_http.things
drop cascades to foreign table json_http.json_things
drop cascades to foreign table json_http.special_keys
drop cascades to foreign table json_http.json_special_keys
drop cascades to foreign table json_http.events
drop cascades to foreign table json_http.json_events