MikeTeo.net

A Software Technologist's Blog (Wanna Email Me?)

JSON Functions for PostgreSQL

November 5, 2011 By miketeo

The JSON Functions for PostgreSQL offers a set of user-defined functions to allow JSON structures processing in PostgreSQL databases. Currently, the available functions only support encoding of data rows into JSON structures. Subsequent releases may allow decoding of JSON strings into database composite types or tables.

The JSON Functions for PostgreSQL has been released to the public as public domain software on Nov 2011. There is no warranty or money-back guarantee, so use at your own risk :mrgreen:

Limitations

  • There is limited support for composite types.
  • Please ensure that the values of your textual columns are encoded in UTF-8. The JSON functions may not work or encode values correctly with other non-UTF8 encoded text.

If you encounter issues, drop me an email or comments. You may need to furnish further details such as your table definitions and SQL statement.

Installation

Ensure that pg-config is available in your PATH. You can test this by running pg-config and see if your shell gives you an error. If there is an error, then you may want to check if you have installed the development files for PostgreSQL (for rpms, you need postgresql-devel RPMs).

  1. Download pgjson-20111103.zip and extract the contents
  2. Run the following commands:
    cd pgjson
    make
    make install
    This will install pgjson.so to the PostgreSQL lib folder, and pgjson.sql and uninstall_pgjson.sql to your PostgreSQL contrib folder.
  3. Restart PostgreSQL service
  4. For those databases that wish to utilize the JSON functions, run all the SQL commands in the pgjson.sql file.
    You can also run the following command in psql (modify the contrib path accordingly to suit your PostgreSQL environment).
    \i /usr/share/postgresql/contrib/pgjson.sql

Available JSON Functions

row_to_json_object() and row_to_json_array()

Encodes the column values of each row into a JSON string. The function row_to_json_object() will return a JSON object (also known as dictionary or hashtable) consisting of each column header name mapped to its corresponding column value. On the other hand, the function row_to_json_array() will return a JSON array containing the column values as its element. Both functions always take in a row parameter.

Example:

psql=# SELECT row_to_json_object(pg_aggregate) FROM pg_aggregate;
                            row_to_json_object                                                                                 
---------------------------------------------------------------------------
 { "aggfnoid": "pg_catalog.avg", "aggtransfn": "int8_avg_accum",
   "aggfinalfn": "numeric_avg", "aggsortop": "0", "aggtranstype": "1231",
   "agginitval": "{0,0}" }
 { "aggfnoid": "pg_catalog.avg", "aggtransfn": "int4_avg_accum",
   "aggfinalfn": "int8_avg", "aggsortop": "0", "aggtranstype": "1016",
   "agginitval": "{0,0}" }
 { "aggfnoid": "pg_catalog.avg", "aggtransfn": "int2_avg_accum",
   "aggfinalfn": "int8_avg", "aggsortop": "0", "aggtranstype": "1016",
   "agginitval": "{0,0}" }
 { "aggfnoid": "pg_catalog.avg", "aggtransfn": "numeric_avg_accum",
   "aggfinalfn": "numeric_avg", "aggsortop": "0", "aggtranstype": "1231",
   "agginitval": "{0,0}" }
.....
psql=# SELECT row_to_json_array(pg_aggregate) FROM pg_aggregate;
                             row_to_json_array                                        
---------------------------------------------------------------------------------
 [ "pg_catalog.avg", "int8_avg_accum", "numeric_avg", "0", "1231", "{0,0}" ]
 [ "pg_catalog.avg", "int4_avg_accum", "int8_avg", "0", "1016", "{0,0}" ]
 [ "pg_catalog.avg", "int2_avg_accum", "int8_avg", "0", "1016", "{0,0}" ]
 [ "pg_catalog.avg", "numeric_avg_accum", "numeric_avg", "0", "1231", "{0,0}" ]
.....

To select a subset of columns for JSON encoding, simply wrap the query as a sub-select statement before calling the JSON functions.

psql=# select row_to_json_object(stat) from (select starelid,stanumbers1,stavalues1
       from pg_statistic order by starelid limit 5) stat;
                             row_to_json_object                                                        
--------------------------------------------------------------------------------------------
 { "starelid": "1136", "stanumbers1": null,
   "stavalues1": "{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}" }
 { "starelid": "1136", "stanumbers1": [ 0.625000, 0.375000 ],
   "stavalues1": "{f,t}" }
 { "starelid": "1136", "stanumbers1": [ 0.625000, 0.375000 ],
   "stavalues1": "{f,t}" }
 { "starelid": "1136", "stanumbers1": [ 0.250000, 0.250000 ],
   "stavalues1": "{plperl_call_handler,plpython_call_handler}" }
 { "starelid": "1136", "stanumbers1": [ 0.250000, 0.250000 ],
   "stavalues1": "{plperl_inline_handler,plpython_inline_handler}" }
(5 rows)
psql=# select row_to_json_array(stat) from
      (select starelid,stanumbers1,stavalues1 from pg_statistic order by starelid limit 5) stat;
                                     row_to_json_array                                    
-------------------------------------------------------------------------------------------
 [ "1136", null, "{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}" ]
 [ "1136", [ 0.625000, 0.375000 ], "{f,t}" ]
 [ "1136", [ 0.625000, 0.375000 ], "{f,t}" ]
 [ "1136", [ 0.250000, 0.250000 ], "{plperl_call_handler,plpython_call_handler}" ]
 [ "1136", [ 0.250000, 0.250000 ], "{plperl_inline_handler,plpython_inline_handler}" ]
(5 rows)

json_object_agg() and json_array_agg()

The previous row functions encode each row into JSON strings; the json_object_agg() and json_array_agg() aggregate functions encode the results of a query into a single JSON string, in a similar way that a count() function will return a single numerical value for the query. Both functions always take in a row parameter.

Example:

psql=# select json_array_agg(stat) from
       (select stanumbers1,stavalues1,starelid from pg_statistic order by starelid limit 5) stat;
                                       json_array_agg
---------------------------------------------------------------------------------------------------------------------
 [[ null, "{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}", "1136" ],
  [ [ 0.625000, 0.375000 ], "{f,t}", "1136" ],
  [ [ 0.625000, 0.375000 ], "{f,t}", "1136" ],
  [ [ 0.250000, 0.250000 ], "{plperl_call_handler,plpython_call_handler}", "1136" ],
  [ [ 0.250000, 0.250000 ], "{plperl_inline_handler,plpython_inline_handler}", "1136" ]]
 (1 row)
psql=# select json_object_agg(stat) from
       (select stanumbers1,stavalues1,starelid from pg_statistic order by starelid limit 5) stat;
                                     json_object_agg
--------------------------------------------------------------------------------------
 [{ "stanumbers1": null, "stavalues1": "{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}",
    "starelid": "1136" },
  { "stanumbers1": [ 0.625000, 0.375000 ], "stavalues1": "{f,t}",
    "starelid": "1136" },
  { "stanumbers1": [ 0.625000, 0.375000 ], "stavalues1": "{f,t}",
    "starelid": "1136" },
  { "stanumbers1": [ 0.250000, 0.250000 ], "stavalues1": "{plperl_call_handler,plpython_call_handler}",
    "starelid": "1136" },
  { "stanumbers1": [ 0.250000, 0.250000 ], "stavalues1": "{plperl_inline_handler,plpython_inline_handler}",
    "starelid": "1136" }]
 (1 row)

 

Add A Comment