Postgres JSON

November 10, 2016

Postgres 9.2+ supports JSON. Sweet.

Turn off paging, alignment, and show only rows. Output a JSON array of dictionaries with entries pid and datesysmetadatamodified for index tasks with a FAILED status.

\pset pager off \a \t

select array_to_json(array_agg(row_to_json(t)), true)
from (
  select pid, datesysmetamodified from index_task
  where status = 'FAILED' ) t;

With output that can be further processed using something like jq:

[{"pid":"P1_2016311212210511","datesysmetamodified":1478496130538},
 {"pid":"cNodeTier1TestCreate20163140039989","datesysmetamodified":1478678439991},
 {"pid":"testGetPackage_2016303234856884","datesysmetamodified":1477810139916},
 {"pid":"testGetPackage_NotAuthorized_2016304224625996","datesysmetamodified":1477896388372},
 {"pid":"cNodeTier1TestCreate201631209179","datesysmetamodified":1478506141082}]

Dave's Notes

Absence of evidence is not evidence of absence

Postgres JSON