I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make things faster. I found that I could format the output into the correct json in postgres very quickly but the query was UGLY as sin. Then postgres 9.4 came out and brought json_object_agg which made the query nice and simple, and even faster than before, and all I had to do was change the data type of the data column to be json (it had been storing json-formatted data anyway).

The current version of the query is:

select json_object_agg(day,data order by day) as jsobj from records

The records table is:

\d records
                         Table "public.records"
 Column |  Type   |                      Modifiers                       
 id     | integer | not null default nextval('records_id_seq'::regclass)
 day    | date    | not null
 data   | json    | not null
    "records_pkey" PRIMARY KEY, btree (id)
    "records_day_key" UNIQUE CONSTRAINT, btree (day)

What I'm wondering is this: is there a way to improve the query further? Either making it more readable or more performant?

I think that because I never look inside the value of data and simply operate on it as a whole (simply inserting it into more json which can be done with blind inlining), that the json datatype is still faster.

The syntax for ordering within an aggregate function is weird, but helpful.

  • 1
    Your query, not so much. The table: if day and data are both always filled, you should make them not null. You'll probably want to use the new jsonb datatype instead of json too. Commented Mar 26, 2015 at 15:38
  • And you probably want to add ORDER BY day (as modifier to the aggregate function or in a subquery), since order of keys may be relevant in the JSON object. Commented Mar 26, 2015 at 23:02


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.