sqlite Json 分組 json_each

我想按年份生成 json 分組使用者。

我在 sqlite 中有一個“使用者”表，其中包含以下列：

• 日期
• name
• 姓
• 價值

我想創建一個具有以下結構的 JSON：

{
"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
   }

如何根據年份對所有數據進行分組？

JSON1擴展使它非常容易。

鑑於以下情況：

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));

這個查詢：

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;

產生這個（通過 JSON 漂亮列印機執行後）：

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

基本上，它使用 CTE 生成按年份分組的使用者行，然後外部查詢以最終數據格式包裝這些使用者。表達式上的索引加快了分組；如果你要經常做這件事很方便，但如果它是偶爾的事情，那就不是真的需要了。

我最接近的

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
;

它返回以下結構：

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

引用自：https://dba.stackexchange.com/questions/235134