Analyze JSON Data Using SQL and DuckDB

Prerequisites

• Install DuckDB (instructions below).
• Optional: View or download the sample JSON data used in this tutorial.

What is JSON?

JSON, which stands for JavaScript Object Notation, is a lightweight data format. It is designed to be fairly easy for humans to read and write, and easy for machines to parse and generate, making it a great way to share data. It was originally created for Web applications to share data between the browser and server and has become a standard for storing and sharing data in many other types of applications. Outside of the browser, JSON is typically stored in a text file with a .json extension.

Let’s waddle through some of the basics of JSON! JSON is built on two basic structures:

• A collection of one or more name/value pairs surrounded by curly braces {}, each pair separated by commas.
• A list of one or more values surrounded by brackets [], each value separated by commas.

Here’s an example:

Copy code
{
  "ducks": [
    {
      "name": "Quackmire",
      "color": "green",
      "actions": [
        "swimming",
        "waddling",
        "quacking"
      ]
    },
    {
      "name": "Feather Locklear",
      "color": "yellow",
      "actions": [
        "sunbathing"
      ]
    },
    {
      "name": "Duck Norris",
      "color": "brown",
      "actions": [
        "karate chopping bread"
      ]
    }
  ],
  "totalDucks": 3
}

• All of the data is wrapped in curly braces {}, like a cozy nest.
• Each duck is part of a "ducks" array (like a flock of ducks in a row), wrapped by square brackets [].
• Each duck in the array is a set of "name/value" pairs. For example, "name": "Duck Norris" tells us one duck's name is Duck Norris.

Curly braces {} are used to represent an object. You might also think of an object as a record, thing, or entity. The name/value pairs are sometimes called properties. The value associated with the name can represent text (a string), a number, true/false (a boolean), a collection of values (an array), or a nested object. An array is represented by square brackets [] and can be an ordered list of strings, numbers, booleans, or objects.

The JSON format can represent data structures ranging from simple to complex with nested objects and arrays! This makes it a great way to express and exchange data.

Install and execute DuckDB

If you don’t already have DuckDB installed, flap on over to duckdb.org and follow the instructions for your operating system. In this tutorial, you’ll be using DuckDB from the command line.

• Mac: Follow the Homebrew (brew) install instructions.
• Windows: Follow the winget install instructions.
• Linux: Download the appropriate archive for your OS and processor. Extract the duckdb executable binary from the archive to a folder where you easily execute it from your terminal.

Copy code
duckdb

Copy code
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D

Copy code
SELECT current_date - 7;

Query JSON files with DuckDB

In many cases, you can query data directly from a JSON file by specifying a path to the file.

• Create a new text file named ducks.json and open it in a text editor.
• Paste the following JSON data into the file and save it.

Copy code
[
  {
    "id": "kA0KgL",
    "color": "red",
    "firstName": "Marty",
    "lastName": "McFly",
    "gender": "male"
  },
  {
    "id": "dx3ngL",
    "color": "teal",
    "firstName": "Duckota",
    "lastName": "Fanning",
    "gender": "female"
  },
  {
    "id": "FQ4dU1",
    "color": "yellow",
    "firstName": "Duck",
    "lastName": "Norris",
    "gender": "male"
  },
  {
    "id": "JqS7ZZ",
    "color": "red",
    "firstName": "James",
    "lastName": "Pond",
    "gender": "male"
  },
  {
    "id": "ZM5uJL",
    "color": "black",
    "firstName": "Darth",
    "lastName": "Wader",
    "gender": "male"
  }
]

With DuckDB running at the command line, paste the following query and press ENTER.

Copy code
SELECT * FROM './ducks.json';

The results should look similar to the following.

Copy code
D SELECT * FROM './ducks.json';
┌─────────┬─────────┬───────────┬──────────┬─────────┐
│   id    │  color  │ firstName │ lastName │ gender  │
│ varchar │ varchar │  varchar  │ varchar  │ varchar │
├─────────┼─────────┼───────────┼──────────┼─────────┤
│ kA0KgL  │ red     │ Marty     │ McFly    │ male    │
│ dx3ngL  │ teal    │ Duckota   │ Fanning  │ female  │
│ FQ4dU1  │ yellow  │ Duck      │ Norris   │ male    │
│ JqS7ZZ  │ red     │ James     │ Pond     │ male    │
│ ZM5uJL  │ yellow  │ Darth     │ Wader    │ male    │
├─────────┴─────────┴───────────┴──────────┴─────────┤
│ 5 rows                                   5 columns │
└────────────────────────────────────────────────────┘

