JSON in SQLite using PHP

Before you begin

In this tutorial, we’ll learn how to store a JSON in a SQLite table with JSON data type column using PHP. A GNU Linux/Mac OS machine with cURL, 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:

# Composer install
$ 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). You can also use phpinfo(); inside of a PHP file or run php -m | grep sqlite to ensure the sqlite3 module is enabled. If sqlite doesn’t appear in the list you might need to install the OS package php-pdo as well.

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 SQL 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 >

Finally, we can check if the database has values on it by querying the table countries. In the following example we are using PHP to query the first result and pretty-printing the JSON value:

$ composer require camspiers/json-pretty
$ php -a

php > require_once __DIR__ . '/vendor/autoload.php';
php > $jsonPretty = new Camspiers\JsonPretty\JsonPretty;
php > $db = new SQLite3('test.db');
php > echo $jsonPretty->prettify($db->querySingle('SELECT data from countries'));
{
	"id":  "ABW",
	 "iso2Code":  "AW",
	 "name":  "Aruba",
	 "region":  {
		"id":  "LCN",
		 "value":  "Latin America & Caribbean "
	},
	 "adminregion":  {
		"id":  "",
		 "value":  ""
	},
	 "incomeLevel":  {
		"id":  "HIC",
		 "value":  "High income"
	},
	 "lendingType":  {
		"id":  "LNX",
		 "value":  "Not classified"
	},
	 "capitalCity":  "Oranjestad",
	 "longitude":  "-70.0167",
	 "latitude":  "12.5167"
}

Query JSON values 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
...

Selecting country names in SQL with values between ‘Eb’ and ‘Et’ inside of the JSON data field:

$ sqlite3 test.db
sqlite> select json_extract(data, '$.name') from countries
where json_extract(data, '$.name') between 'Eb' and 'Et'
order by json_extract(data, '$.name') asc;
Ecuador
Egypt, Arab Rep.
Eritrea
Estonia

Or fetching randomly one country name:

$ sqlite3 test.db
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