zot24
5/17/2012 - 3:08 AM
PostgreSQL as JSON Document Store
PostgreSQL as JSON Document Store
-- 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;