Feature Request: json_contains function support #978

Matt343 · 2025-11-21T00:24:39Z

Matt343
Nov 21, 2025

The json_contains function family is missing from the implementation in pg_duckdb.

JelteF · 2025-12-01T15:09:02Z

JelteF
Dec 1, 2025
Maintainer

Can you share the query + error message where it's not working? Because json_contains is actually implemented and tested:

pg_duckdb/test/regression/sql/json_functions_duckdb.sql

Lines 92 to 101 in b9ea050

    
           -- <JSON_CONTAINS> 
        
           -- Simple JSON array with numeric needle 
        
           SELECT public.json_contains('[1, 2, 3, 4]', '2') AS contains_numeric; -- Expected: TRUE 
        
           -- JSON object containing the needle as a value 
        
           SELECT public.json_contains('{"key1": "value1", "key2": 42}', '"value1"') AS contains_object_value; -- Expected: TRUE 
        
           -- </JSON_CONTAINS>

9 replies

Matt343 Dec 2, 2025
Author

Oh weird, the query works for me in psql, but not from the retool application. I wonder if this is some issue with the connector it's using?

# \dx pg_duckdb
                List of installed extensions
   Name    | Version | Schema |         Description
-----------+---------+--------+-----------------------------
 pg_duckdb | 1.0.0   | public | DuckDB Embedded in Postgres

# \dx+ pg_duckdb
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 access method duckdb
 cast from duckdb.unresolved_type to bigint
 cast from duckdb.unresolved_type to bigint[]
 cast from duckdb.unresolved_type to bit
 cast from duckdb.unresolved_type to bit[]
 cast from duckdb.unresolved_type to boolean
 cast from duckdb.unresolved_type to boolean[]
 cast from duckdb.unresolved_type to bytea
 cast from duckdb.unresolved_type to bytea[]
 cast from duckdb.unresolved_type to character
 cast from duckdb.unresolved_type to character varying
 cast from duckdb.unresolved_type to character varying[]
 cast from duckdb.unresolved_type to character[]
 cast from duckdb.unresolved_type to date
 cast from duckdb.unresolved_type to date[]
 cast from duckdb.unresolved_type to double precision
 cast from duckdb.unresolved_type to double precision[]
 cast from duckdb.unresolved_type to duckdb."json"
 cast from duckdb.unresolved_type to integer
 cast from duckdb.unresolved_type to integer[]
 cast from duckdb.unresolved_type to interval
 cast from duckdb.unresolved_type to interval[]
 cast from duckdb.unresolved_type to json
 cast from duckdb.unresolved_type to json[]
 cast from duckdb.unresolved_type to jsonb
 cast from duckdb.unresolved_type to jsonb[]
 cast from duckdb.unresolved_type to numeric
 cast from duckdb.unresolved_type to numeric[]
 cast from duckdb.unresolved_type to real
 cast from duckdb.unresolved_type to real[]
 cast from duckdb.unresolved_type to smallint
 cast from duckdb.unresolved_type to smallint[]
 cast from duckdb.unresolved_type to text
 cast from duckdb.unresolved_type to text[]
 cast from duckdb.unresolved_type to time with time zone
 cast from duckdb.unresolved_type to time with time zone[]
 cast from duckdb.unresolved_type to time without time zone
 cast from duckdb.unresolved_type to time without time zone[]
 cast from duckdb.unresolved_type to timestamp with time zone
 cast from duckdb.unresolved_type to timestamp without time zone
 cast from duckdb.unresolved_type to timestamp without time zone[]
 cast from duckdb.unresolved_type to uuid
 cast from duckdb.unresolved_type to uuid[]
 cast from json to duckdb."json"
 cast from jsonb to duckdb."json"
 event trigger duckdb_alter_table_trigger
 event trigger duckdb_create_table_trigger
 event trigger duckdb_drop_trigger
 event trigger duckdb_grant_trigger
 foreign-data wrapper duckdb
 function "json"(duckdb."json")
 function "json_exists"(duckdb."json",character varying)
 function "json_value"(duckdb."json",bigint)
