JSON Export

To export the data from a table to a JSON file, use the COPY statement:

SQL

COPY tbl TO 'output.json';

The result of queries can also be directly exported to a JSON file. Reading the file back shows its contents:

SQL

COPY (SELECT * FROM range(3) tbl(n)) TO 'output.json';

SQL

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

 line
---------
 {"n":0}
 {"n":1}
 {"n":2}

The JSON export writes JSON lines by default, standardized as Newline-delimited JSON. The ARRAY option can be used to write a single JSON array object instead; reading it back yields the same rows:

SQL

COPY (SELECT * FROM range(3) tbl(n)) TO 'output.json' (ARRAY);

SQL

SELECT n FROM read_json('output.json') ORDER BY n;

 n
---
 0
 1
 2

For additional options, see the COPY statement documentation.