Better support for JSONB

[JelteF]

Description

We currently only support the Postgres json type, not the jsonb type. The jsonb type is much more common in practice in Postgres though, so we should support it. Related to #513.

• 0. Conversion between JSONB type and DuckDB (JSONB support #513)
• 1. Start supporting some of duckdb its JSON functions (Added support for json functions #546)
• 2. Introduce some of the JSONB operators that postgres supports to DuckDB.
• 3. Introduce a PG compatibility mode in duckdb to make existing operators behave exactly the same.

[ritwizsinha]

Taking this up

[ritwizsinha]

Next in line

Next steps in my order of priority would be:

1. Start supporting some of duckdb its JSON functions
2. Introduce some of the JSONB operators that postgres supports to DuckDB.
3. Introduce a PG compatibility mode in duckdb to make existing operators behave exactly the same.

[JelteF]

Item 1 was also requested now in #533, specifically support for the json_object_array aggregate.

[JelteF]

I think with #513 and #546 we now did all the low-hanging fruit and have basic support for JSON/JSONB support. I updated the description with the next steps, but those require some more thinking on the good way to handle them. They might also require DuckDB changes.

[jp-agenta]

Hey, hi !
First of all, kudos on pg_duckdb.
Second, JSONB support is a must for us and any large-scale-observability-on-postgres-with-duck-db kind of work.
We'll be waiting for this to be resolved before we move our GB/TBs to pg_duckdb.

[JelteF]

can you clarify what specific things you're currently missing? You can read jsonb columns just fine, you'll simply need to use the duckdb functions instead of the Postgres jsonb functions to extract data from it.