Working with JSON in SQLite databases

[StephenQuan1]

Summary

Consider a project which uses a SQLite database.

Over time requirements changes. We may be forced to change the schema of that SQLite database.

Unlike other RDBMS, SQLite doesn't allow you to add, modify or delete columns in a table after a table is created. You will be required to export the data, recreate the schema, then import the data. Such data migration exercises requires scripts to be built with attention to detail to ensure there is no data loss during data migration.

You can future proof your schema by storing data as JSON. AppStudio's SqlScalarFunction helps optimize the use of JSON.

Scenario

You've been tasked with creating a Geographic Quiz app.

Version 1 will ship with a set of countries and we want to quiz the user their knowledge of capitals.
Version 2 will ship with population and we want to quiz the user to rank countries in order.
Version 3 will ship with currency conversion quiz.

Initial App

The following is a sample app which is an initial implementation of Version 1. We will deconstruct this app and explore how it could handle the future requirements.

import QtQuick 2.7
import ArcGIS.AppFramework 1.0
import ArcGIS.AppFramework.Sql 1.0

Item {
    property FileFolder sqlFolder: FileFolder { path: "~/ArcGIS/Data/Sql" }

    ListView {
        id: listView
        anchors.fill: parent
        anchors.margins: 10
        delegate: Row { spacing: 10; Text { text: name } Text { text: capital } }
    }

    SqlDatabase {
        id: db
        databaseName: sqlFolder.filePath('countries.sqlite')
        SqlScalarFunction {
            name: 'json_value'
            method: function (json_text, key) { return json_text ? JSON.parse(json_text)[key] : null; }
        }
    }

    Component.onCompleted: {
        db.open();
        db.query("DROP TABLE IF EXISTS countries ");
        db.query("CREATE TABLE IF NOT EXISTS countries (json_text TEXT)");
        db.query("CREATE INDEX countries_name ON countries ( json_value(json_text, 'name') COLLATE NOCASE )");
        db.query("INSERT INTO countries VALUES (:json_text)", { json_text: '{"name":"United States", "capital":"Washington D.C."}'} );
        db.query("INSERT INTO countries VALUES (:json_text)", { json_text: '{"name":"Australia", "capital":"Canberra", "population": 24130000}'} );
        db.query("INSERT INTO countries VALUES (:json_text)", { json_text: '{"name":"France", "capital":"Paris"}'} );

        var sql = [
            "SELECT json_value(json_text, 'name') as name, ",
            "       json_value(json_text, 'capital') as capital ",
            "FROM   countries ",
            "WHERE  name like 'United%' "
        ].join("\n");

        listView.model = db.queryModel(sql);
    }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Schema

You see the schema for our app is simply:

CREATE TABLE countries
(
    json_text TEXT
)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

In Version 1, we want json_text to be a JSON string with the country's name and capital defined. To preview Version 2 one of the records already has population defined as well.

Unpacking JSON with SqlScalarFunction

We have a SqlScalarFunction defined which implements:

function json_value(json_text, key)
{
    return json_text ? JSON.parse(json_text)[key] : null;
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

So that `SQLite` can use this to unpack a JSON string and retrieve a value by its key.

Indexing and Querying

SQLite allows one to create indexes on expressions. This means we can create an index on a key extracted from the JSON string. This feature is incredible. It means we are effectively caching an extracted value so that means we can avoid repeated calculation.

CREATE INDEX countries_name ON countries ( json_value(json_text, 'name') COLLATE NOCASE );

SELECT json_value(json_text, 'name') AS name,
       json_value(json_text, 'capital') AS capital
FROM   countries
WHERE  name LIKE 'United%';‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

When you check the query with `EXPLAIN QUERY PLAN` we see that it is using the index:

EXPLAIN QUERY PLAN
SELECT json_value(json_text, 'name') AS name,
       json_value(json_text, 'capital') AS capital
FROM   countries
WHERE  name LIKE 'United%';

SEARCH TABLE countries USING INDEX countries_name (<expr>>? AND <expr><?)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Note that a collating sequence must be specified else `LIKE` where clauses will yield a full table scan.

Planning

When planning for Versions 2 and Versions 3 of the app, you'll appreciate that we can reuse the existing SQLite database and just add or update records within. We can easily drop / create indexes to accommodate new fields and new app requirements.