PostgreSQL - Queries returned as JSON

table_to_json.pg.sql

; Outputs 1 json row per line.
;   Eg.:
;     {"foo": "bar"}
;     {"baz": "qux"}
copy (
  select row_to_json(t)
  from (select * from users) t
) to '/Users/ryan/Desktop/users.json';

------ Other modifications ------
-- src: https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql

-- Select specific rows:
select row_to_json(t)
from (
  select id, text from words
) t;

-- More complex:
select row_to_json(t)
from (
  select text, pronunciation,
    (
      select array_to_json(array_agg(row_to_json(d)))
      from (
        select part_of_speech, body
        from definitions
        where word_id=words.id
        order by position asc
      ) d
    ) as definitions
  from words
  where text = 'autumn'
) t;

table_to_json_array.sql

; Outputs array of json objects
;   Eg.:
;     [{"foo": "bar"},{baz": "qux"}]
COPY (SELECT array_to_json(array_agg(row_to_json(t)))
FROM (SELECT * FROM users) t) TO '/Users/ryan/Desktop/users.json';