Postgresql Native JSON Support - Best of Both SQL & NoSQL Worlds

Postgfresql joined the NoSQL movement way back in September 2012 with the release of Postgresql 9.2 which  introduced native JSON support via the JSON data type  .  Unlike the new kids on the block, rebel-without-a-cause, NoSQL data stores Postgresql hasn't rejected its rich and robust relational database heritage but instead assimilated the technological diversity of the NoSQL world and added them its own technological advantage, like any enterprise technology juggernaut would. Sorry Juan Luc!

Postgresl Native JSON Support - NoSQL with the Benefits of SQL!

What Postgresql 9.1 began, 9.2 continued with the introduction of a rich set of functions to enable translation between the JSON and relational world. In Postgresql 9.3 the JSONB data type was introduced which allows for the creation of indexes and partial indexes on JSONB data types, that is down to a property level, on a JSON object.

Postgres - Write Functions (Stored Procedures) in JavaScript

Along with Postgresql's release of native JSON support in 2012 came an extension that integrated the Google V8 JavaScript engine as a supported language into Postgres, allowing developers to write functions,aka stored procedures (but so much more!), in JavaScript. The extension provides several functions that allow execution of SQL statements directly from JavaScript. 

Enabling Postgresql JavaScript Support

To enable postgres functions written in JavaScript you will need to install and then enable the plv8 extension. On Ubuntu Linux you can install the extension with: (You may need to change the version of Postgresql 9.4 is for 15.04)

sudo apt-get install postgresql-9.4-plv8

You will also have to enable the extension in postgres to enable JavaScript language support. You can do this in the psql command line client after connecting to your database with:

create extension plv8;

Postgresql JSON/REST Example

Let say we have a form that is to capture profile details. It will request, via a rest api, a json array of provinces and related cities to populate a linked drop down list dynamically.  Beside the province and city the form will also capture first name, last name and a tag list of technologies the person is interested in. The application designers want the form to be flexible and new fields should be able to be added over time without having to recode the database back-end.

Creating the Postgresql Function to Return JSON Array of Provinces and Related Cities

To achieve the first goal, of responding with an array of JSON objects to populate the drop downs, we will create a few tables making use of other unique features of Postgresql. If you going to be using the JSON data type, or any other NoSQL database for that matter, you will already have given up on loose coupling at the data store level so we might as well take advantage of all Postgres has to offer. Besides it makes life a lot easier.

First we will create an enumeration of our provinces with:

 create type province as enum ('Eastern Cape','Free State','Gauteng',
                              'KwaZulu Natal','Limpopo','Mpumalanga',
                               'North West','Northern Cape',
                               'Western Cape');

A postgres enum can be appended and changed later with an "alter type province add .." should we so desire. Next we will create the list of cities that belong in each province to enable the HTML 5 front-end to filter the lookup list after the selection of the province.

For this we will use Postgresql's array feature, which allows us to store an array of data types in a database column.

create table province_city (id serial primary key, "province" province, cities text[]);

Now we can populate our province_city table as follows: (Later we can add cities as needed by appending to the array)

insert into province_city (province,cities) values 
    ('Western Cape',array['Cape Town','Franshoek','Hermanus','Stellenbosch']),
    ('Gauteng',array['Alexandra','Johannesburg','Midrand','Soweto']), 
    ('KwaZulu Natal',array['Durban','KwaMashu','Pietermaritzburg','Umhlanga Rocks']);

At this point we have all we need to create our function to return the JSON array of  provinces and related cities. The function is defined below using JavaScript:(Note: We could just send a sql query from the web tier directly and use the built in row_to_json or to_json function to do the conversion for us. We using a function here to show off the plv8.exectue method)

create or replace function getProvinceCities()  returns JSON as
   $$    
         var json_result = plv8.execute('SELECT province,cities FROM province_city');
         return JSON.stringify(json_result);
   $$ LANGUAGE plv8;

So how will this function be called from the web server? Below is an example, in PHP, to call the function from the web tier, usually we would pass the JSON to the response object but we process it in PHP here for demonstration pruposes. You can run this without a web server by typing "php <name-of-php-script>", after adjusting for your setup.

<?php
        try{
                $conn = new PDO('pgsql:host=localhost;port=5432;dbname=demo','postgres','postgres');
                $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
                $prep = $conn-&gt;prepare('select getProvinceCity()');
                $prep->execute();
                $json =  $prep-&gt;fetchColumn(0);
                $obj = json_decode($json,true);
                print_r($obj);    
        } catch(PDOException $e){
                print_r($e);        
        }
?>

So we have completed our first task of providing the data to the Web2.0 front end in JSON format via rest calls.

Saving JSON Objects to Postgres

