DuckDB: Up and Running

Chapter 6. Using DuckDB with JSON Files

In Chapter 2, you learned how to load data in different formats— CSV, Parquet, Excel files, and MySQL databases—into DuckDB. Another important file format that is popular among developers is JSON (JavaScript Object Notation).

One of the notable features of JSON is its flexible and dynamic structure: there is no fixed schema that you need to follow, and you are able to use dynamic key/value pairs to represent your own data. In DuckDB, there are several techniques you can use to work with JSON files, and hence JSON deserves a dedicated chapter.

In this chapter, we’ll examine the various ways you can load JSON files into DuckDB. We will look at a few JSON files with different structures and provide recommendations on which function you can use for loading them.

Primer on JSON

This section is a quick primer for those who are new to the JSON file format. If you’re already familiar with JSON, feel free to jump straight to the next section.

JSON is a lightweight data representation format that’s easy for humans to read and interpret, and very efficient for computers to manipulate and generate. JSON supports the following data types:

• Object

• String

• Boolean

• Number

• Array

• null

The following sections will elaborate on each of the data types.

Object

An object is an unordered collection of key/value pairs enclosed in a pair of curly braces ({}). Here is an example:

{
    "key": "value"
}

Objects can also contain no key/value pairs. These objects are known ...