Skip to content

Instantly share code, notes, and snippets.

@choplin
Created May 17, 2012 14:30
Show Gist options
  • Save choplin/2719269 to your computer and use it in GitHub Desktop.
Save choplin/2719269 to your computer and use it in GitHub Desktop.
operator to json type in PostgreSQL with plv8
CREATE OR REPLACE FUNCTION json_access(obj json, path text) RETURNS json AS $$
var obj = JSON.parse(obj);
var paths = path.split(".");
var p;
var ret;
while (p = paths.shift()) {
if (typeof obj[p] == 'undefined'){
obj = null;
break
}
obj = obj[p];
}
if (typeof obj == 'object' && obj != null) {
ret = JSON.stringify(obj);
} else if (typeof obj == 'boolean') {
ret = obj.toString();
} else {
ret = obj;
}
return ret;
$$ LANGUAGE plv8 IMMUTABLE STRICT;
CREATE OPERATOR @ (
LEFTARG = json,
RIGHTARG = text,
PROCEDURE = json_access
);
$ SELECT '{"a":1}'::json @ 'a' -- 1
$ SELECT '{"a":true}'::json @ 'a' -- true
$ SELECT '{"a":null}'::json @ 'a' -- NULL
$ SELECT '{"a":[1,2,3]}'::json @ 'a' -- [1,2,3]
$ SELECT '{"a":[1,2,3]}'::json @ 'a.1' -- 2
$ SELECT '{"a":{"b":1}}'::json @ 'a' -- {"b":1}
$ SELECT '{"a":{"b":1}}'::json @ 'a.b' -- 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment