Javascript Functions for PostgreSQL
Javascript in Postgres has gotten a good bit of love lately, part of that is from Heroku Postgres recently adding support for Javascript and part from a variety of people championing the power of it such as @leinweber (Embracing the web with JSON and PLV8) and @selenamarie (schema liberation with JSON and PLV8). In a recent conversation it was pointed out that it seems a bit of headache to have to create your own functions, or at least having an initial collection would make it that much more powerful. While many can look forward to PostgreSQL 9.3 which will have a bit more built in support for JSON a few functions can really help make it more useful today.
These are courtesy of Will Leinweber. For each of the following functions I’ll highlight an example of using it as well. To get an idea of the data its being run on:
select * from example;
data
--------------------------------------------
{"name":"Craig Kerstiens", +
"age":27, +
"siblings":1, +
"numbers":[ +
{"type":"work", +
"number":"123-456-7890"}, +
{"type":"home", +
"number":"456-123-7890"}]}
(1 row)
get_text
CREATE OR REPLACE FUNCTION
get_text(key text, data json)
RETURNS text AS $$
return data[key];
$$ LANGUAGE plv8 IMMUTABLE STRICT;
Then using the function:
select get_text('name', data) from example;
get_text
----------------
Craig Kerstiens
(1 row)
get_numeric
CREATE OR REPLACE FUNCTION
get_numeric(key text, data json)
RETURNS numeric AS $$
return data[key];
$$ LANGUAGE plv8 IMMUTABLE STRICT;
Then using the function:
select get_numeric('siblings', data) from example;
get_text
----------------
1
(1 row)
json_select
create or replace function
json_select(selector text, data json)
returns json as $$
exports = {};
(function(a){function z(a){return{sel:q(a)[1],match:function(a){return y(this.sel,a)},forEach:function(a,b){return x(this.sel,a,b)}}}function y(a,b){var c=[];x(a,b,function(a){c.push(a)});return c}function x(a,b,c,d,e,f){var g=a[0]===","?a.slice(1):[a],h=[],i=!1,j=0,k=0,l,m;for(j=0;j<g.length;j++){m=w(b,g[j],d,e,f),m[0]&&(i=!0);for(k=0;k<m[1].length;k++)h.push(m[1][k])}if(h.length&&typeof b=="object"){h.length>=1&&h.unshift(",");if(u(b))for(j=0;j<b.length;j++)x(h,b[j],c,undefined,j,b.length);else for(l in b)b.hasOwnProperty(l)&&x(h,b[l],c,l)}i&&c&&c(b)}function w(a,b,c,d,e){var f=[],g=b[0]===">"?b[1]:b[0],h=!0,i;g.type&&(h=h&&g.type===v(a)),g.id&&(h=h&&g.id===c),h&&g.pf&&(g.pf===":nth-last-child"?d=e-d:d++,g.a===0?h=g.b===d:(i=(d-g.b)%g.a,h=!i&&d*g.a+g.b>=0));if(h&&g.has){var j=function(){throw 42};for(var k=0;k<g.has.length;k++){try{x(g.has[k],a,j)}catch(l){if(l===42)continue}h=!1;break}}h&&g.expr&&(h=p(g.expr,a)),b[0]!==">"&&b[0].pc!==":root"&&f.push(b),h&&(b[0]===">"?b.length>2&&(h=!1,f.push(b.slice(2))):b.length>1&&(h=!1,f.push(b.slice(1))));return[h,f]}function v(a){if(a===null)return"null";var b=typeof a;b==="object"&&u(a)&&(b="array");return b}function u(a){return Array.isArray?Array.isArray(a):b.call(a)==="[object Array]"}function t(a,b,c){var d=b,g={},j=i(a,b);j&&j[1]===" "&&(d=b=j[0],j=i(a,b)),j&&j[1]===f.typ?(g.type=j[2],j=i(a,b=j[0])):j&&j[1]==="*"&&(j=i(a,b=j[0]));for(;;){if(j===undefined)break;if(j[1]===f.ide)g.id&&e("nmi",j[1]),g.id=j[2];else if(j[1]===f.psc)(g.pc||g.pf)&&e("mpc",j[1]),j[2]===":first-child"?(g.pf=":nth-child",g.a=0,g.b=1):j[2]===":last-child"?(g.pf=":nth-last-child",g.a=0,g.b=1):g.pc=j[2];else{if(j[1]!==f.psf)break;if(j[2]===":val"||j[2]===":contains")g.expr=[undefined,j[2]===":val"?"=":"*=",undefined],j=i(a,b=j[0]),j&&j[1]===" "&&(j=i(a,b=j[0])),(!j||j[1]!=="(")&&e("pex",a),j=i(a,b=j[0]),j&&j[1]===" "&&(j=i(a,b=j[0])),(!j||j[1]!==f.str)&&e("sex",a),g.expr[2]=j[2],j=i(a,b=j[0]),j&&j[1]===" "&&(j=i(a,b=j[0])),(!j||j[1]!==")")&&e("epex",a);else if(j[2]===":has"){j=i(a,b=j[0]),j&&j[1]===" "&&(j=i(a,b=j[0])),(!j||j[1]!=="(")&&e("pex",a);var k=q(a,j[0],!0);j[0]=k[0],g.has||(g.has=[]),g.has.push(k[1])}else if(j[2]===":expr"){g.expr&&e("mexp",a);var l=o(a,j[0]);j[0]=l[0],g.expr=l[1]}else{(g.pc||g.pf)&&e("mpc",a),g.pf=j[2];var m=h.exec(a.substr(j[0]));m||e("mepf",a),m[5]?(g.a=2,g.b=m[5]==="odd"?1:0):m[6]?(g.a=0,g.b=parseInt(m[6],10)):(g.a=parseInt((m[1]?m[1]:"+")+(m[2]?m[2]:"1"),10),g.b=m[3]?parseInt(m[3]+m[4],10):0),j[0]+=m[0].length}}j=i(a,b=j[0])}d===b&&e("se",a);return[b,g]}function s(a){if(a[0]===","){var b=[","];for(var c=c;c<a.length;c++){var d=r(d[c]);b=b.concat(d[0]===","?d.slice(1):d)}return b}return r(a)}function r(a){var b=[],c;for(var d=0;d<a.length;d++)if(a[d]==="~"){if(d<2||a[d-2]!=">")c=a.slice(0,d-1),c=c.concat([{has:[[{pc:":root"},">",a[d-1]]]},">"]),c=c.concat(a.slice(d+1)),b.push(c);if(d>1){var e=a[d-2]===">"?d-3:d-2;c=a.slice(0,e);var f={};for(var g in a[e])a[e].hasOwnProperty(g)&&(f[g]=a[e][g]);f.has||(f.has=[]),f.has.push([{pc:":root"},">",a[d-1]]),c=c.concat(f,">",a.slice(d+1)),b.push(c)}break}if(d==a.length)return a;return b.length>1?[","].concat(b):b[0]}function q(a,b,c,d){c||(d={});var f=[],g,h;b||(b=0);for(;;){var j=t(a,b,d);f.push(j[1]),j=i(a,b=j[0]),j&&j[1]===" "&&(j=i(a,b=j[0]));if(!j)break;if(j[1]===">"||j[1]==="~")j[1]==="~"&&(d.usesSiblingOp=!0),f.push(j[1]),b=j[0];else if(j[1]===",")g===undefined?g=[",",f]:g.push(f),f=[],b=j[0];else if(j[1]===")"){c||e("ucp",j[1]),h=1,b=j[0];break}}c&&!h&&e("mcp",a),g&&g.push(f);var k;!c&&d.usesSiblingOp?k=s(g?g:f):k=g?g:f;return[b,k]}function p(a,b){if(a===undefined)return b;if(a===null||typeof a!="object")return a;var c=p(a[0],b),d=p(a[2],b);return l[a[1]][1](c,d)}function o(a,b){function c(a){return typeof a!="object"||a===null?a:a[0]==="("?c(a[1]):[c(a[0]),a[1],c(a[2])]}var d=n(a,b?b:0);return[d[0],c(d[1])]}function n(a,b){b||(b=0);var c=m(a,b),d;if(c&&c[1]==="("){d=n(a,c[0]);var f=m(a,d[0]);(!f||f[1]!==")")&&e("epex",a),b=f[0],d=["(",d[1]]}else!c||c[1]&&c[1]!="x"?e("ee",a+" - "+(c[1]&&c[1])):(d=c[1]==="x"?undefined:c[2],b=c[0]);var g=m(a,b);if(!g||g[1]==")")return[b,d];(g[1]=="x"||!g[1])&&e("bop",a+" - "+(g[1]&&g[1]));var h=n(a,g[0]);b=h[0],h=h[1];var i;if(typeof h!="object"||h[0]==="("||l[g[1]][0]<l[h[1]][0])i=[d,g[1],h];else{i=h;while(typeof h[0]=="object"&&h[0][0]!="("&&l[g[1]][0]>=l[h[0][1]][0])h=h[0];h[0]=[d,g[1],h[0]]}return[b,i]}function m(a,b){var d,e=j.exec(a.substr(b));if(e){b+=e[0].length,d=e[1]||e[2]||e[3]||e[5]||e[6];if(e[1]||e[2]||e[3])return[b,0,c(d)];if(e[4])return[b,0,undefined];return[b,d]}}function k(a,b){return typeof a===b}function i(a,b){b||(b=0);var d=g.exec(a.substr(b));if(!d)return undefined;b+=d[0].length;var h;d[1]?h=[b," "]:d[2]?h=[b,d[0]]:d[3]?h=[b,f.typ,d[0]]:d[4]?h=[b,f.psc,d[0]]:d[5]?h=[b,f.psf,d[0]]:d[6]?e("upc",a):d[8]?h=[b,d[7]?f.ide:f.str,c(d[8])]:d[9]?e("ujs",a):d[10]&&(h=[b,f.ide,d[10].replace(/\\([^\r\n\f0-9a-fA-F])/g,"$1")]);return h}function e(a,b){throw new Error(d[a]+(b&&" in '"+b+"'"))}function c(a){try{if(JSON&&JSON.parse)return JSON.parse(a);return(new Function("return "+a))()}catch(b){e("ijs",b.message)}}var b=Object.prototype.toString,d={bop:"binary operator expected",ee:"expression expected",epex:"closing paren expected ')'",ijs:"invalid json string",mcp:"missing closing paren",mepf:"malformed expression in pseudo-function",mexp:"multiple expressions not allowed",mpc:"multiple pseudo classes (:xxx) not allowed",nmi:"multiple ids not allowed",pex:"opening paren expected '('",se:"selector expected",sex:"string expected",sra:"string required after '.'",uc:"unrecognized char",ucp:"unexpected closing paren",ujs:"unclosed json string",upc:"unrecognized pseudo class"},f={psc:1,psf:2,typ:3,str:4,ide:5},g=new RegExp('^(?:([\\r\\n\\t\\ ]+)|([~*,>\\)\\(])|(string|boolean|null|array|object|number)|(:(?:root|first-child|last-child|only-child))|(:(?:nth-child|nth-last-child|has|expr|val|contains))|(:\\w+)|(?:(\\.)?(\\"(?:[^\\\\\\"]|\\\\[^\\"])*\\"))|(\\")|\\.((?:[_a-zA-Z]|[^\\0-\\0177]|\\\\[^\\r\\n\\f0-9a-fA-F])(?:[_a-zA-Z0-9\\-]|[^\\u0000-\\u0177]|(?:\\\\[^\\r\\n\\f0-9a-fA-F]))*))'),h=/^\s*\(\s*(?:([+\-]?)([0-9]*)n\s*(?:([+\-])\s*([0-9]))?|(odd|even)|([+\-]?[0-9]+))\s*\)/,j=new RegExp('^\\s*(?:(true|false|null)|(-?\\d+(?:\\.\\d*)?(?:[eE][+\\-]?\\d+)?)|("(?:[^\\]|\\[^"])*")|(x)|(&&|\\|\\||[\\$\\^<>!\\*]=|[=+\\-*/%<>])|([\\(\\)]))'),l={"*":[9,function(a,b){return a*b}],"/":[9,function(a,b){return a/b}],"%":[9,function(a,b){return a%b}],"+":[7,function(a,b){return a+b}],"-":[7,function(a,b){return a-b}],"<=":[5,function(a,b){return k(a,"number")&&k(b,"number")&&a<=b}],">=":[5,function(a,b){return k(a,"number")&&k(b,"number")&&a>=b}],"$=":[5,function(a,b){return k(a,"string")&&k(b,"string")&&a.lastIndexOf(b)===a.length-b.length}],"^=":[5,function(a,b){return k(a,"string")&&k(b,"string")&&a.indexOf(b)===0}],"*=":[5,function(a,b){return k(a,"string")&&k(b,"string")&&a.indexOf(b)!==-1}],">":[5,function(a,b){return k(a,"number")&&k(b,"number")&&a>b}],"<":[5,function(a,b){return k(a,"number")&&k(b,"number")&&a<b}],"=":[3,function(a,b){return a===b}],"!=":[3,function(a,b){return a!==b}],"&&":[2,function(a,b){return a&&b}],"||":[1,function(a,b){return a||b}]};a._lex=i,a._parse=q,a.match=function(a,b){return z(a).match(b)},a.forEach=function(a,b,c){return z(a).forEach(b,c)},a.compile=z})(typeof exports=="undefined"?window.JSONSelect={}:exports)
return JSON.stringify(
exports.match(selector,
data));
$$ LANGUAGE plv8 IMMUTABLE STRICT
Then using the function:
select json_select('.name nth-child(1)', data) as name, json_select('.numbers', data) as phone
from example;
name | phone
--------------------+------------------------------------------------------------------------------------------
["Craig Kerstiens"] | [[{"type":"work","number":"456-123-7890"},{"type":"home","number":"123-456-7890"}]]
(1 row)
javascript injection attack
create or replace function
js(src text) returns text as $$
return eval(
"(function() { " + src + "})"
)();
$$ LANGUAGE plv8;
Have any others you feel are essential when starting to work with JSON? Let me know craig.kerstiens@gmail.com. Beyond that give JSON and JavaScript a try inside your database.