{"id":24389,"date":"2022-03-16T08:34:49","date_gmt":"2022-03-15T22:34:49","guid":{"rendered":"https:\/\/database.guide\/?p=24389"},"modified":"2022-03-22T08:43:27","modified_gmt":"2022-03-21T22:43:27","slug":"format-sqlite-results-as-json","status":"publish","type":"post","link":"https:\/\/database.guide\/format-sqlite-results-as-json\/","title":{"rendered":"Format SQLite Results as JSON"},"content":{"rendered":"\n

It’s possible to output query results as a JSON document when using the SQLite command line interface.<\/p>\n\n\n\n

We can do this with the json<\/code> output mode.<\/p>\n\n\n\n

We can also use SQLite functions like json_object()<\/a><\/code> and\/or json_array()<\/a><\/code> to return query results as a JSON document.<\/p>\n\n\n\n\n\n\n\n

JSON Output Mode<\/h2>\n\n\n\n

We can change the output mode like this:<\/p>\n\n\n\n

.mode json<\/code><\/pre>\n\n\n\n
That’s all. <\/p>\n\n\n\n
Now when we run a query, the results are output as a JSON document:<\/p>\n\n\n\n
SELECT * FROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
[{\"PetId\":1,\"PetName\":\"Homer\",\"TypeId\":3},\n{\"PetId\":2,\"PetName\":\"Yelp\",\"TypeId\":1},\n{\"PetId\":3,\"PetName\":\"Fluff\",\"TypeId\":2},\n{\"PetId\":4,\"PetName\":\"Brush\",\"TypeId\":4}]<\/pre>\n\n\n\n
Just to be clear, here it is in table mode:<\/p>\n\n\n\n
.mode table<\/code><\/pre>\n\n\n\n
Run the query:<\/p>\n\n\n\n
SELECT * FROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------+---------+--------+\n| PetId | PetName | TypeId |\n+-------+---------+--------+\n| 1     | Homer   | 3      |\n| 2     | Yelp    | 1      |\n| 3     | Fluff   | 2      |\n| 4     | Brush   | 4      |\n+-------+---------+--------+<\/pre>\n\n\n\n
Note that the json output mode was introduced in SQLite 3.33.0, which was released on 14 August 2020.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Alternatively, you could use a JSON function to do the job (assuming JSON functions are enabled).<\/p>\n\n\n\n
JSON Functions<\/h2>\n\n\n\n
Another way to do it is to incorporate one or more JSON functions into our query so that it returns a JSON document.<\/p>\n\n\n\n
First, let’s set our output mode to list<\/code>:<\/p>\n\n\n\n
.mode list<\/code><\/pre>\n\n\n\n
Now let’s run a query that uses json_group_array()<\/a><\/code> and json_object()<\/code> to return our query results in a JSON document:<\/p>\n\n\n\n
SELECT json_group_array( \n        json_object(\n        'PetId', PetId, \n        'PetName', PetName,\n        'TypeId', TypeId \n        )\n    )\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
[{\"PetId\":1,\"PetName\":\"Homer\",\"TypeId\":3},{\"PetId\":2,\"PetName\":\"Yelp\",\"TypeId\":1},{\"PetId\":3,\"PetName\":\"Fluff\",\"TypeId\":2},{\"PetId\":4,\"PetName\":\"Brush\",\"TypeId\":4}]<\/pre>\n\n\n\n
Here, we output each row as a JSON object, and the whole thing is wrapped in a JSON array.<\/p>\n\n\n\n
We can omit the json_group_array()<\/code> function to return each object on its own:<\/p>\n\n\n\n
SELECT json_object( \n    'PetId', PetId, \n    'PetName', PetName,\n    'TypeId', TypeId \n    )\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"PetId\":1,\"PetName\":\"Homer\",\"TypeId\":3}\n{\"PetId\":2,\"PetName\":\"Yelp\",\"TypeId\":1}\n{\"PetId\":3,\"PetName\":\"Fluff\",\"TypeId\":2}\n{\"PetId\":4,\"PetName\":\"Brush\",\"TypeId\":4}<\/pre>\n\n\n\n
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). <\/p>\n\n\n\n
Prior to SQLite version 3.38.0 (released on 22 February 2022), we needed to compile SQLite with the SQLITE_ENABLE_JSON1<\/code> 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[100],"tags":[10],"class_list":["post-24389","post","type-post","status-publish","format-standard","hentry","category-sqlite","tag-how-to"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/24389","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=24389"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/24389\/revisions"}],"predecessor-version":[{"id":24534,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/24389\/revisions\/24534"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=24389"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=24389"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=24389"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}