This PR implements the read_json function, analogous to read_csv. This table function requires specifying the output column names and types, and cannot auto-detect yet (read_json_auto will be added in a future PR).

For example, the read_json / read_ndjson function can be used like so:

create temporary table lineitem as
select *
from read_ndjson('lineitem.json', 
                 columns={l_orderkey: 'INTEGER',
                          l_partkey: 'INTEGER',
                          l_suppkey: 'INTEGER',
                          l_linenumber: 'INTEGER',
                          l_quantity: 'INTEGER',
                          l_extendedprice: 'DECIMAL(15,2)',
                          l_discount: 'DECIMAL(15,2)',
                          l_tax: 'DECIMAL(15,2)',
                          l_returnflag: 'VARCHAR',
                          l_linestatus: 'VARCHAR',
                          l_shipdate: 'DATE',
                          l_commitdate: 'DATE',
                          l_receiptdate: 'DATE',
                          l_shipinstruct: 'VARCHAR',
                          l_shipmode: 'VARCHAR',
                          l_comment: 'VARCHAR'});

Here lineitem.json is a newline-delimited JSON file containing the TPC-H SF1 lineitem table.

I've benchmarked this example query against read_csv with the same parameters. I enabled

SET experimental_parallel_csv=true;

To make the comparison fairer, as read_json is parallel. Here are the results:

As we can see, the results are very close. The CSV file is ~0.7GB, while the JSON file is ~2.0GB, because each JSON record specifies the column names.

I've also implemented projection pushdown, and to benchmark this I run TPC-H Q1 straight on the JSON/CSV file. Here are the results:

The CSV reader does not have projection pushdown (yet), so the JSON reader has the edge here. In the future, filter pushdown can be added to the JSON reader as well to save some more conversions.

I've also added some better error reporting when JSON parsing fails. This is easier for newline-delimited JSON, so we get a bit more information in our error:

-- ndjson error
Invalid Input Error: Malformed JSON in file "data/json/unterminated_quotes.ndjson", line 3 at byte 26: unexpected end of data.
-- unstructured json error
Invalid Input Error: Malformed JSON in file "data/json/unterminated_quotes.ndjson" at byte 111: unexpected control character in string.

For newline-delimited JSON we get line number and byte information, and for unstructured JSON we get only the byte within the file where the error occurs. Getting accurate line number information is tricky when scans are parallel, but I think the implementation should cover this too.

Happy to receive feedback!

Edit/Update 1: I've implemented the json_keys function (#5522), and done some performance improvements (numbers above are updated)

Edit/Update 2: I've implemented schema detection in this PR as well. Here's a benchmark/comparison of reading lineitem SF1 as newline-delimited JSON with different systems:

For pandas/pyarrow/polars I've used the read_json/read_ndjson functions, and for duckdb I've used

create temporary table as select * from 'lineitem.json'

It's not a perfect comparison, but all of the approaches materialize the result fully, so it's reasonable. As a bonus, DuckDB is able to detect that some of the string values in lineitem.json are of type DATE, which the other systems do not.