Storing and Retrieving JSON Data In SQLite

It has always been possible to store JSON Data in an SQLite database as JSON data is simply text data.

However what was not previously possible was how to query this JSON data using a key.

This changed in version 3.9 in 2015 (SQLite history) and so there is now no need to separate out the JSON data so that you can query using a key. .



In this tutorial we look at storing JSON data and retrieving it using keys instead of fields, and then look at storing time series data.

As an example let us take a JavaScript object

{timestamp:1650223112523,temperature:25,humidity:59}

To store this in SQLite you simple convert to JSON and store as text.

However prior to version 3.9 it wasn’t possible to query the database using the temperature,time or humidity keys as they are part of the JSON data.

To accomplish this you would need to extract the key from the JSON data and create a database key for this.

Example Flows

The best way to see this is via an example and so we will create two flows using database tables that use these two methods.

For our flows we are going to be storing temperature and humidity data for a group of sensors (sensor1,sensor2 etc).

We will be recording

  • sensor name
  • time
  • temperature
  • humidity

Method 1

If we needed to query the database on all of these fields the we would create a database scheme with the following fields:

DEVICE TEXT NOT NULL,
TIMESTAMP INT NOT NULL
TEMPERATURE REAL NOT NULL,
HUMIDITY REAL NOT NULL

Note the time stamp is an integer as we are using a Unix like time stamp.

Method 2

DEVICE TEXT NOT NULL,
DATA TEXT NOT NULL

Where Data contains our time stamp,temperature and humidity stored in JSON.

Creating the Tables

Storing as Individual Fields

We are going to call our tables sensor_data1 and sensor data_2 and use the database called test.db

Our command to create sensor_data1 is

CREATE TABLE sensor_data1(DEVICE TEXT NOT NULL,TIMESTAMP INT NOT NULL,TEMPERATURE REAL NOT NULL,HUMIDITY REAL NOT NULL)

Now although the above works well and it is relatively easy to create queries for any of the fields it isn’t flexible as what happens if I need suddenly to record the atmospheric pressure?

The answer is that we need to add another field to the table which is possible but not simple.

Storing as JSON

To create our table we use:

CREATE TABLE sensor_data2(DEVICE TEXT NOT NULL,DATA TEXT NOT NULL)

This time our data is simply a JSON object with time stamp,temperature and humidityr.

We can easily expand it without having to modify the table.

The screen shot below illustrate this and the first entries were created without a timestamp and then I added a timestamp to the JSON data.

sqlite-json-data

The screen shot above also illustrates how we can modify a JSON key value . I created a query to change the humidity value based on the timestamp.

Here is the actual code taken from the flow.

let temp="update sensor_data2 set data=";
temp=temp+"json_set(sensor_data2.data,'$.humidity',59) where json_extract(sensor_data2.data,'$.timestamp')=1650223112523";
msg.topic=temp;
return msg;

Working with the JSON Data

The documentation lists all of the JSON functions that are available.

My example flow illustrates two of them :

  • json_extract
  • json_set

If you refer to the code above

sensor_data2.data =table_name.field_name

The actual JSON key is referenced as $.keyname ($.humidity) and is in quotes.

For the timestamp we use the JSON extract json_extract(sensor_data2.data,’$.timestamp’)

This time we have $.timestamp.



Example Flow

Video

Storing Time series Data In SQLite

When it comes to storing time series data Influxdb seems to be the main choice.

Influxdb is very powerful, but not so easy to use as SQLite, and is I feel an overkill for small data volumes on devices like edge nodes and home networks.

The fact that you can store and retrieve JSON data  makes storing time series data very easy.

We simply create a database table with the following fields:

TIMESTAMP INT NOT NULL
DATA TEXT NOT NULL

The timestamp field contains the current time and the data field our JSON data.

Resources and references and Tutorials

Click to rate this post!
[Total: 5 Average: 4.2]

2 comments

Leave a Reply to Doug Franklin Cancel reply

Your email address will not be published. Required fields are marked *