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.
Subscribe to:
Post Comments (Atom)
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:
ReplyDeletevar 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.
You can even write this as a simple SQL function:
ReplyDeletecreate or replace function json_pluck(j json, field text)
returns text[]
language sql
as
$$ 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';
?column?
-------------------
{moe,larry,curly}