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