How to export SQLite to JSON?

Question

Is there any way to get a SQLite view on a JSON file?

Thanks

Answer 1

On recent versions of SQLite, this is built in. The following:

sqlite3> .mode json
sqlite3> .once out.json
sqlite3> SELECT * from foo;

writes the table foo to out.json.

Or, directly from the command line:

sqlite3 db.sqlite3 '.mode json' '.once out.json' 'select * from foo'

.once, which writes the output of the next SQL command to the indicated file, has been in SQLite since 3.8.5 in 2014.

The .mode json is newer though, added in 3.33.0 in 2020-08. It comes with ubuntu 20.10 but older operating systems are unlikely to have that feature in their built-in SQLite version.