Could we add more tests with the structs/vectors in test_all_types and test_vector_types (e.g. select * from test_vector_types(null::int[], false);)?

I'm happy to add more tests, but I'm not sure I understand what you mean.

test_all_types is in DuckDB core, while the JSON type is only in the extension. How would this work? Happy to discuss in the office today

Ah, I think I understand. I'll write some more tests and then you can check if it's OK

I just meant including the casts to and from the vectors, e.g.:

select test_vector::JSON from test_vector_types(null::int[], false);
select test_vector::JSON::int[]::json from test_vector_types(null::int[], false);
select test_vector::JSON from test_vector_types(null::int[][], false);
select test_vector::JSON::int[][]::json from test_vector_types(null::int[][], false);
select test_vector::JSON from test_vector_types(null::row(i varchar, j int), false);
...

The false indicates whether or not to flatten the vectors, this can be used to find bugs in handling of non-flat vectors by checking if the result is the same for both settings (true and false), e.g.:

foreach flatten true false

query I nosort expected_result_row
select test_vector::JSON from test_vector_types(null::row(i varchar, j int), ${flatten});

endloop

This was an excellent suggestion, thanks! I was able to quickly squash a bunch of bugs. I've also added more functionality in the process. We can now cast all of our types to JSON and back, e.g.:

D select '1996-03-27'::DATE::JSON d;
┌──────────────┐
│      d       │
│     json     │
├──────────────┤
│ "1996-03-27" │
└──────────────┘
D select '"1996-03-27"'::JSON::DATE d;
┌────────────┐
│     d      │
│    date    │
├────────────┤
│ 1996-03-27 │
└────────────┘

VARCHAR is a special case of this, and still behaves like before. It is parsed and validated instead of being interpreted as a string that has to be quoted.