darkblue blog

-- Simple JSON/JSONb compare, by Oleg
-- json: text storage, as is
-- jsonb: whitespace dissolved, no duplicate keys (last in wins), keys sorted
SELECT 
  '{"c":0,   "a":2, "a":1}'::json,
  '{"c":0,   "a":2, "a":1}'::jsonb;

          json           |      jsonb       
-------------------------+------------------
 {"c":0,   "a":2, "a":1} | {"a": 1, "c": 0}
(1 row)


-- emit JSON text from Census corpus
--
SELECT json_agg(row_to_json(p)) from 
(  
  select gid,fullname,'feat' as ftype from tiger_data.ca_featnames 
  where fullname ~ '^Az' ) as p;

          json_agg  (formatting added) 
-------------------------------------------
[ 
  {"gid":5048,"fullname":"Aztec Way","ftype":"feat"},
  {"gid":9682,"fullname":"Azalea Ct","ftype":"feat"},
    ...
  {"gid":4504601,"fullname":"Azure Pl","ftype":"feat"}
]

##--  return a dict with metadata fields, and an array of dict
select row_to_json(a.*) from 
(select 
  'census_acs_2013' as origin,
  'ca' as state,
  'ca_featnames' as table,
  (
    SELECT json_agg(row_to_json(p)) from (  
      select gid,fullname,'feat' as ftype from tiger_data.ca_featnames 
      where fullname ~ '^Az' ) as p
  ) as rows
) a;

          row_to_json  (formatting added) 
---------------------------------------------------------
{   "origin":"census_acs_2013",
    "state":"ca",
    "table":"ca_featnames",
    "rows": [
      {"gid":5048,"fullname":"Aztec Way","ftype":"feat"},
      ...
      {"gid":4519032,"fullname":"Azalea Way","ftype":"feat"}
  ]
}