function "json_value"(duckdb."json",character varying)
 function "json_value"(duckdb."json",character varying[])
 function approx_count_distinct(anyelement)
 function approx_count_distinct_sfunc(bigint,anyelement)
 function arbitrary(duckdb.unresolved_type)
 function arg_max(duckdb.unresolved_type,"any")
 function arg_max(duckdb.unresolved_type,"any","any")
 function arg_max_null(duckdb.unresolved_type,"any")
 function arg_min(duckdb.unresolved_type,"any")
 function arg_min(duckdb.unresolved_type,"any","any")
 function arg_min_null(duckdb.unresolved_type,"any")
 function array_agg(duckdb.unresolved_type)
 function avg(duckdb.unresolved_type)
 function bit_and(duckdb.unresolved_type)
 function bit_or(duckdb.unresolved_type)
 function bit_xor(duckdb.unresolved_type)
 function bitstring_agg(duckdb.unresolved_type)
 function bool_and(duckdb.unresolved_type)
 function bool_or(duckdb.unresolved_type)
 function date_trunc(text,duckdb.unresolved_type)
 function delta_scan(text)
 function duckdb._alter_table_trigger()
 function duckdb._am_handler(internal)
 function duckdb._create_table_trigger()
 function duckdb._drop_trigger()
 function duckdb._grant_trigger()
 function duckdb._update_extensions_table_seq()
 function duckdb.autoload_extension(text,boolean)
 function duckdb.cache(text,text)
 function duckdb.create_azure_secret(text,text)
 function duckdb.create_simple_secret(text,text,text,text,text,text,text,text,text)
 function duckdb.enable_motherduck(text,text)
 function duckdb.fdw_handler()
 function duckdb.fdw_validator(text[],oid)
 function duckdb.force_motherduck_sync(boolean)
 function duckdb.install_extension(text,text)
 function duckdb.is_motherduck_enabled()
 function duckdb.json_in(cstring)
 function duckdb.json_out(duckdb."json")
 function duckdb.json_subscript(internal)
 function duckdb.load_extension(text)
 function duckdb.map_in(cstring)
 function duckdb.map_out(duckdb.map)
 function duckdb.map_subscript(internal)
 function duckdb.query(text)
 function duckdb.raw_query(text)
 function duckdb.recycle_ddb()
 function duckdb.row_in(cstring)
 function duckdb.row_out(duckdb."row")
 function duckdb.row_subscript(internal)
 function duckdb.struct_in(cstring)
 function duckdb.struct_out(duckdb.struct)
 function duckdb.struct_subscript(internal)
 function duckdb.time_bucket(interval,date)
 function duckdb.time_bucket(interval,date,date)
 function duckdb.time_bucket(interval,date,interval)
 function duckdb.time_bucket(interval,duckdb.unresolved_type)
function duckdb.time_bucket(interval,duckdb.unresolved_type,character varying)
 function duckdb.time_bucket(interval,duckdb.unresolved_type,date)
 function duckdb.time_bucket(interval,duckdb.unresolved_type,interval)
 function duckdb.time_bucket(interval,duckdb.unresolved_type,timestamp with time zone)
 function duckdb.time_bucket(interval,duckdb.unresolved_type,timestamp without time zone)
 function duckdb.time_bucket(interval,timestamp with time zone)
 function duckdb.time_bucket(interval,timestamp with time zone,character varying)
 function duckdb.time_bucket(interval,timestamp with time zone,interval)
 function duckdb.time_bucket(interval,timestamp with time zone,timestamp with time zone)
 function duckdb.time_bucket(interval,timestamp without time zone)
 function duckdb.time_bucket(interval,timestamp without time zone,interval)
 function duckdb.time_bucket(interval,timestamp without time zone,timestamp without time zone)
 function duckdb.union_in(cstring)
 function duckdb.union_out(duckdb."union")
 function duckdb.unresolved_type_btree_cmp(duckdb.unresolved_type,duckdb.unresolved_type)
 function duckdb.unresolved_type_final(duckdb.unresolved_type)
 function duckdb.unresolved_type_hash(duckdb.unresolved_type)
 function duckdb.unresolved_type_in(cstring)
 function duckdb.unresolved_type_operator("any",duckdb.unresolved_type)
 function duckdb.unresolved_type_operator(duckdb.unresolved_type)
 function duckdb.unresolved_type_operator(duckdb.unresolved_type,"any")
 function duckdb.unresolved_type_operator(duckdb.unresolved_type,duckdb.unresolved_type)
 function duckdb.unresolved_type_operator_bool("any",duckdb.unresolved_type)
 function duckdb.unresolved_type_operator_bool(duckdb.unresolved_type,"any")
 function duckdb.unresolved_type_operator_bool(duckdb.unresolved_type,duckdb.unresolved_type)
 function duckdb.unresolved_type_out(duckdb.unresolved_type)
 function duckdb.unresolved_type_state_trans(duckdb.unresolved_type,duckdb.unresolved_type)
 function duckdb.unresolved_type_state_trans(duckdb.unresolved_type,duckdb.unresolved_type,"any")
 function duckdb.unresolved_type_state_trans(duckdb.unresolved_type,duckdb.unresolved_type,"any","any")
 function duckdb.unresolved_type_subscript(internal)
 function duckdb.view(text,text,text,text)
 function epoch(date)
 function epoch(duckdb.unresolved_type)
 function epoch(interval)
 function epoch(time with time zone)
 function epoch(time without time zone)
 function epoch(timestamp with time zone)
 function epoch(timestamp without time zone)
 function epoch_ms(bigint)
 function epoch_ms(date)
 function epoch_ms(duckdb.unresolved_type)
 function epoch_ms(interval)
 function epoch_ms(time with time zone)
 function epoch_ms(time without time zone)
 function epoch_ms(timestamp with time zone)
 function epoch_ms(timestamp without time zone)
 function epoch_ns(date)
 function epoch_ns(duckdb.unresolved_type)
 function epoch_ns(interval)
 function epoch_ns(time with time zone)
 function epoch_ns(time without time zone)
 function epoch_ns(timestamp with time zone)
 function epoch_ns(timestamp without time zone)
 function epoch_us(date)
 function epoch_us(duckdb.unresolved_type)
