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


  -- 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
  --        literl js true will convert to 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
  --    
  -- 
  -- 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 != null) ret = ret[keys[i]];
    }    
   
  
    if (ret != null) {
      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 != null) 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 != null) 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 != null) 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 != null) 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 != null) 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;