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?
Best Answer
The JSON1 extension makes it pretty easy.
Given the following:
this query:
Produces this (After being run through a JSON pretty-printer):
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.