sqlite Json grouping json_each
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.