---json
{
"canonical": ":howto:database:json:load",
"description": "This howto will show you how to load json document (files) into your relational database.",
"images": [
{ "path": ":combostrap:icons:codicon:json.svg" }
],
"low_quality_page": "false",
"name": "Loading Json documents",
"page_id": "a8z097oexo9em70mdaha6",
"title": "How to load a JSON documents in a database"
}
---
====== Database HowTo - How to load a JSON document in a database ======
===== A propos =====
This [[howto:howto|howto]] will show you how to load [[docs:resource:json|json document (files)]] into a [[docs:system:relational|relational database]].
This example uses the [[docs:system:sqlite:howto|sqlite howto database]] but you may use any other [[docs:system:relational|relational database]]
===== Steps =====
tabli data drop --not-strict user@sqlite
==== The Json files ====
To be able to follow along the [[https://www.sqlite.org/json1.html|SQLite examples of the JSON documentation]], we will load ''phones numbers'' in a ''user'' table.
The [[docs:resource:json|JSON files]] are located in the subdirectory ''json/phone'' of the [[docs:connection:howto|howto directory]] where there is one file by ''user'' containing a JSON array of phones.
With the [[docs:tabli:data:list|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 [[docs:tabli:data:print|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 [[docs:resource:json|json file]] is seen as a [[docs:resource:tabular|relation]] with one column called ''json''
The [[docs:resource:structure|structure]] of the JSON file can be seen with the [[docs:tabli:data:struct|data struct command]].
For instance, for the ''Don.json'' file, there is one column:
* at the ''position 1''
* called ''json''
* with the ''json'' [[docs:common:data_type|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 [[docs:tabli:data:insert|data insert command]] will load the JSON files into the ''phones'' [[docs:resource:table|table]] of the [[docs:system:sqlite:howto|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 [[docs:tabli:data:head|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 [[docs:resource:json#attribute|columnName json attribute]]
After running the below [[docs:tabli:data:head|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 [[docs:resource:virtual_column|virtual columns]] where it's possible to add [[docs:resource:attribute|any attribute information about the file loaded]].
The [[docs:resource:virtual_column|virtual columns]] are specified with the ''--virtual-column'' [[docs:tabli:option|option]] that has the following syntax:
--virtual-column virtualColumnName=resourceAttributeName
where:
* ''virtualColumnName'' is the name of the column added
* ''resourceAttributeName'' is the name of [[docs:resource:attribute|resource attribute]].
For instance, if we want to add:
* the [[docs:resource:logicalname|logical name attribute]]
* into a column called ''name'',
we would add ''--virtual-column name=logical_name''
With the [[docs:tabli:data:print|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 [[docs:resource:logicalname|file logical name]]
* with a column ''phones'' containing the ''JSON document''
the syntax of the [[docs:tabli:data:insert|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:
* ''%%--target-operation%%'' will ''replace'' the target ''phones@sqlite''
* ''%%--source-attribute columnName=phones%%'' will set the [[docs:resource:json#attribute|JSON attribute columnName]] to ''phones''
* ''%%--virtual-column name=logical_name%%'' will add the [[docs:resource:virtual_column|virtual column]] names ''name'' with the [[docs:resource:logicalname|logical name attribute]] of the source
* ''json/phone/*@howto'' is the [[docs:resource:data_selector|source data selector]]
* ''phones@sqlite'' is the [[docs:resource:target_uri|target uri]]
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 [[https://www.sqlite.org/json1.html#examples_using_json_each_and_json_tree_|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 [[docs:resource:select|SQL query]], we will use the [[docs:tabli:data:print|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 [[https://www.sqlite.org/json1.html|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 [[docs:resource:json#attributes|structure attribute]] to ''properties''.