function epoch_us(interval)
 function epoch_us(time with time zone)
 function epoch_us(time without time zone)
 function epoch_us(timestamp with time zone)
 function epoch_us(timestamp without time zone)
 function favg(duckdb.unresolved_type)
 function first(duckdb.unresolved_type)
 function from_json(duckdb."json",duckdb."json")
 function from_json_strict(duckdb."json",duckdb."json")
 function fsum(duckdb.unresolved_type)
 function geomean(duckdb.unresolved_type)
 function histogram(duckdb.unresolved_type)
 function histogram(duckdb.unresolved_type,"any")
 function histogram_exact(duckdb.unresolved_type,"any")
 function iceberg_metadata(text,boolean,text,boolean,text,text)
 function iceberg_scan(text,boolean,text,text,boolean,text,text)
 function iceberg_snapshots(text,text,boolean,text,text)
 function json_array_length(duckdb."json",character varying)
 function json_array_length(duckdb."json",character varying[])
 function json_contains(duckdb."json",duckdb."json")
 function json_extract(duckdb."json",bigint)
 function json_extract(duckdb."json",character varying)
 function json_extract(duckdb."json",character varying[])
 function json_extract_string(duckdb."json",bigint)
 function json_extract_string(duckdb."json",character varying)
 function json_extract_string(duckdb."json",character varying[])
 function json_group_array("any")
 function json_group_array_sfunc(json,"any")
 function json_group_object("any","any")
 function json_group_object_sfunc(json,"any","any")
 function json_group_structure(duckdb."json")
 function json_group_structure_sfunc(json,duckdb."json")
 function json_keys(duckdb."json",character varying)
 function json_keys(duckdb."json",character varying[])
 function json_structure(duckdb."json")
 function json_transform(duckdb."json",duckdb."json")
 function json_transform_strict(duckdb."json",duckdb."json")
 function json_type(duckdb."json",character varying)
 function json_type(duckdb."json",character varying[])
 function json_valid(duckdb."json")
 function last(duckdb.unresolved_type)
 function length(duckdb.unresolved_type)
 function list(duckdb.unresolved_type)
 function make_timestamp(bigint)
 function make_timestamp(duckdb.unresolved_type)
 function make_timestamptz(bigint)
 function make_timestamptz(duckdb.unresolved_type)
 function max(duckdb.unresolved_type)
 function max(duckdb.unresolved_type,"any")
 function max_by(duckdb.unresolved_type,"any")
 function max_by(duckdb.unresolved_type,"any","any")
 function min(duckdb.unresolved_type)
 function min(duckdb.unresolved_type,"any")
 function min_by(duckdb.unresolved_type,"any")
 function min_by(duckdb.unresolved_type,"any","any")
 function product(duckdb.unresolved_type)
function read_csv(text,boolean,boolean,boolean,text[],character varying,character varying,character varying,character varying,character varying,boolean,text[],boolean,boolean,boolean,bigint,text[],character varying,boolean,boolean,text[],boolea
n,character varying,bigint,character varying,bigint,character varying,text[],boolean)
 function read_csv(text[],boolean,boolean,boolean,text[],character varying,character varying,character varying,character varying,character varying,boolean,text[],boolean,boolean,boolean,bigint,text[],character varying,boolean,boolean,text[],bool
ean,character varying,bigint,character varying,bigint,character varying,text[],boolean)
 function read_json(text,boolean,character varying,character varying,character varying,boolean,bigint,integer,character varying,bigint,character varying,boolean)
 function read_json(text[],boolean,character varying,character varying,character varying,boolean,bigint,integer,character varying,bigint,character varying,boolean)
 function read_parquet(text,boolean,boolean,boolean,boolean,boolean)
 function read_parquet(text[],boolean,boolean,boolean,boolean,boolean)
 function regexp_replace(duckdb.unresolved_type,text,text)
 function regexp_replace(duckdb.unresolved_type,text,text,text)
 function strftime(date,text)
 function strftime(duckdb.unresolved_type,text)
 function strftime(timestamp with time zone,text)
 function strftime(timestamp without time zone,text)
 function string_agg(duckdb.unresolved_type,"any")
 function strptime(duckdb.unresolved_type,text)
 function strptime(duckdb.unresolved_type,text[])
 function strptime(text,text)
 function strptime(text,text[])
 function sum(duckdb.unresolved_type)
 function time_bucket(interval,date)
 function time_bucket(interval,date,date)
 function time_bucket(interval,date,interval)
 function time_bucket(interval,duckdb.unresolved_type)
 function time_bucket(interval,duckdb.unresolved_type,character varying)
 function time_bucket(interval,duckdb.unresolved_type,date)
 function time_bucket(interval,duckdb.unresolved_type,interval)
 function time_bucket(interval,duckdb.unresolved_type,timestamp with time zone)
 function time_bucket(interval,duckdb.unresolved_type,timestamp without time zone)
 function time_bucket(interval,timestamp with time zone)
 function time_bucket(interval,timestamp with time zone,character varying)
 function time_bucket(interval,timestamp with time zone,interval)
 function time_bucket(interval,timestamp with time zone,timestamp with time zone)
 function time_bucket(interval,timestamp without time zone)
 function time_bucket(interval,timestamp without time zone,interval)
 function time_bucket(interval,timestamp without time zone,timestamp without time zone)
 function union_extract(duckdb."union",text)
 function union_extract(duckdb.unresolved_type,text)
 function union_tag(duckdb."union")
 function union_tag(duckdb.unresolved_type)
 operator !~("any",duckdb.unresolved_type)
 operator !~(duckdb.unresolved_type,"any")
 operator !~(duckdb.unresolved_type,duckdb.unresolved_type)
 operator !~~("any",duckdb.unresolved_type)
 operator !~~(duckdb.unresolved_type,"any")
 operator !~~(duckdb.unresolved_type,duckdb.unresolved_type)
 operator !~~*("any",duckdb.unresolved_type)
 operator !~~*(duckdb.unresolved_type,"any")
 operator !~~*(duckdb.unresolved_type,duckdb.unresolved_type)
 operator *("any",duckdb.unresolved_type)
 operator *(duckdb.unresolved_type,"any")
 operator *(duckdb.unresolved_type,duckdb.unresolved_type)
 operator +("any",duckdb.unresolved_type)
