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
    }
]
}