Copy code
.help .mode

Copy code
.mode column

Copy code
D SELECT * FROM './ducks.json';
id      color   firstName  lastName  gender
------  ------  ---------  --------  ------
kA0KgL  red     Marty      McFly     male
dx3ngL  teal    Duckota    Fanning   female
FQ4dU1  yellow  Duck       Norris    male
JqS7ZZ  red     James      Pond      male
ZM5uJL  black   Darth      Wader     male

Copy code
.mode duckbox

Copy code
SELECT * FROM './*.json';

Copy code
SELECT * FROM './monthly-sales-2023*.json';

Copy code
SELECT ducks.firstName || ' ' || ducks.lastName AS duck_name,
    samples.action,
    COUNT(*) AS observations
FROM    './samples.json' AS samples
JOIN    './ducks.json' AS ducks ON ducks.id = samples.id
GROUP BY ALL
ORDER BY 1, 3 DESC;

Copy code
┌────────────────┬─────────────────────────┬──────────────┐
│   duck_name    │         action          │ observations │
│    varchar     │         varchar         │    int64     │
├────────────────┼─────────────────────────┼──────────────┤
│ Captain Quack  │ sleeping                │          890 │
│ Captain Quack  │ quacking                │          632 │
│ Captain Quack  │ eating                  │          623 │
│ Captain Quack  │ annoying                │          594 │
│ Captain Quack  │ swimming                │          356 │
│ Captain Quack  │ waddling                │          351 │
│ Captain Quack  │ sunbathing              │          348 │
│ Captain Quack  │ twitching               │          125 │
│ Captain Quack  │ flying                  │          121 │
│ Captain Quack  │ dancing                 │          117 │
│ Captain Quack  │ diving                  │          106 │
│ Captain Quack  │ posting on social media │           57 │
...

Copy code
CREATE OR REPLACE TABLE duck_samples AS
SELECT CAST(samples.sampleTime AS date) AS sample_date,
    ducks.firstName || ' ' || ducks.lastName AS duck_name,
    samples.action,
    COUNT(*) AS observations
FROM    read_json('./samples.json', columns = { id: 'varchar', sampleTime: 'datetime', action: 'varchar' }) AS samples
JOIN    './ducks.json' AS ducks ON ducks.id = samples.id
GROUP BY ALL;

Copy code
SELECT ds.sample_date, 
    ds.action,
    ds.observations,
    round(( ds.observations / totals.total_obs ) * 100, 1) AS percent_total
FROM ( SELECT sample_date, action, SUM(observations) AS observations FROM duck_samples GROUP BY ALL ) AS ds
    JOIN ( SELECT sample_date, SUM(observations) AS total_obs FROM duck_samples GROUP BY ALL ) AS totals
    ON ds.sample_date = totals.sample_date
WHERE ds.sample_date = '2024-01-01'
GROUP BY ALL
ORDER BY 3 DESC;

┌─────────────┬─────────────────────────┬──────────────┬───────────────┐
│ sample_date │         action          │ observations │ percent_total │
│    date     │         varchar         │    int128    │    double     │
├─────────────┼─────────────────────────┼──────────────┼───────────────┤
│ 2024-01-01  │ sleeping                │         1551 │          21.5 │
│ 2024-01-01  │ quacking                │          978 │          13.6 │
│ 2024-01-01  │ eating                  │          977 │          13.6 │
│ 2024-01-01  │ annoying                │          947 │          13.2 │
│ 2024-01-01  │ swimming                │          612 │           8.5 │
│ 2024-01-01  │ waddling                │          600 │           8.3 │
│ 2024-01-01  │ sunbathing              │          598 │           8.3 │
│ 2024-01-01  │ flying                  │          231 │           3.2 │
│ 2024-01-01  │ diving                  │          220 │           3.1 │
│ 2024-01-01  │ twitching               │          208 │           2.9 │
│ 2024-01-01  │ dancing                 │          193 │           2.7 │
│ 2024-01-01  │ posting on social media │           85 │           1.2 │
├─────────────┴─────────────────────────┴──────────────┴───────────────┤
│ 12 rows                                                    4 columns │
└──────────────────────────────────────────────────────────────────────┘

Query complex JSON data

