json_array_elements() throwing error when run from pg_scanner

[sudhirkenguva]

What happens?

When I am running the following command from the Python file, it's throwing the error.
------------------------------CODE-----------------------
file name: a.py

import duckdb
cursor = duckdb.connect()
cursor.execute('LOAD postgres')
query = f"""select json_array_elements(F.A -> 'B' -> 'C') ->> 'id' as cid 
from postgres_scan('dbname={db} user={user} password={password} host={host} port={prot}', {schema}, {table}) F"""
cursor.execute(f"""copy({query}) to 'parquetFiles/{table}.parquet' (FORMAT PARQUET)""")

------------------------------ERROR-----------------------
duckdb.CatalogException: Catalog Error: Scalar Function with name json_array_elements does not exist!

To Reproduce

python3 a.py

OS:

linux

PostgreSQL Version:

PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by Debian clang version 12.0.1, 64-bit

DuckDB Version:

duckdb-0.8.0-cp39-cp39-manylinux_2_17_x86_64.manylinux2014_x86_64.whl

DuckDB Client:

Python

Full Name:

Sudhir Kenguva

Affiliation:

DuckDB

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[Mause]

This error is expected, as DuckDB does not have a function named json_array_elements(). You may want to look at the functions we do support via the json extension: https://duckdb.org/docs/extensions/json