JSON in SQLite using PHP

Before you begin

In this tutorial will learn how to store a JSON in a SQLite table with JSON data type column using PHP. A Linux machine with PHP 7.x and SQLite version > 3.3 will be required to follow this tutorial. Finally, make sure you have the PHP package manager composer installed to follow this post:

$ curl -sS https://getcomposer.org/installer | \
  php -- --install-dir=/usr/local/bin --filename=composer
$ composer version -V
> Composer version 1.9.1 2019-11-01 17:20:17

Retrieve data from an API using PHP cURL

Retrieves a list of countries from the World Bank API using cURL PHP library (First 100 countries).

$ php -a

php > $ch = curl_init();
php > curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
php > curl_setopt($ch, CURLOPT_URL,"http://api.worldbank.org/countries?format=json&per_page=100");
php > $result=curl_exec($ch);
php > $countries = json_decode($result)[1];
php > curl_close($ch);
php > var_dump($countries);
array(100) {
  [0]=>
  object(stdClass)#501 (10) {
    ["id"]=>
    string(3) "ABW"
    ["iso2Code"]=>
    string(2) "AW"
    ["name"]=>
    string(5) "Aruba"
    ["region"]=>
    object(stdClass)#500 (2) {
      ["id"]=>
      string(3) "LCN"
      ["value"]=>
      string(26) "Latin America & Caribbean "
    }
    ["adminregion"]=>
    object(stdClass)#499 (2) {
      ["id"]=>
      string(0) ""
      ["value"]=>
      string(0) ""
    }
    ["incomeLevel"]=>
    object(stdClass)#498 (2) {
      ["id"]=>
      string(3) "HIC"
      ["value"]=>
      string(11) "High income"
    }
    ["lendingType"]=>
    object(stdClass)#492 (2) {
      ["id"]=>
      string(3) "LNX"
      ["value"]=>
      string(14) "Not classified"
    }
    ["capitalCity"]=>
    string(10) "Oranjestad"
    ["longitude"]=>
    string(8) "-70.0167"
    ["latitude"]=>
    string(7) "12.5167"
  }
...

Create a SQLite table using PHP

Using PHP and it’s extension for SQLite we are creating a connection to the test.db database. Make sure you have the SQLite extension enabled in your php.ini extension=sqlite3 (without a semicolon at the beginning of the line).

php > var_dump(SQLite3::version());
array(2) {
  ["versionString"]=>
  string(6) "3.28.0"
  ["versionNumber"]=>
  int(3028000)
}

The following PHP lines we’ll allow us to create a table countries with a SQLite-JSON based field called data:

$ php -a

php > $db = new SQLite3('test.db');
php > $db->exec("CREATE TABLE IF NOT EXISTS countries (id varchar(3), data json)");


Insert values into a SQLite table with a JSON column using PHP

Loops array countries and inserts them one by one using PHP foreach statement:

$ php -a

php > $db = new SQLite3('test.db');
php >
php > foreach ($countries as $country) {
php {  $stm = $db->prepare("insert into countries values (?, ?)");
php {  $stm->bindValue(1, $country->id, SQLITE3_TEXT);
php {  $stm->bindValue(2, json_encode($country), SQLITE3_TEXT);
php {  $res = $stm->execute();
php { }
php >

Query JSON in SQLite

Finally, we can query the countries table. For example, getting a list of ISO country codes by accessing the attribute iso2Code on the JSON type data field:

$ apt-get install -y sqlite3 # or equivalent in your OS, if sqlite3 needs to be installed
$ sqlite3 --version
3.22.0 2018-01-22 18:45:57
$ sqlite3 test.db
sqlite> select json_extract(data, '$.iso2Code') from countries;
AW
AF
A9
AO
AL
AD
L5
1A
AE
AR
...

Or fetching randomly one country name:

sqlite> select json_extract(data, '$.name') from countries order by random() limit 1;
Andorra

Although we’ve used PHP in this post, there are lot of other programming languages that can be used to easily query JSON in SQLite like Pyhton