Postgre SQL - PLV8 functions to query complex json datatype

Post date: Oct 26, 2012 2:25:55 PM

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 bigintLANGUAGE 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$;