Export PostgreSQL query results to JSON or JSON Lines
Run a PostgreSQL query through Scriptella and write one JSON object per row. Choose a single JSON array for a document or JSON Lines for consumers that read one record at a time.
Prerequisites
Download Scriptella 1.7.0 and follow the installation instructions for Java 17 or later. The binary distribution includes the JSON driver and its Jackson dependency. Download postgresql-42.7.13.jar from the PostgreSQL driver page and place it in a lib/ directory next to the ETL file.
Use a login that can connect to a PostgreSQL database named demo. To export your own data, give the login SELECT access to the source tables.
Save and run the ETL
Save this as export.etl.xml. Adjust the JDBC URL for your database and replace DEMO_USER and DEMO_PASSWORD with your local demo credentials.
<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
<connection id="source" driver="postgresql"
url="jdbc:postgresql://localhost:5432/demo"
user="DEMO_USER" password="DEMO_PASSWORD"
classpath="lib/postgresql-42.7.13.jar"/>
<connection id="output" driver="json" url="contacts.json"/>
<query connection-id="source">
SELECT id, name, active, note
FROM (VALUES
(1, 'Ada Lovelace', TRUE, 'Writes "notes"'),
(2, 'Sam Rivera', FALSE, NULL)
) AS contacts(id, name, active, note)
ORDER BY id;
<script connection-id="output">
{"id": ?{id}, "name": ?{name}, "active": ?{active}, "note": ?{note}}
</script>
</query>
</etl>
For shared or deployed ETL files, keep real passwords out of version control. Set SCRIPTELLA_DB_USER and SCRIPTELLA_DB_PASSWORD in the process environment and use user="${env.SCRIPTELLA_DB_USER}" and password="${env.SCRIPTELLA_DB_PASSWORD}" in the connection.
From the working directory, run the launcher. On Windows, use scriptella.bat.
scriptella.sh export.etl.xml
Check the JSON output
Open contacts.json next to the ETL file. It contains one array with two objects. Whitespace may differ from this display.
[
{"id":1,"name":"Ada Lovelace","active":true,"note":"Writes \"notes\""},
{"id":2,"name":"Sam Rivera","active":false,"note":null}
]
Use an unquoted ?{column_name} for each dynamic value, including strings. Scriptella quotes and escapes strings and preserves numbers, booleans, and nulls. A quoted "?{name}" is literal text. For JDBC dates or timestamps, convert the value explicitly to a formatted string or epoch number. See the JSON driver guide for examples.
Write JSON Lines
Change the output connection’s URL to contacts.jsonl and run the same command. The extension selects JSON Lines. The template stays the same, and each line is a complete JSON object.
{"id":1,"name":"Ada Lovelace","active":true,"note":"Writes \"notes\""}
{"id":2,"name":"Sam Rivera","active":false,"note":null}
Reruns, empty results, and failures
A run with rows replaces the destination when the first row is written. With zero rows, Scriptella never opens the output file. An existing file keeps its previous data, and a missing file stays absent.
The PostgreSQL query is read-only. File writes are outside the database transaction, so a failure can leave partial output in either format. For scheduled exports, write to a fresh staging filename on each run and publish only after Scriptella succeeds. If no file was created, explicitly publish [] for JSON or an empty file for JSON Lines when that is the agreed behavior.