Sunday, December 30, 2012

Json API work

Following a couple of requests, I've pushed a copy of my Json API development repo to Bitbucket. To play with it, pull the "jsonapi" branch. It has a number of interesting new goodies, including json_each() and just yesterday I added json_get_path() which takes a variadic sequence of path values to descend the json tree (no reparsing required). Today I'm working on populate_record(), very thankful that I can re-use a lot of hstore's code for this. After that, json_unnest() to pull apart json arrays will just about complete the major work, and I can start filling in gaps, cleaning code, making more comprehensive tests, and so on.


  1. Nice. Even though the number of available functions are low, many of the most common uses of JSON are now unlocked through a few combinations of the above functions. One popular example is "pluck" which is basically json_unnest then json_get, such as this:

    var stooges = [{name : 'moe', age : 40}, {name : 'larry', age : 50}, {name : 'curly', age : 60}];
    _.pluck(stooges, 'name');
    => ["moe", "larry", "curly"]

    I'll go through some of the libaries like underscore.js to see which things I'd commonly do with JSON but still have to pull out of the database and loop over due to awkwardness of doing them right in sql.

  2. You can even write this as a simple SQL function:

    create or replace function json_pluck(j json, field text)
    returns text[]
    language sql
    $$ select array_agg(j->>$2) from json_unnest($1) j $$

    and then for even more fun make it an operator:

    create operator | (procedure = json_pluck, leftarg = 'json', rightarg = 'text');

    and then you can do:

    andrew=# select json '[{"name" : "moe", "age" : 40}, {"name" : "larry", "age" : 50}, {"name" : "curly", "age" : 60}]' | 'name';