So the user is presented with a form, she fills out the necessary, selects the province and the filtered city and posts the results back to the web server via an rest post method call. We don't want to dissect the response and then save the individual properties into columns in a table because the user profile section keep changing with fields being added and taken away on a regular basis. The only fields that are more-or-less stable are the core fields of Province, City, First Name and Last Name.

To accommodate this requirement we will store the JSON response as is, in the Postgres table using the JSONB data type which will allow us to index and query the data.

create table people (id serial primary key,person jsonb);

We can now create our function to populate this table. We could populate the table directly via a sql statement but we want to perform some validation to ensure we have a valid province and city first. Below is the script to create our insertPerson function which will insert a person JSON object into our Postgresql table after validation, throwing an error if there is a violation.

create or replace function insertPerson(person json) returns void as
$$
        var provinces = plv8.execute('select enum_range(enum_first(null::province),null::province)');
        if (provinces[0].enum_range.indexOf(person.province)!=-1){
                var cities = plv8.execute('select cities from province_city where province =$1',person.province);
                if (cities[0].cities.indexOf(person.city)!=-1){
                        plv8.execute('insert into people (person) values($1)',JSON.stringify(person));
                }else{
                        throw "Invalid city";
                }
        }else{
                throw "Invalid Province";
        }
$$
language plv8;

If the object passed validation we persist the JSON object to the people table. We can test our function from the psql cli client with:

 select insertPerson('{
                       "firstname":"Bob",
                       "lastname":"Marley",
                       "province":"Gauteng",
                       "city":"Johannesburg", 
                       "tech":["Java","PHP","Linux","Postgres"]
                     }'
                   );

How do we call this function from the web tier? Here again we have a PHP script which receives the posted JSON data and persists it to the database. This script creates the JSON object for demo purposes in PHP. It shows what happens when an valid json object is presented, what happens when an  invalid province is received and how the JSON type can change between calls without requiring a schema change on the database side.

<?php
        try{
                //create a test json object. This should be retrieved from the post.                
                $person=array(
                        'firstname'=>'Ludwig',
                        'lastname'=>'Beethoven',
                        'province'=>'Gauteng',
                        'city'=>'Johannesburg',
                        'tech'=>["Java","PHP","Linux","Postgres"]
                );
                $json = json_encode($person);
                //send the json object to postgres for validation and persistence
                $conn = new PDO('pgsql:host=localhost;port=5432;dbname=demo','postgres','postgres');
                $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
                $prep = $conn->prepare('select insertPerson(:json)');
                $prep->bindParam(':json',    $json);
                $prep->execute();
                echo "SUCCESSFULLY INSERTED RECORD".PHP_EOL;
       } catch(PDOException $e){
                echo "VALIDATION ERROR".PHP_EOL;
                echo $e->getMessage().PHP_EOL;        
       }
 
       try {
                //create a invalid json object.
                //should throw an exception               
                $person=array(
                        'firstname'=>'Fredrick',
                        'lastname'=>'Chopin',
                        'province'=>'Pretoria',
                        'city'=>'Johannesburg',
                        'tech'=>["Drupal","JBOSS"]
                );
                $json = json_encode($person);
                $prep = $conn->prepare('select insertPerson(:json)');
                $prep->bindParam(':json',    $json);
                $prep->execute();         
        } catch(PDOException $e){
                echo "VALIDATION ERROR".PHP_EOL;
                echo $e->getMessage().PHP_EOL;        
        }
 
        try{
                //create a json object with extended attributes. This should be retrieved from the post.                
                $person=array(
                        'firstname'=>'Johan',
                        'lastname'=>'Strauss',
                        'province'=>'Gauteng',
                        'city'=>'Johannesburg',
                        'tech'=>["Java","PHP","Linux","Postgres"],
                        'email'=>'[email protected]'
                );
                $json = json_encode($person);
                //send the json object to postgres for validation and persistence
                $conn = new PDO('pgsql:host=localhost;port=5432;dbname=demo','postgres','postgres');
                $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
                $prep = $conn->prepare('select insertPerson(:json)');
                $prep->bindParam(':json',    $json);
                $prep->execute();
                echo "SUCCESSFULLY INSERTED RECORD".PHP_EOL;
       } catch(PDOException $e){
                echo "VALIDATION ERROR".PHP_EOL;
                echo $e->getMessage().PHP_EOL;        
       }
?>

Postgresql Not Your Every Day Enterprise Database

I hope this article showed what is possible with Postgresql. The example is a bit contrived by demonstrates what is possible when a great relational database engine is combined with NoSQL principles. We haven't even looked at querying the JSON data - I will deal with that in a future post.

Comments

If you really want people to get the best out of your blog, please stick to high contrast, readable fonts. Your current choices only detract from your information.

It's not really readable, started reading twice, gave up. (Oh, and if you don't notice  the captcha, the comment field gets cleared :/ )