PostgreSQL as JSON Document Store

postsql.sql

 -- PostgreSQL 9.2 beta (for the new JSON datatype)
 --   You can actually use an earlier version and a TEXT type too
 -- PL/V8 http://code.google.com/p/plv8js/wiki/PLV8

 -- Inspired by 
 -- http://people.planetpostgresql.org/andrew/index.php?/archives/249-Using-PLV8-to-index-JSON.html
 -- http://ssql-pgaustin.herokuapp.com/#1

  -- JSON Types need to be mapped into corresponding PG types 
  -- 
  -- Number     => INT or DOUBLE PRECISION
  -- String     => TEXT
  -- Date       => TIMESTAMP
  -- Boolean    => BOOLEAN
  -- Array      => ARRAY of appropriate PG Type 
  -- Object     => 
  -- null       => NULL

  --  USING the following functions:
  --  
  --  Each function takes a JSON column and a field to access as string 
  --  Nested fields can be access as well eg "person.name"
  --
  --    json_string
  --  
  --    json_int
  --
  --    json_float
  --
  --    json_bool
  --        literal js true will convert to PG true, other values are falsey
  --
  --    json_datetime
  --        currently will convert any numeric value into a timestamp
  --
  --    json_int_array (with others to come)
  --        will wrap an integer into an array as required
  --    
  --    json_push(column, field, json_value)
  --        Appends value to an array
  --        or if field is not present sets field to the array json_value
  --        UPDATE things SET data = json_add_to_set(data, 'array', '10');
  --        Will error if field is not an array.
  --
  --    json_add_to_set(column, field, json_value)
  --        Appends value to an array only if its not in the array already
  --        or if field is not present sets field to the array json_value
  --        UPDATE things SET data = json_add_to_set(data, 'object.array', '10');
  --        Will error if field is not an array.
  --
  -- SAMPLE DATE
  -- {
  --   "uuid":"ba596c94-9e50-11e1-a50e-70cd60fffe0e",
  --   "integer":10,
  --   "string":"Blick",      
  --   "date":"2012-05-11T15:42:15+10:00",
  --   "boolean":true,
  --   "numeric":99.9,
  --   "object":{
  --     "string":"Ullrich",
  --     "array":[3428,7389,5166,5823,3566,6086,3087,7690,6374,4531,6019,9722,8793,6732,5264,9618,5843,6714,5160,4065,2102,4972,2778,6110,4357,4385,1296,7981,607,3104,4992,8207,7517,1932,8097,2626,5196,425,8803,4778,7814,5337,9467,200,3542,4001,5930,4646,7304,4033,4838,7539,648,7016,6377,7957,7411,4023,7105,3676,9195,2337,8259,9166,9972,4740,7705,5368,5815,2592,5569,4842,6577,3805,1473,8585,9371,8732,9491,3819,7517,3437,6342,3397,8603,5324,676,7922,813,9850,8032,9324,733,5436,2971,9878,1648,6248,2109,1422]
  --   }
  -- }
  --  
  --
  -- Using the correctly typed accessor allows PG's normal operators to JUST WORK. 
  --
  --    SELECT id, json_string(data,'name') FROM things WHERE json_string(data,'name') LIKE 'G%';
  --    SELECT id, json_int(data,'count') FROM things WHERE json_int(data,'count') = 10;
  -- 
  --    Including ARRAY operators and FUNCTIONS:
  --    SELECT id, (json_int_array(data,'object.list') FROM things WHERE 10 = ALL (json_int_array(data,'object.list'))
  --
  --  Creating an index makes performance on-par with regular PG columnn data
  -- 
  --    CREATE INDEX name_in_json ON things (json_string(data,'name'));


SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean') = false LIMIT 10;


  CREATE TABLE "public"."things" (
    "id" int4 NOT NULL DEFAULT nextval('things_id_seq'::regclass),
    "created_at" timestamp(6) NOT NULL,
    "updated_at" timestamp(6) NOT NULL,
    "data" json NOT NULL,
    CONSTRAINT "things_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE
  );

 
  CREATE or REPLACE FUNCTION 
  json_string(data json, key text) RETURNS TEXT AS $$

    var ret = JSON.parse(data); 
    var keys = key.split('.')
    var len = keys.length;
    
    for (var i=0; i<len; ++i) {
      if (!ret) ret = ret[keys[i]];
    }    
   
  
    if (!ret) {
      ret = ret.toString();
    }

    return ret;

  $$ LANGUAGE plv8 IMMUTABLE STRICT;



  CREATE or REPLACE FUNCTION 
  json_int(data json, key text) RETURNS INT AS $$

    var ret = JSON.parse(data); 
    var keys = key.split('.')
    var len = keys.length;
    
    for (var i=0; i<len; ++i) {
      if (!ret) ret = ret[keys[i]];
    }    
   
    ret = parseInt(ret);   
    if (isNaN(ret)) ret = null;

    return ret;

  $$ LANGUAGE plv8 IMMUTABLE STRICT;


  CREATE or REPLACE FUNCTION 
  json_int_array(data json, key text) RETURNS INT[] AS $$

    var ret = JSON.parse(data); 
    var keys = key.split('.')
    var len = keys.length;
    
    for (var i=0; i<len; ++i) {
      if (!ret) ret = ret[keys[i]];
    }    
   
    if (! (ret instanceof Array)) {
      ret = [ret];
    }
    
    return ret;

  $$ LANGUAGE plv8 IMMUTABLE STRICT;




  CREATE or REPLACE FUNCTION 
  json_float(data json, key text) RETURNS DOUBLE PRECISION AS $$

    var ret = JSON.parse(data); 
    var keys = key.split('.')
    var len = keys.length;
    
    for (var i=0; i<len; ++i) {
      if (!ret) ret = ret[keys[i]];
    }    
   
    ret = parseFloat(ret);   
    if (isNaN(ret)) ret = null;

    return ret;

  $$ LANGUAGE plv8 IMMUTABLE STRICT;



  CREATE or REPLACE FUNCTION 
  json_bool(data json, key text) RETURNS BOOLEAN AS $$

    var ret = JSON.parse(data); 
    var keys = key.split('.')
    var len = keys.length;
    
    for (var i=0; i<len; ++i) {
      if (!ret) ret = ret[keys[i]];
    }    
   
    if (ret != true || ret != false) ret = null;

    return ret;

  $$ LANGUAGE plv8 IMMUTABLE STRICT;



  CREATE or REPLACE FUNCTION 
  json_datetime(data json, key text) RETURNS TIMESTAMP AS $$

    var ret = JSON.parse(data); 
    var keys = key.split('.')
    var len = keys.length;
    
    for (var i=0; i<len; ++i) {
      if (!ret) ret = ret[keys[i]];
    }    

    //ret = Date.parse(ret)
    //if (isNaN(ret)) ret = null;
  
    ret = new Date(ret)
    if (isNaN(ret.getTime())) ret = null;

  $$ LANGUAGE plv8 IMMUTABLE STRICT;




  CREATE or REPLACE FUNCTION 
  json_update(data json, value text) RETURNS BOOLEAN AS $$

    var data = JSON.parse(data); 
    var forUpdate = JSON.parse(value); 

    for (k in forUpdate) {
      if ( data.hasOwnProperty(k) ) {
        data[k] = forUpdate[k];
      }
    }    

    return true;

  $$ LANGUAGE plv8 STABLE STRICT;


  -- USAGE: UPDATE things SET data = json_add_to_set(data, 'object.list', '10');
  CREATE or REPLACE FUNCTION 
  json_push(data json, key text, value json) RETURNS JSON AS $$

    var data = JSON.parse(data); 
    var value = JSON.parse(value); 

    var keys = key.split('.')
    var len = keys.length;
      
    var last_field = data;
    var field = data;

    for (var i=0; i<len; ++i) {    
      last_field = field;    
      if (field) field = field[keys[i]];
    }    

    if (field) { 
      field.push(value)
    } else {      
      if (! (value instanceof Array)) {
        value = [value];
      }
      last_field[keys.pop()]= value;
    }

  return JSON.stringify(data);
   
  $$ LANGUAGE plv8 STABLE STRICT;

  



  -- USAGE: UPDATE things SET data = json_add_to_set(data, 'object.list', '10');
  CREATE or REPLACE FUNCTION 
  json_add_to_set(data json, key text, value json) RETURNS JSON AS $$

    var data = JSON.parse(data); 
    var value = JSON.parse(value); 

    var keys = key.split('.')
    var len = keys.length;
    
    var last_field = data;
    var field = data;

    for (var i=0; i<len; ++i) {
      last_field = field;    
      if (field) field = field[keys[i]];
    }    


    if (field && field.indexOf(value) == -1) {
      field.push(value)
    } else {
      if (! (value instanceof Array)) {
        value = [value];
      }
      last_field[keys.pop()]= value;
    }

  return JSON.stringify(data);
   
  $$ LANGUAGE plv8 STABLE STRICT;

  

  -- USAGE: UPDATE things SET data = json_add_to_set(data, 'object.list', '10');
  CREATE or REPLACE FUNCTION 
  json_pull(data json, key text, value json) RETURNS JSON AS $$

    var data = JSON.parse(data); 
    var value = JSON.parse(value); 

    var keys = key.split('.')
    var len = keys.length;
    
    var field = data;

    for (var i=0; i<len; ++i) {    
      if (field) field = field[keys[i]];
    }    

    if (field) {
      var idx = field.indexOf(value);
      
      if (idx != -1) {
        field.slice(idx);
      }      
    }


  return JSON.stringify(data);
   
  $$ LANGUAGE plv8 STABLE STRICT;