Playing around with JSON and json_enhancements

json_example.sql

You are now connected to database "postgres" as user "postgres".
postgres:5432# create table birds (bird_info json);
CREATE TABLE   
Time: 162.227 ms
postgres:5432# insert into birds VALUES( '{"name": "scrubjay", "where": "my backyard", "when": "2013-05-24"}');
INSERT 0 1
Time: 0.616 ms 
postgres:5432# select * from birds;
                             bird_info
--------------------------------------------------------------------
 {"name": "scrubjay", "where": "my backyard", "when": "2013-05-24"}
(1 row)

Time: 12.114 ms
postgres:5432# \df *json*
                              List of functions
   Schema   |     Name      | Result data type | Argument data types |  Type
------------+---------------+------------------+---------------------+--------
 pg_catalog | array_to_json | json             | anyarray            | normal
 pg_catalog | array_to_json | json             | anyarray, boolean   | normal
 pg_catalog | json_in       | json             | cstring             | normal
 pg_catalog | json_out      | cstring          | json                | normal
 pg_catalog | json_recv     | json             | internal            | normal
 pg_catalog | json_send     | bytea            | json                | normal
 pg_catalog | row_to_json   | json             | record              | normal
 pg_catalog | row_to_json   | json             | record, boolean     | normal
(8 rows)

postgres:5432# create extension hstore; 
CREATE EXTENSION
Time: 255.910 ms
postgres:5432# create extension json_enhancements; 
CREATE EXTENSION
Time: 32.668 ms
postgres:5432# \df *json*
                                                               List of functions
   Schema   |           Name            | Result data type  |                           Argument data types                           |  Type  
------------+---------------------------+-------------------+-------------------------------------------------------------------------+--------
 pg_catalog | array_to_json             | json              | anyarray                                                                | normal
 pg_catalog | array_to_json             | json              | anyarray, boolean                                                       | normal
 pg_catalog | json_in                   | json              | cstring                                                                 | normal
 pg_catalog | json_out                  | cstring           | json                                                                    | normal
 pg_catalog | json_recv                 | json              | internal                                                                | normal
 pg_catalog | json_send                 | bytea             | json                                                                    | normal
 pg_catalog | row_to_json               | json              | record                                                                  | normal
 pg_catalog | row_to_json               | json              | record, boolean                                                         | normal
 public     | hstore_to_json            | json              | hstore                                                                  | normal
 public     | hstore_to_json_loose      | json              | hstore                                                                  | normal
 public     | json_agg                  | json              | anyelement                                                              | agg
 public     | json_agg_finalfn          | json              | internal                                                                | normal
 public     | json_agg_transfn          | internal          | internal, anyelement                                                    | normal
 public     | json_array_element        | json              | the_json json, element integer                                          | normal
 public     | json_array_element_text   | text              | the_json json, element integer                                          | normal
 public     | json_array_elements       | TABLE(value json) | the_json json                                                           | normal
 public     | json_array_length         | integer           | the_json json                                                           | normal
 public     | json_each                 | SETOF record      | the_json json, OUT key text, OUT value json                             | normal
 public     | json_each_text            | SETOF record      | the_json json, OUT key text, OUT value text                             | normal
 public     | json_extract_path         | json              | the_json json, VARIADIC path_elements text[]                            | normal
 public     | json_extract_path_op      | json              | the_json json, path_elements text[]                                     | normal
 public     | json_extract_path_text    | text              | the_json json, VARIADIC path_elements text[]                            | normal
 public     | json_extract_path_text_op | text              | the_json json, path_elements text[]                                     | normal
 public     | json_object_field         | json              | json, text                                                              | normal
 public     | json_object_field_text    | text              | json, text                                                              | normal
 public     | json_object_keys          | SETOF text        | the_json json                                                           | normal
 public     | json_populate_record      | anyelement        | base anyelement, from_json json, use_json_as_text boolean DEFAULT false | normal
 public     | json_populate_recordset   | SETOF anyelement  | base anyelement, from_json json, use_json_as_text boolean DEFAULT false | normal
 public     | to_json                   | json              | anyelement                                                              | normal
(29 rows)
postgres:5432# select json_object_field_text(bird_info, 'name') from birds; 
 json_object_field_text 
------------------------
 scrubjay
(1 row)

Time: 0.330 ms
postgres:5432# select json_object_field(bird_info, 'name') from birds; 
 json_object_field 
-------------------
 "scrubjay"
(1 row)

Time: 0.260 ms
postgres:5432# select json_object_keys(bird_info) from birds; 
 json_object_keys 
------------------
 name
 where
 when
(3 rows)

Time: 0.387 ms