PostgreSQL: json_agg()

[Val K]

Hi!

This is useful aggregate function for PG-users (tested with PostgreSQL 9.3 ):

def json_agg_sql(flds_lst, as_alias):
        json_fld_sql = """ '"%s":' || to_json(%s) """
        json_row = [json_fld_sql  % (f.name, f.name) for  f in flds_lst]
        json_row_sql = "'{' || " + "|| ',' ||".join(json_row) + " || '}'"
        json_agg_sql =  'json_agg((%s)::json) AS %s' % (json_row_sql, as_alias)
        return json_agg_sql

usage:

rows= db().select(db.table.field, ... , json_agg_sql([db.table.field_1, db.table.field_2], as_alias='your_alias'),  groupby=[db.table.field, ...] )

rows[0].table.field  #  - access to "group by" field 

rows[0].your_alias[0]   #  - access to "json_agg"  record
rows[0].your_alias[0]['field_1']   #  - access to "json_agg" field

 

Note that it's possible to have more than one json_agg  in one select! just  use different aliases