1

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?

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.