redshift_json_udf.sql

CREATE OR REPLACE FUNCTION CLEAN_DEEP_JSON (j VARCHAR(65535), f VARCHAR(65535))
RETURNS VARCHAR(MAX)
STABLE AS $$
  import json
  if not j:
    return None
  try:
    arr = json.loads(j)
  except valueerror:
    return None
  fs = f.split('$')
  for l in fs:
    if l in arr:
      arr = arr[l]
    else:
      return None
  return str(arr)
$$ LANGUAGE PLPYTHONU;