Depending on the structure of the JSON data you are working with it may be necessary to extract values from nested objects or arrays. Nested objects are referred to in DuckDB as a struct data type. In some cases, it's possible to access data directly in a struct using syntax that resembles schema or table namespaces. For example, imagine you have JSON file named ducks-nested-name.json with the following data.

Copy code
[
  {
    "color": "red",
    "name": {
      "firstName": "Marty",
      "lastName": "McFly"
    },
    "gender": "male"
  },
  {
    "color": "teal",
    "name": {
      "firstName": "Duckota",
      "lastName": "Fanning"
    },
    "gender": "female"
  },
  {
    "color": "yellow",
    "name": {
      "firstName": "Duck",
      "lastName": "Norris"
    },
    "gender": "male"
  }
]

If you query the file directly, the results would like the following.

Copy code
D SELECT * FROM './ducks-nested-name.json';

┌─────────┬─────────────────────────────────────────────┬─────────┐
│  color  │                    name                     │ gender  │
│ varchar │ struct(firstname varchar, lastname varchar) │ varchar │
├─────────┼─────────────────────────────────────────────┼─────────┤
│ red     │ {'firstName': Marty, 'lastName': McFly}     │ male    │
│ teal    │ {'firstName': Duckota, 'lastName': Fanning} │ female  │
│ yellow  │ {'firstName': Duck, 'lastName': Norris}     │ male    │
└─────────┴─────────────────────────────────────────────┴─────────┘

You can access the nested values under name using the following syntax.

Copy code
D SELECT color, name.firstName FROM './ducks-nested-name.json';

┌─────────┬───────────┐
│  color  │ firstName │
│ varchar │  varchar  │
├─────────┼───────────┤
│ red     │ Marty     │
│ teal    │ Duckota   │
│ yellow  │ Duck      │
└─────────┴───────────┘

DuckDB provides the unnest function to help when dealing with nested data. Taking the first example with Quackmire, Feather Locklear, and Duck Norris, if you query this JSON data without using unnest, you'll see the following results.

Copy code
┌─────────────────────────────────────────────────────────────────┬────────────┐
│                              ducks                              │ totalDucks │
│   struct("name" varchar, color varchar, actions varchar[])[]    │   int64    │
├─────────────────────────────────────────────────────────────────┼────────────┤
│ [{'name': Quackmire, 'color': green, 'actions': [swimming, wa…  │          3 │
└─────────────────────────────────────────────────────────────────┴────────────┘

To make better use of the data in the ducks column, use the unnest function to destructure and flatten the data into their own columns.

Copy code
D SELECT unnest(ducks, recursive:= true) AS ducks 
FROM './ducks-example.json';

┌──────────────────┬─────────┬────────────────────────────────┐
│       name       │  color  │            actions             │
│     varchar      │ varchar │           varchar[]            │
├──────────────────┼─────────┼────────────────────────────────┤
│ Quackmire        │ green   │ [swimming, waddling, quacking] │
│ Feather Locklear │ yellow  │ [sunbathing]                   │
│ Duck Norris      │ brown   │ [karate chopping bread]        │
└──────────────────┴─────────┴────────────────────────────────┘

Query JSON data from an API

DuckDB can also parse data directly from APIs that return JSON. The following example uses the TVmaze API, a public API for TV shows.

Copy code
D SELECT show.name, show.type, show.summary
FROM read_json('https://api.tvmaze.com/search/shows?q=duck', 
       auto_detect=true);

┌──────────────────────┬──────────────┬────────────────────────────────────────────────────────────────┐
│      show_name       │  show_type   │                            summary                             │
│         json         │     json     │                              json                              │
├──────────────────────┼──────────────┼────────────────────────────────────────────────────────────────┤
│ "Duck Dynasty"       │ "Reality"    │ "<p>In <b>Duck Dynasty</b>, A&amp;E Network introduces the R…  │
│ "Darkwing Duck"      │ "Animation"  │ "<p>In the city of St. Canard, the people are plagued by the…  │
│ "Duck Dodgers"       │ "Animation"  │ "<p>Animated sci-fi series based on the alter ego of Looney …  │
│ "Duck Patrol"        │ "Scripted"   │ "<p><b>Duck Patrol</b> deals with the activities of the offi…  │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘

Learn more about DuckDB

To learn more about what you can do with DuckDB, check out the DuckDB Snippets Library or download a free copy of DuckDB in Action.