tag:blogger.com,1999:blog-2356137376934964551.post5635199163797845946..comments2023-06-14T06:01:24.499-04:00Comments on Andrew Dunstan's PostgreSQL and Technical blog: Json API workAndrew Dunstanhttp://www.blogger.com/profile/04684286585449188201noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-2356137376934964551.post-14540069155677722372013-01-16T13:58:08.035-05:002013-01-16T13:58:08.035-05:00You can even write this as a simple SQL function:
...You can even write this as a simple SQL function:<br /><br />create or replace function json_pluck(j json, field text)<br />returns text[]<br />language sql<br />as<br />$$ select array_agg(j->>$2) from json_unnest($1) j $$<br />;<br /><br />and then for even more fun make it an operator:<br /><br />create operator | (procedure = json_pluck, leftarg = 'json', rightarg = 'text');<br /><br />and then you can do:<br /><br />andrew=# select json '[{"name" : "moe", "age" : 40}, {"name" : "larry", "age" : 50}, {"name" : "curly", "age" : 60}]' | 'name';<br />?column?<br />-------------------<br />{moe,larry,curly}Andrew Dunstanhttps://www.blogger.com/profile/04684286585449188201noreply@blogger.comtag:blogger.com,1999:blog-2356137376934964551.post-10386951550987434662013-01-14T18:05:06.064-05:002013-01-14T18:05:06.064-05:00Nice. Even though the number of available functio...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:<br /><br />var stooges = [{name : 'moe', age : 40}, {name : 'larry', age : 50}, {name : 'curly', age : 60}];<br />_.pluck(stooges, 'name');<br />=> ["moe", "larry", "curly"]<br /><br />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.tmanhttps://www.blogger.com/profile/01893199104573379008noreply@blogger.com