operator +(NONE,duckdb.unresolved_type)
 operator +(duckdb.unresolved_type,"any")
 operator +(duckdb.unresolved_type,duckdb.unresolved_type)
 operator -("any",duckdb.unresolved_type)
 operator -(NONE,duckdb.unresolved_type)
 operator -(duckdb.unresolved_type,"any")
 operator -(duckdb.unresolved_type,duckdb.unresolved_type)
 operator /("any",duckdb.unresolved_type)
 operator /(duckdb.unresolved_type,"any")
 operator /(duckdb.unresolved_type,duckdb.unresolved_type)
 operator <("any",duckdb.unresolved_type)
 operator <(duckdb.unresolved_type,"any")
 operator <(duckdb.unresolved_type,duckdb.unresolved_type)
 operator <=("any",duckdb.unresolved_type)
 operator <=(duckdb.unresolved_type,"any")
 operator <=(duckdb.unresolved_type,duckdb.unresolved_type)
 operator <>("any",duckdb.unresolved_type)
 operator <>(duckdb.unresolved_type,"any")
 operator <>(duckdb.unresolved_type,duckdb.unresolved_type)
 operator =("any",duckdb.unresolved_type)
 operator =(duckdb.unresolved_type,"any")
 operator =(duckdb.unresolved_type,duckdb.unresolved_type)
 operator >("any",duckdb.unresolved_type)
 operator >(duckdb.unresolved_type,"any")
 operator >(duckdb.unresolved_type,duckdb.unresolved_type)
 operator >=("any",duckdb.unresolved_type)
 operator >=(duckdb.unresolved_type,"any")
 operator >=(duckdb.unresolved_type,duckdb.unresolved_type)
 operator class duckdb.unresolved_type_ops for access method btree
 operator class duckdb_unresolved_type_hash_ops for access method hash
 operator family duckdb.unresolved_type_ops for access method btree
 operator family duckdb_unresolved_type_hash_ops for access method hash
 operator ~("any",duckdb.unresolved_type)
 operator ~(duckdb.unresolved_type,"any")
 operator ~(duckdb.unresolved_type,duckdb.unresolved_type)
 operator ~~("any",duckdb.unresolved_type)
 operator ~~(duckdb.unresolved_type,"any")
 operator ~~(duckdb.unresolved_type,duckdb.unresolved_type)
 operator ~~*("any",duckdb.unresolved_type)
 operator ~~*(duckdb.unresolved_type,"any")
 operator ~~*(duckdb.unresolved_type,duckdb.unresolved_type)
 schema duckdb
 sequence duckdb.extensions_table_seq
 table duckdb.extensions
 table duckdb.tables
 type blob
 type blob[]
 type double
 type duckdb."json"
 type duckdb."json"[]
 type duckdb."row"
 type duckdb."row"[]
 type duckdb."union"
 type duckdb."union"[]
 type duckdb.extensions
 type duckdb.extensions[]
type duckdb.iceberg_metadata_record
 type duckdb.iceberg_metadata_record[]
 type duckdb.iceberg_snapshots_record
 type duckdb.iceberg_snapshots_record[]
 type duckdb.map
 type duckdb.map[]
 type duckdb.struct
 type duckdb.struct[]
 type duckdb.tables
 type duckdb.tables[]
 type duckdb.unresolved_type
 type duckdb.unresolved_type[]

JelteF Dec 2, 2025
Maintainer

That output looks good.

Some sanity checks: Are you using the same connection string in retool and psql? Maybe a different user or something? Or you're connecting to a different database?

Matt343 Dec 2, 2025
Author

Ah there we go, if I log in with the retool user the query fails with the same error. The output of the \dx commands looks the same though, and this user is a superuser with pg_read_all_data assigned. Is there a more specific permission I need to grant it?

JelteF Dec 2, 2025
Maintainer

maybe the public schema is not in that user its search_path?

Matt343 Dec 2, 2025
Author

Aha! yeah you're right I had changed the search path for this user, setting it back to public fixed the issue. Thanks for the help!

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Feature Request: json_contains function support #978

Uh oh!

{{title}}

Uh oh!

Replies: 1 comment 9 replies

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{editor}}'s edit

{{editor}}'s edit

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Uh oh!

{{title}}

Uh oh!

Select a reply

Uh oh!

Feature Request: json_contains function support #978

Uh oh!

Matt343 Nov 21, 2025

Replies: 1 comment · 9 replies

Uh oh!

Uh oh!

JelteF Dec 1, 2025 Maintainer

Uh oh!

Matt343 Dec 2, 2025 Author

Uh oh!

JelteF Dec 2, 2025 Maintainer

Uh oh!

Matt343 Dec 2, 2025 Author

Uh oh!

JelteF Dec 2, 2025 Maintainer

Uh oh!

Matt343 Dec 2, 2025 Author

Matt343
Nov 21, 2025

Replies: 1 comment 9 replies

JelteF
Dec 1, 2025
Maintainer

Matt343 Dec 2, 2025
Author

JelteF Dec 2, 2025
Maintainer

Matt343 Dec 2, 2025
Author

JelteF Dec 2, 2025
Maintainer

Matt343 Dec 2, 2025
Author