SQLite Results as JSON using the SQLite JSON1 extension

sqlite_to_json.sql

-- When SQLite is compiled with the JSON1 extensions it provides builtin tools
-- for manipulating JSON data stored in the database. 
-- This is a gist showing SQLite return query data as a JSON object.
-- https://www.sqlite.org/json1.html

-- An example table with some data
CREATE TABLE users (
  id INTEGER PRIMARY KEY NOT NULL, 
  full_name TEXT NOT NULL, 
  email TEXT NOT NULL, 
  created DATE NOT NULL
);
INSERT INTO users 
VALUES 
(1, "Bob McFett", "[email protected]", "32-01-01"),
(2, "Angus O'Vader", "[email protected]", "02-03-04"),
(3, "Imperator Colin", "[email protected]", "01-01-01");

-- Get query data as a JSON object using the 
-- json_group_object() [1] and json_object() [2] functions.
SELECT 
json_group_object(
	email, 
	json_object('full_name', full_name, 'created', created)
) AS json_result
FROM (SELECT * FROM users WHERE created > "02-01-01");

-- 1 rows returned
-- json_result
-- {"[email protected]":{"full_name":"Bob McFett","created":"32-01-01"},"[email protected]":{"full_name":"Angus O'Vader","created":"02-03-04"}}

-- Get query data as a JSON object using the 
-- json_group_array() function to maintain order.
SELECT 
json_group_array(
	json_object('full_name', full_name, 'created', created)
) AS json_result
FROM (SELECT * FROM users ORDER BY created);

-- 1 rows returned
-- json_result
-- [{"full_name":"Colin","created":"01-01-01"},{"full_name":"Angus O'Vader","created":"02-03-04"},{"full_name":"Bob McFett","created":"32-01-01"}]
  
-- Links
-- [1] https://www.sqlite.org/json1.html#jgroupobject
-- [2] https://www.sqlite.org/json1.html#jobj