Using PLV8 to index JSON

People seem to be getting very excited about JSON in 9.2. So I just tried using the new type in combination with PLV8. It seems to work pretty well. Let's say we want to index a field in our JSON object called "x", so when we query on it the query will run fast. No problem. Here's a very simple function that gets the member out of the JSON:

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

  var ej = JSON.parse(j); // or in later versions of PLV8 just var ej = j;
  if (typeof ej != 'object')
        return NULL;
  return JSON.stringify(ej[key]);

$function$;

In reality we'd want something a bit more sophisticated than this, but you can get the idea from this. Armed with this function we could now create our index, using the functional index feature of PostgreSQL:

CREATE INDEX x_in_json ON mytable (jmember(jsonfield,'x'));

Now, when we issue a query like

SELECT *
FROM mytable
WHERE jmember(jsonfield,'x') = 'foo';

It should be able to use the index. This is reasonably analogous to a very simple use of MongoDB's ensureIndex() function.

We could make this somewhat nicer by providing some operators, and maybe building in a function like this, but the fundamental idea should work pretty much the same.