Writing JSON

The contents of tables or the result of queries can be written directly to a JSON file using the COPY statement. For example:

Query

CREATE TABLE cities AS    FROM (VALUES ('Amsterdam', 1), ('London', 2)) cities(name, id);
COPY cities TO 'cities.json';

This writes cities.json with one JSON object per line. Reading the file back shows its contents:

Query

SELECT unnest(string_split(rtrim(content, chr(10)), chr(10))) AS lineFROM read_text('cities.json');

Result

 line----------------------------- {"name":"Amsterdam","id":1} {"name":"London","id":2}

See the COPY statement for more information.