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
Indexes:
"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.
day
anddata
are both always filled, you should make themnot null
. You'll probably want to use the newjsonb
datatype instead ofjson
too.ORDER BY day
(as modifier to the aggregate function or in a subquery), since order of keys may be relevant in the JSON object.