json_operator.sql

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