JSON support in WASM issues (and not on par with DuckDB JSON extension)

[pmm-motif]

I have several use-cases which require using JSON input and every now and then I stumble upon some problems, for which the simplest workaround is going to DuckDB standalone, loading JSON and exporting to Parquet, which can be then loaded by DuckDB WASM without issues.

I filled a bug for TIMESTAMP parsing few weeks ago (many thanks for fixing it so quickly, looking forward to next release). More recently though, I got into issues with reading JSON Lines. Following example taken from the website:

{"name": "Gilbert", "wins": [["straight", "7♣"], ["one pair", "10♥"]]}
{"name": "Alexa", "wins": [["two pair", "4♠"], ["two pair", "9♠"]]}
{"name": "May", "wins": []}
{"name": "Deloise", "wins": [["three of a kind", "5♣"]]}

It works just fine via DuckDB standalone and JSON extension, i.e.:

D select * from 'jsonlines.json';
┌─────────┬───────────────────────────────────┐
│  name   │               wins                │
│ varchar │            varchar[][]            │
├─────────┼───────────────────────────────────┤
│ Gilbert │ [[straight, 7♣], [one pair, 10♥]] │
│ Alexa   │ [[two pair, 4♠], [two pair, 9♠]]  │
│ May     │ []                                │
│ Deloise │ [[three of a kind, 5♣]]           │
└─────────┴───────────────────────────────────┘

While DuckDB WASM gives this:

Error: Table type not specified.

I think that JSON Analyzer does not consider it a valid format. There are no test cases for such format either. I would argue it's a pretty common way to send JSON data though (e.g. BigQuery outputs data that way).

I am just wondering at this point if maybe instead of filling the gaps, the plain DuckDB WASM version might enable using JSON extension? I saw the shellwip deployment (and it works there just fine!), though at the same time I think it's not intended for shipping.

So I'm curious on your thoughts and direction here. Thanks for all the great stuff!

[carlopi]

Hi @pmm-motif!

We are in a transition between statically linking a fixed set of extensions to allow proper dynamic loading, there are few implementation details that are still to be fixed, but the goal would be moving JSON support to the loadable extension.

If there are not too many unseen problems, we are considering releasing an experimental/wip version of the npm packages, keep both 'stable' and 'experimental' version alive for some period (to allow switching / adapting the embedding, since this might break workflows) and then rolling them out properly.

Ideally this packages could be made available at some point this week / early next week, then you could already serve that package.

Do you have any feedback on this at any point, it's very welcome either here or on #1202!

[pmm-motif]

Many thanks @carlopi I think that would be very helpful, great news!