How to load a JSON documents in a database

A propos

This howto will show you how to load json document (files) into a relational database.

This example uses the sqlite howto database but you may use any other relational database

Steps

The Json files

To be able to follow along the SQLite examples of the JSON documentation, we will load phones numbers in a user table.

The JSON files are located in the subdirectory json/phone of the howto directory where there is one file by user containing a JSON array of phones.

With the data list command, we can list them.

tabli data list json/phone/*.json@howto  
path                     
----------------------   
json\phone\Arnold.json   
json\phone\Bob.json      
json\phone\Dale.json     
json\phone\Daniel.json   
json\phone\Don.json

With the data print command, we can see for instance the JSON array of phones in the Bob.json file

tabli data print json/phone/Bob.json@howto  
["323-423-2238","747-444-3222","475-202-7142"]

Json relational structure

By default, a json file is seen as a relation with one column called json

The structure of the JSON file can be seen with the data struct command.

For instance, for the Don.json file, there is one column:

  • at the position 1
  • called json
  • with the json data type
tabli data struct json/phone/Don.json@howto  
Structure of the resource json\phone\Don.json@howto
position   name   data_type   precision   scale   primary_key   nullable   autoincrement   comment   
--------   ----   ---------   ---------   -----   -----------   --------   -------------   -------   
1          json   json                                          x          false

Loading the JSON files

The below data insert command will load the JSON files into the phones table of the sqlite howto database.

tabli data insert json/phone/*.json@howto  user@sqlite
Source                         Target        Latency (ms)   Row Count   Error   Message   
----------------------------   -----------   ------------   ---------   -----   -------   
json\phone\Arnold.json@howto   user@sqlite   10             1                             
json\phone\Bob.json@howto      user@sqlite   12             1                             
json\phone\Dale.json@howto     user@sqlite   11             1                             
json\phone\Daniel.json@howto   user@sqlite   11             1                             
json\phone\Don.json@howto      user@sqlite   29             1

You can inspect the content with the head command

tabli data head user@sqlite
The first 10 rows of the data resource (user@sqlite): 
json                                              
-----------------------------------------------   
["470-293-7517"]                                  
["323-423-2238","747-444-3222","475-202-7142"]    
["704-555-0150","202-555-0150"]                   
["425-898-3481","360-637-7972", "205-533-8271"]   
["731-414-7236","256-233-3557","409-645-3618"]

Changing the name of the JSON column

The name of the JSON column is by default json but we can change it by setting the columnName json attribute

After running the below head command, we can see that the column name has changed to phones

tabli data head --attribute columnName=phone json/phone/Don.json@howto
The first 10 rows of the data resource (json\phone\Don.json@howto): 
phone              
----------------   
["470-293-7517"]

Adding a column with the logical name of the JSON file

Because the JSON content of files does not have any kind of identifier, we can see that the data is actually worthless because we can't relate the phone to a user.

To be able to add context to file loaded, Tabulify supports the concept of virtual columns where it's possible to add any attribute information about the file loaded.

The virtual columns are specified with the –virtual-column option that has the following syntax:

--virtual-column virtualColumnName=resourceAttributeName

where:

  • virtualColumnName is the name of the column added
  • resourceAttributeName is the name of resource attribute.

For instance, if we want to add:

we would add –virtual-column name=logical_name

With the print command, the full command would look like below.

tabli data print --attribute columnName=phone --virtual-column name=logical_name json/phone/Bob.json@howto  
phone                                            name   
----------------------------------------------   ----   
["323-423-2238","747-444-3222","475-202-7142"]   Bob

Reloading the JSON files with the virtual column name

To reload the JSON files:

  • with the extra column name and the file logical name
  • with a column phones containing the JSON document

the syntax of the tabli data insert would be:

tabli data insert --target-operation replace --source-attribute columnName=phone --virtual-column name=logical_name json/phone/*@howto  user@sqlite

where:

Output:

Source                         Target        Latency (ms)   Row Count   Error   Message   
----------------------------   -----------   ------------   ---------   -----   -------   
json\phone\Arnold.json@howto   user@sqlite   15             1                             
json\phone\Bob.json@howto      user@sqlite   14             1                             
json\phone\Dale.json@howto     user@sqlite   16             1                             
json\phone\Daniel.json@howto   user@sqlite   14             1                             
json\phone\Don.json@howto      user@sqlite   37             1

The JSON data loaded has now the name column as identifier.

tabli data print user@sqlite
phone                                             name     
-----------------------------------------------   ------   
["470-293-7517"]                                  Don      
["323-423-2238","747-444-3222","475-202-7142"]    Bob      
["704-555-0150","202-555-0150"]                   Arnold   
["425-898-3481","360-637-7972", "205-533-8271"]   Daniel   
["731-414-7236","256-233-3557","409-645-3618"]    Dale

Performing JSON SQL statement

Inspired from the SQLite examples of the JSON documentation - section 4.3.1, we will now run the below SQL with JSON function.

-- https://www.sqlite.org/json1.html#examples_using_json_each_and_json_tree_
SELECT DISTINCT user.name
  FROM user, json_each(user.phone)
 WHERE json_each.value LIKE '704-%';


To see the result of this SQL query, we will use the print command and see that Arnold is the user that have a phone number that starts with 704.

tabli data print (sqlite/user_phone_json.sql@howto)@sqlite
name     
------   
Arnold

Next

  • If you want to explore the JSON SQL function of SQLite, you can read the Sqlite JSON documentation.
  • A JSON file containing an JSON object can also be loaded with its first properties representing columns. To do that, you need to set the structure attribute to properties.
Task Runner