Skip to content

Instantly share code, notes, and snippets.

@julienb74

julienb74/postsql.sql

Forked from tobyhede/postsql.sql
Created May 19, 2012 09:02
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save julienb74/2730160 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save julienb74/2730160 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @tobyhede tobyhede revised this gist May 18, 2012. 1 changed file with 4 additions and 2 deletions.
    6 changes: 4 additions & 2 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,10 @@

    -- 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
    --
    @@ -325,4 +327,4 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')

    $$ LANGUAGE plv8 STABLE STRICT;



  2. @tobyhede tobyhede revised this gist May 18, 2012. 1 changed file with 39 additions and 8 deletions.
    47 changes: 39 additions & 8 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -94,11 +94,11 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var len = keys.length;

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


    if (ret != null) {
    if (!ret) {
    ret = ret.toString();
    }

    @@ -116,7 +116,7 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var len = keys.length;

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

    ret = parseInt(ret);
    @@ -135,7 +135,7 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var len = keys.length;

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

    if (! (ret instanceof Array)) {
    @@ -157,7 +157,7 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var len = keys.length;

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

    ret = parseFloat(ret);
    @@ -177,7 +177,7 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var len = keys.length;

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

    if (ret != true || ret != false) ret = null;
    @@ -196,14 +196,14 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var len = keys.length;

    for (var i=0; i<len; ++i) {
    if (ret != null) ret = ret[keys[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;
    if (isNaN(ret.getTime())) ret = null;

    $$ LANGUAGE plv8 IMMUTABLE STRICT;

    @@ -295,3 +295,34 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    $$ 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;


  3. @tobyhede tobyhede revised this gist May 18, 2012. 1 changed file with 13 additions and 5 deletions.
    18 changes: 13 additions & 5 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,4 @@


    -- 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
    @@ -237,15 +236,23 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')

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


    var last_field = data;
    var field = data;

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


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

    return JSON.stringify(data);

    @@ -265,6 +272,7 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var keys = key.split('.')
    var len = keys.length;

    var last_field = data;
    var field = data;

    for (var i=0; i<len; ++i) {
  4. @tobyhede tobyhede revised this gist May 18, 2012. 1 changed file with 21 additions and 5 deletions.
    26 changes: 21 additions & 5 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,5 @@


    -- 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
    @@ -34,8 +35,16 @@
    -- 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)
    -- UPDATE things SET data = json_add_to_set(data, 'object.list', '10');
    -- 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
    @@ -232,7 +241,7 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var field = data;

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


    @@ -259,11 +268,18 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var field = data;

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

    if (field.indexOf(value) == -1) {


    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);
  5. @tobyhede tobyhede revised this gist May 18, 2012. 1 changed file with 30 additions and 2 deletions.
    32 changes: 30 additions & 2 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@

    -- 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
    @@ -218,10 +219,9 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    $$ 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 $$
    json_push(data json, key text, value json) RETURNS JSON AS $$

    var data = JSON.parse(data);
    var value = JSON.parse(value);
    @@ -243,3 +243,31 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    $$ 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 field = data;

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

    if (field.indexOf(value) == -1) {
    field.push(value)
    }

    return JSON.stringify(data);

    $$ LANGUAGE plv8 STABLE STRICT;


  6. @tobyhede tobyhede revised this gist May 18, 2012. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,3 @@

    -- 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
    @@ -35,7 +34,7 @@
    -- will wrap an integer into an array as required
    --
    -- json_add_to_set(column, field, json_value)
    -- UPDATE things_copy SET data = json_add_to_set(data, 'object.list', '10');
    -- UPDATE things SET data = json_add_to_set(data, 'object.list', '10');
    -- Will error if field is not an array.
    --
    -- SAMPLE DATE
  7. @tobyhede tobyhede revised this gist May 18, 2012. 1 changed file with 6 additions and 3 deletions.
    9 changes: 6 additions & 3 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -34,7 +34,10 @@
    -- json_int_array (with others to come)
    -- will wrap an integer into an array as required
    --
    --
    -- json_add_to_set(column, field, json_value)
    -- UPDATE things_copy SET data = json_add_to_set(data, 'object.list', '10');
    -- Will error if field is not an array.
    --
    -- SAMPLE DATE
    -- {
    -- "uuid":"ba596c94-9e50-11e1-a50e-70cd60fffe0e",
    @@ -217,7 +220,7 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')




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

    @@ -228,7 +231,7 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    var len = keys.length;

    var field = data;

    for (var i=0; i<len; ++i) {
    if (field != null) field = field[keys[i]];
    }
  8. @tobyhede tobyhede revised this gist May 18, 2012. 1 changed file with 48 additions and 2 deletions.
    50 changes: 48 additions & 2 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -26,7 +26,7 @@
    -- json_float
    --
    -- json_bool
    -- literl js true will convert to true, other values are falsey
    -- literal js true will convert to PG true, other values are falsey
    --
    -- json_datetime
    -- currently will convert any numeric value into a timestamp
    @@ -194,4 +194,50 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    ret = new Date(ret)
    if (isNaN(ret.getTime()) ret = null;

    $$ LANGUAGE plv8 IMMUTABLE STRICT;
    $$ 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;




    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 field = data;

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


    field.push(value)

    return JSON.stringify(data);

    $$ LANGUAGE plv8 STABLE STRICT;


  9. @tobyhede tobyhede revised this gist May 17, 2012. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -14,7 +14,11 @@
    -- Object =>
    -- null => NULL

    -- USING the following functions:
    -- 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
  10. @tobyhede tobyhede revised this gist May 17, 2012. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -51,6 +51,7 @@
    -- 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
  11. @tobyhede tobyhede revised this gist May 17, 2012. 1 changed file with 9 additions and 1 deletion.
    10 changes: 9 additions & 1 deletion postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -16,12 +16,20 @@

    -- USING the following functions:
    -- json_string
    --
    -- json_int
    --
    -- json_float
    --
    -- json_bool
    -- literl js true will convert to true, other values are falsey
    --
    -- json_datetime
    -- json_int_array (with others to come)
    -- 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
    -- {
  12. @tobyhede tobyhede revised this gist May 17, 2012. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,4 @@


    -- 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
    @@ -115,7 +114,7 @@ SELECT id, json_bool(data,'boolean') FROM things WHERE json_bool(data,'boolean')
    if (ret != null) ret = ret[keys[i]];
    }

    if (! ret instanceof Array) {
    if (! (ret instanceof Array)) {
    ret = [ret];
    }

  13. @tobyhede tobyhede revised this gist May 17, 2012. 1 changed file with 179 additions and 2 deletions.
    181 changes: 179 additions & 2 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,184 @@

    -- 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
    -- 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:
    -- json_string
    -- json_int
    -- json_float
    -- json_bool
    -- json_datetime
    -- json_int_array (with others to come)
    --
    --
    -- 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;
    --
    -- 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;
  14. @tobyhede tobyhede created this gist May 17, 2012.
    8 changes: 8 additions & 0 deletions postsql.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,8 @@


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