Skip to content

Instantly share code, notes, and snippets.

@bfcoder
Created September 29, 2015 20:09
Show Gist options
  • Save bfcoder/7843c0e1bbdb3923dfed to your computer and use it in GitHub Desktop.
Save bfcoder/7843c0e1bbdb3923dfed to your computer and use it in GitHub Desktop.
PostgreSQL (9.2+) : How to return an empty json array from an empty resultset.
/* will return null::json on empty resultset */
SELECT array_to_json(array_agg(row_to_json(t))) FROM t;
/*
will return '[]' on empty resultset,
null::json seems to be managed the same way than sql NULL by COALESCE()
*/
SELECT COALESCE(array_to_json(array_agg(row_to_json(t))), '[]') FROM t;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment