Difficulty: Beginner
Estimated Time: 10 minutes

Welcome!

Let's pretend for a moment that you've been given a bunch of JSON data from a third-party. You've been asked to join the data together and generate some csv exports. You also need a way for others to access the data and query it using SQL.

You think to start by looking at the JSON data and designing a relational database schema that matches the JSON data. Then, you might build a script that takes your JSON data and transforms it into a tabular format for loading into your database.

But that approach can be slow and full of pitfalls:

  • Designing a database schema that matches our data can take time.
  • If the format of the JSON data changes in the future, the database schema will need to be changed as well.
  • If the data contains nested objects, transforming the JSON data into a format that can be loaded into the database can require complex conditionals and looping logic.

But perhaps there's an easier way...

In this scenario, we're going to be using SQLite to work with JSON data. Let's get started!

Congratulations!

You just finished the scenario on using SQLite to work with JSON data. You learned how to:

  • Load JSON data into a SQLite database
  • Create and run queries using the SQLite CLI
  • Export the result of database queries to csv
  • Perform JOINs on JSON data

SQLite: Working with JSON data

Step 1 of 8

Step 1 - Overview of our data

In our data directory, we have the JSON files that come from our third-party. For this scenario, we're using data from JSON Placeholder.

ls -lrt data

We can look at the contents one of the json files.

cat data/users.json

To load the data in our JSON files into our SQLite database, we'll write a simple Python script.

To start working with Python, use the following command:

python