JSON and JSONB support in SQLite
This article provides insight on SQLite’s support for JSON (JavaScript Object Notation) and the latest addition, JSONB. It explains how SQLite facilitates handling JSON and JSONB data and what the differences are between JSON and JSONB. Additionally, the article provides practical “hello world” examples.
What is JSON
JSON is a format for structuring data in an easily readable way, both for computers and for humans. JSON can code four primitive types of data: strings, numbers, booleans and null. It can also include non-primitive types, namely objects and arrays.
In addition there is the JSON5 format – offering extended syntax to cover more use cases for JSON, like using single quotes, no quotes, or trailing commas.
JSON support in SQLite
At first SQLite provided functions for JSON handling as an opt-in extension, controlled by the “-DSQLITE_ENABLE_JSON1” compile-time option. Since SQLite version 3.38.0, JSON1 support is built-in and can be omitted during compile time by adding the “-DSQLITE_OMIT_JSON” option. SQLite supports the JSON5 standard since the 3.42.0 version. While SQLite can process JSON5 structures, its functions will consistently return JSON in its JSON1 form.
SQLite, unlike other database engines, is flexibly typed, which is important for handling JSONs. When creating a table, you have the option to omit specifying the data type of each column entirely. SQLite operates with so called “storage classes”, where input is always classified into one of five categories [NULL, INTEGER, REAL, TEXT, BLOB]. These classes are internally divided into more granular types, aligning more naturally with the C language. However, this granularity remains hidden from users’ view. JSON naturally falls under the TEXT class. The following examples outline how you can store the JSON structure and query it in various ways within the SQLite database:
Practical examples of JSON handling in SQLite
This section will use the following JSON for demonstrative purposes:
$ JSON1='{
"country": "Luxembourg",
"capital": "Luxembourg City",
"languages": [ "French", "German", "Luxembourgish" ]
}'
$ JSON2='{
"country": "Netherlands",
"capital": "Amsterdam",
"languages": [ "Dutch" ]
}'
Parsing JSON before it enters a database
JSON can be parsed programmatically or by existing CLI tools, such as sqlite-utils, before being inserted into the database. In this process, the tool parses the JSON, organizing it into different columns within a table. The external tool processes the JSON to SQL commands directly inserting JSON fields into the table:
$ echo $JSON1 | sqlite-utils insert states.db states - $ echo $JSON2 | sqlite-utils insert states.db states - sqlite> .open states.db sqlite> SELECT * FROM states; Luxembourg|Luxembourg City|["French", "German", "Luxembourgish"] Netherlands|Amsterdam|["Dutch"]
Storing whole JSON into one column
Users can store JSON as it is or use the json() function, converting the JSON into its minified version before storing it. The json() function also throws an error if the JSON is invalid. However, it is advised to use the json_valid() or json_error_position() function for explicit validity tests before hand. The following example shows both cases:
sqlite> CREATE TABLE states(data TEXT);
sqlite> SELECT json_valid('{"country":"Luxembourg","capital":"Luxembourg City","languages":["French","German","Luxembourgish"]}');
1
sqlite> INSERT INTO states VALUES ('{"country":"Luxembourg","capital":"Luxembourg City","languages":["French","German","Luxembourgish"]}');
sqlite> INSERT INTO states VALUES (json('{"country":"Netherlands","capital":"Amsterdam","languages":["Dutch"]}'));
sqlite> SELECT * FROM states;
{"country":"Luxembourg","capital":"Luxembourg City","languages":["French","German","Luxembourgish"]}
{"country":"Netherlands","capital":"Amsterdam","languages":["Dutch"]}
Query JSON field as standard text
JSON stored in the TEXT data class means it can be selected without any added overhead like any other TEXT field. It is up to the application that uses this approach to correctly parse the data from the obtained JSON structure.
sqlite> SELECT data FROM states;
{"country":"Luxembourg","capital":"Luxembourg City","languages":["French","German","Luxembourgish"]}
{"country":"Netherlands","capital":"Amsterdam","languages":["Dutch"]}
Using JSON built-in functions
Built-in JSON functionality provides a couple of handy functions for querying specific elements of the stored JSON. json_extract() returns one or more values from the provided JSON. Similar are the “->” and “->>” operators. There are semantic differences between json_extract(), “->” and “->>”. The json_extract() function returns a JSON structure if the queried data is a JSON object or array and returns SQL format otherwise. The operator “->” always returns the JSON representation, and “->>” returns the SQL representation of the queried structure.
Using JSON functions for handling the JSON data leaves the parsing part of the job for the database, and the user application no longer needs to expend any effort on it. When accessing JSON elements, SQLite must parse JSON stored as a string each time.
Querying specific column and row can look like this:
sqlite> SELECT data->>'country' FROM states WHERE data->>'capital'=='Amsterdam'; Netherlands sqlite> SELECT data->'country' FROM states WHERE data->>'capital'=='Amsterdam'; "Netherlands" sqlite> SELECT json_extract(data,'$.country') FROM states WHERE json_extract(data, '$.capital')=='Amsterdam'; Netherlands sqlite> SELECT json_extract(data, '$.languages') FROM states; ["French","German","Luxembourgish"] ["Dutch"]
Other JSON functions in SQLite
Help with creating JSON structures is available with functions like json_array(), and json_object(). Functions for adjusting existing JSON structure are json_insert(), json_replace() and json_set() . A helpful function for debugging is json_type(), which returns the type of the JSON element. SQLite documentation provides more detailed information about these and many more JSON functions.
JSONB in SQLite
A big new feature is introduced in the SQLite 3.45.0 release – the SQLite JSONB. The aim of this feature is to speed up the JSON manipulation, since storing JSON as BLOB will save time normally spent on parsing the standard JSON saved as string. The JSONB object consists of a header and a body. The header of each element stores its properties, like size or type. Knowing the size of the JSON element speeds up its parsing; eliminating the need of searching for the next delimiter. SQLite offers various functions for JSONB handling. Many standard JSON functions have their JSONB equivalent, like the jsonb() function, which returns a JSONB object, or jsonb_extract(), extracting values from a JSONB blob. Many standard JSON functions can also take JSONB blob as a parameter.
Practical examples of JSONB handling in SQLite
Inserting a JSONB blob
When storing the JSON as a blob the jsonb() function can be used. This function takes valid JSON as a parameter and returns JSONB binary data. It can also take valid JSONB as parameter, in this situation it simply returns it back.
sqlite> CREATE TABLE states(data BLOB);
sqlite> INSERT INTO states VALUES(jsonb('{"country":"Luxembourg","capital":"Luxembourg City","languages":["French","German","Luxembourgish"]}'));
sqlite> INSERT INTO states VALUES(jsonb('{"country":"Netherlands","capital":"Amsterdam","languages":["Dutch"]}'));
Retrieving JSONB blob
If we choose the standard way for retrieving JSONB, its binary representation is returned.
sqlite> SELECT data FROM states; �wcountryLuxembourgwcapital�Luxembourg Citylanguages�gFrenchgGerman�Luxembourgish �wcountryNetherlandswcapitalAmsterdamlanguageskWDutch
It can be more useful to retrieve the text form of stored JSONB. For this the json() and json_extract() functions can be used.
sqlite> SELECT json(data) FROM states;
{"country":"Luxembourg","capital":"Luxembourg City","languages":["French","German","Luxembourgish"]}
{"country":"Netherlands","capital":"Amsterdam","languages":["Dutch"]}
sqlite> SELECT json_extract(data, '$.languages') FROM states;
["French","German","Luxembourgish"]
["Dutch"]
On the other hand, jsonb_extract() returns JSONB binary if a JSON object or array is retrieved and returns values otherwise.
sqlite> SELECT jsonb_extract(data, '$.languages') FROM states; �gFrenchgGerman�Luxembourgish kWDutch sqlite> SELECT jsonb_extract(data, '$.capital') FROM states; Luxembourg City Amsterdam
In conclusion
This article presented the handling of JSON in the SQLite database, demonstrating various approaches for storing and retrieving JSON data. Additionally, it briefly outlined SQLite’s new feature, SQLite JSONB, with illustrative examples.