Just Code‎ > ‎

Postgre SQL - PLV8 functions to query complex json datatype

posted Oct 26, 2012, 7:25 AM by Peter Henell   [ updated Oct 26, 2012, 7:26 AM ]
Simple functions to query complex json structures.

Example usage (imagine the json being complex with many levels, arrays etc):
select *,
jText(evnt, 'body.orders[0].totalCost.customerCurrency') as customerCurrency,
jText(evnt, 'body.orders[0].totalCost.shopCurrency') as storeCurrency
from OrderEventLog

Not perfect just yet but a fun begining!


CREATE or replace FUNCTION jLong (j json, inp text)
RETURNS bigint
LANGUAGE plv8 
IMMUTABLE
AS $function$

var ej = JSON.parse(j);
if (typeof ej != 'object')
    return NULL;
return eval("ej." + inp);
$function$;

CREATE or replace FUNCTION jText (j json, inp text)
RETURNS text
LANGUAGE plv8 
IMMUTABLE
AS $function$

var ej = JSON.parse(j);
if (typeof ej != 'object')
    return NULL;
return JSON.stringify(eval("ej." + inp));
$function$;
Comments