JSON Export

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

Query

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:

Query

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

Query

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

Result

 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:

Query

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

Query

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

Result

 n--- 0 1 2

For additional options, see the COPY statement documentation.