How to insert a Map?

[vibl]

How do I insert an array of objects into a MAP type field ? Passing it as value does not work (it is serialized with toString()).

I couldn't find anything in the documentation.

[vibl]

FWIW, inserting the value [Object.keys(ob), Object.values(obj)] does not work either (contrary to what https://duckdb.org/docs/sql/data_types/overview.html#nested--composite-types would suggest).

[Draiken]

Haven't tried on Map, but with STRUCT I managed to do it with JSON.stringify. Since Map is similar, it might be the way to go.

I'd really love if the docs had examples of this, since it's not clear from the get go and I'm unsure if JSON.stringify is the best solution.

[markg85]

Disclaimer: i'm using duckdb-async but as that wraps this library it should apply to this one.

I was about to open a new bug report for this but the issue here seems to reflect what i'm facing too. I have a query like:

await db.run(`INSERT INTO x (a, b, c, embedding) VALUES (?, ?, ?, ?)`, valA, valB, valC, [0.0,0.0,0.0]);

^^ simplified the above for the examples sake. This fails with the error message:

Conversion Error: Type VARCHAR with value '0,0,0' can't be cast to the destination type ARRAY[3], the size of the array must match the destination type

Which makes no sense to me because my table definition is:

CREATE TABLE IF NOT EXISTS x (
    a TEXT PRIMARY KEY,
    b TEXT,
    c INTEGER,
    embedding FLOAT[3]
);

Based on the documentation i'd also say this would be completely contradicting what it says for array values. It explicitly states: Each row must have the **same data type** within each instance of the ARRAY and the same number of elements.

Logically this should work but it doesn't.

await db.run(`INSERT INTO x (a, b, c, embedding) VALUES (?, ?, ?, ?)`, valA, valB, valC, [0.0,0.0,0.0]);

And logically this should not work but does.

await db.run(`INSERT INTO x (a, b, c, embedding) VALUES (?, ?, ?, ?)`, valA, valB, valC, JSON.stringify([0.0,0.0,0.0]));

[Draiken]

Yeah that's definitely the same experience I had when inserting objects. Regardless of how weird it is though, if it was at least documented, it wouldn't be such an issue. But the way it is, everyone that uses the library has to figure this out by themselves when they have to use structs/arrays.