Format output as JSON object instead of array

Question

Table:

sqlite> select * from t;
id  name  date
--  ----  ----
1   ...   ...
2   ...   ...
3   ...   ...

Schema:

sqlite> .schema t
CREATE TABLE t (id integer primary key, name text, date text);

The output can be formatted in JSON as an array of objects:

sqlite> .mode json
sqlite> select * from t;
[
    {"id": 1, "name": "...", "date": "..."},
    {"id": 2, "name": "...", "date": "..."},
    {"id": 3, "name": "...", "date": "..."}
]

However, I would like an object where the keys are integers and the values are objects:

{
    1: {"name": "...", "date": "..."},
    2: {"name": "...", "date": "..."},
    3: {"name": "...", "date": "..."}
}

Is this possible with SQLite?

Answer 1

Use function json_object() to get a json object for the name and date of each row and json_group_object() to aggregate all rows:

SELECT json_group_object(
         id, 
         json_object('name', name, 'date', date)
       ) AS result
FROM t;

See the demo.