It’s possible to output query results as a JSON document when using the SQLite command line interface.

We can do this with the json output mode.

We can also use SQLite functions like json_object() and/or json_array() to return query results as a JSON document.

JSON Output Mode

We can change the output mode like this:

.mode json

That’s all.

Now when we run a query, the results are output as a JSON document:

SELECT * FROM Pets;

Result:

[{"PetId":1,"PetName":"Homer","TypeId":3},
{"PetId":2,"PetName":"Yelp","TypeId":1},
{"PetId":3,"PetName":"Fluff","TypeId":2},
{"PetId":4,"PetName":"Brush","TypeId":4}]

Just to be clear, here it is in table mode:

.mode table

Run the query:

SELECT * FROM Pets;

Result:

+-------+---------+--------+
| PetId | PetName | TypeId |
+-------+---------+--------+
| 1     | Homer   | 3      |
| 2     | Yelp    | 1      |
| 3     | Fluff   | 2      |
| 4     | Brush   | 4      |
+-------+---------+--------+

Note that the json output mode was introduced in SQLite 3.33.0, which was released on 14 August 2020.

If you’re using an earlier version of SQLite, the above examples won’t work for you. You’ll need to upgrade to a later version if you want this to work.

Alternatively, you could use a JSON function to do the job (assuming JSON functions are enabled).

JSON Functions

Another way to do it is to incorporate one or more JSON functions into our query so that it returns a JSON document.

First, let’s set our output mode to list:

.mode list

Now let’s run a query that uses json_group_array() and json_object() to return our query results in a JSON document:

SELECT json_group_array( 
        json_object(
        'PetId', PetId, 
        'PetName', PetName,
        'TypeId', TypeId 
        )
    )
FROM Pets;

Result:

[{"PetId":1,"PetName":"Homer","TypeId":3},{"PetId":2,"PetName":"Yelp","TypeId":1},{"PetId":3,"PetName":"Fluff","TypeId":2},{"PetId":4,"PetName":"Brush","TypeId":4}]

Here, we output each row as a JSON object, and the whole thing is wrapped in a JSON array.

We can omit the json_group_array() function to return each object on its own:

SELECT json_object( 
    'PetId', PetId, 
    'PetName', PetName,
    'TypeId', TypeId 
    )
FROM Pets;

Result:

{"PetId":1,"PetName":"Homer","TypeId":3}
{"PetId":2,"PetName":"Yelp","TypeId":1}
{"PetId":3,"PetName":"Fluff","TypeId":2}
{"PetId":4,"PetName":"Brush","TypeId":4}

These examples assume that JSON functions are enabled. If you’re using SQLite 3.38.0 or later, these should be enabled by default (unless they were explicitly disabled when compiling SQLite).

Prior to SQLite version 3.38.0 (released on 22 February 2022), we needed to compile SQLite with the SQLITE_ENABLE_JSON1 option in order to include the JSON functions in the build. However, beginning with SQLite version 3.38.0, the JSON functions are included by default.