sqlite Json grouping json_each

Question

I want to generate json grouping users by year.

I have got a "user" table in sqlite with the following columns:

• date
• name
• surname
• value

I would like to create a JSON with the following structure:

{
"App":[
 {
 "data": [
 {
 "name": "UserName1",
 "Surname": "Surname1",
 "v": 1231
 },
 {
 "name": "UserName2",
 "Surname": "Surname2",
 "v": 131
 },
 {
 "name": "UserName3",
 "Surname": "Surname3",
 "v": 121
 }
 ],
 "year": 2018
 },
 {
 "data": [
 {
 "name": "UserName4",
 "Surname": "Surname4",
 "v": 1231
 },
 {
 "name": "UserName5",
 "Surname": "Surname5",
 "v": 131
 }
 ],
 "year": 2019
 }

How to group all the data based on the year?

Answer 1

The JSON1 extension makes it pretty easy.

Given the following:

CREATE TABLE user(name text, surname text, date text, value integer);
INSERT INTO user VALUES('UserName1','Surname1','2018-04-01',1231);
INSERT INTO user VALUES('UserName2','Surname2','2018-03-01',131);
INSERT INTO user VALUES('UserName3','Surname3','2018-02-01',121);
INSERT INTO user VALUES('UserName4','Surname4','2019-02-01',1231);
INSERT INTO user VALUES('UserName5','Surname5','2019-03-01',131);
CREATE INDEX user_year_idx ON user(strftime('%Y', date));

this query:

WITH json_by_year AS
 (SELECT json_object('year', cast(strftime('%Y', date) AS INTEGER)
 , 'data', json_group_array(json_object('name', name
 , 'surname', surname
 , 'value', value))) AS year_obj
 FROM user
 GROUP BY strftime('%Y', date)
 ORDER BY strftime('%Y', date))
SELECT json_object('App', json_group_array(year_obj))
FROM json_by_year;

Produces this (After being run through a JSON pretty-printer):

{
 "App": [
 {
 "year": 2018,
 "data": [
 {
 "name": "UserName1",
 "surname": "Surname1",
 "value": 1231
 },
 {
 "name": "UserName2",
 "surname": "Surname2",
 "value": 131
 },
 {
 "name": "UserName3",
 "surname": "Surname3",
 "value": 121
 }
 ]
 },
 {
 "year": 2019,
 "data": [
 {
 "name": "UserName4",
 "surname": "Surname4",
 "value": 1231
 },
 {
 "name": "UserName5",
 "surname": "Surname5",
 "value": 131
 }
 ]
 }
 ]
}

Basically, it uses a CTE to produce rows of users grouped by year, and then the outer query wraps those in your final data format. The index on the expression speeds up the grouping; handy if you're going to be doing this a lot, but not really needed if it's a once in a while thing.

Answer 2

The closest I have come to

SELECT 
 '{ "year": ' || year || ',' || '"data": [ ' || group_concat(json) || ']}'
from (select substr(date,-4) as year,
 '{"name": "' || name || 
 '" ,"surname": "' || surname ||
 '" ,"value": "' || value ||
 '" }' as json
from user) 
group by year
;

Which returns the following structure:

{
"year": 2018,
"data": [
 {
 "name": "UserName1",
 "Surname": "Surname1",
 "v": 1231
 },
 {
 "name": "UserName2",
 "Surname": "Surname2",
 "v": 131
 },
 {
 "name": "UserName3",
 "Surname": "Surname3",
 "v": 121
 }
]
},
{
"year": 2019,
"data": [
 {
 "name": "UserName4",
 "Surname": "Surname4",
 "v": 1231
 },
 {
 "name": "UserName5",
 "Surname": "Surname5",
 "v": 131
 }
]
}