0-aggregate_json.sql

-- Queries a table like this:
--
-- id	    settings	                  updated_at
-- 10013  {"reports.url_expiry":400}  2018-04-30 00:00:00
-- 10014  {"setting2":"foo"}          2018-05-01 00:00:00
-- 10015  {"reports.url_expiry":200}  2018-05-02 00:00:00
--
-- to roll up the settings column into:
--
-- { "reports.url_expiry" : 200, "setting2" : "foo" }
--

select json_object_agg(key, value)
from (
	select distinct on (json_data.key) json_data.key, json_data.value
	from app_settings, json_each(app_settings.settings) as json_data
	order by json_data.key, updated_at desc
) as key_pairs;

1-aggregate_json.rb

Record
  .select("json_object_agg(key, value) as settings")
  .from(
    Record
      .select("DISTINCT ON (setting.key) setting.key, setting.value")
      .from("app_settings, json_each(app_settings.settings) AS setting")
      .order("setting.key, updated_at DESC")
  )
  .load.first.settings