pg-promise与pgAdmin执行同一SQL查询返回结果不一致的问题咨询

我在使用PostgreSQL编写聚合查询时遇到了一个奇怪的问题：同一查询在pgAdmin和pg-promise中返回的结果不一致——pgAdmin里似乎显示了同日期下的多条记录，但pg-promise里只保留了一条，而且JSON结构也有差异。

我的SQL查询 #

SELECT report.date_concerned, json_object_agg('title', json_build_object('title', report.title, 'duration', report.duration, 'fk_category_id', report.fk_category_id, 'fk_client_id', report.fk_client_id, 'category_name', category.name, 'client_name', client.name)) AS details 
FROM report, category, client 
WHERE report.fk_user_id=2 AND report.fk_category_id = category.id AND report.fk_client_id = client.id 
GROUP BY report.date_concerned 
ORDER BY report.date_concerned

pgAdmin返回的结果（显示异常） #

[
  {
    "date_concerned": "2021-08-01T22:00:00.000Z",
    "details": {
      "title": "élément 2 du 2 août",
      "duration": "02:15:00",
      "fk_category_id": 1,
      "fk_client_id": 2,
      "category_name": "Trotinettes",
      "client_name": "James Bond"
    }
  },
  {
    "title": "élément 1 du 2 aout",
    "duration": null,
    "fk_category_id": 1,
    "fk_client_id": 2,
    "category_name": "Trotinettes",
    "client_name": "James Bond"
  },
  {
    "date_concerned": "2021-08-11T22:00:00.000Z",
    "details": {
      "title": "premier mot, deuxième mot, troisième mot, quatrième mot",
      "duration": "03:15:00",
      "fk_category_id": 1,
      "fk_client_id": 2,
      "category_name": "Trotinettes",
      "client_name": "James Bond"
    }
  }
]

pg-promise返回的结果（丢失记录） #

[
  {
    "date_concerned": "2021-08-01T22:00:00.000Z",
    "details": {
      "title": {
        "title": "élément 2 du 2 août",
        "duration": "02:15:00",
        "fk_category_id": 1,
        "fk_client_id": 2,
        "category_name": "Trotinettes",
        "client_name": "James Bond"
      }
    }
  },
  {
    "date_concerned": "2021-08-11T22:00:00.000Z",
    "details": {
      "title": {
        "title": "premier mot, deuxième mot, troisième mot, quatrième mot",
        "duration": "03:15:00",
        "fk_category_id": 1,
        "fk_client_id": 2,
        "category_name": "Trotinettes",
        "client_name": "James Bond"
      }
    }
  }
]

我的pg-promise调用代码 #

调用方式：

sendQuery("SELECT report.date_concerned, json_object_agg('title', json_build_object('title', report.title, 'duration', report.duration, 'fk_category_id', report.fk_category_id, 'fk_client_id', report.fk_client_id, 'category_name', category.name, 'client_name', client.name)) AS details FROM report, category, client WHERE report.fk_user_id=$1 AND report.fk_category_id = category.id AND report.fk_client_id = client.id GROUP BY report.date_concerned ORDER BY report.date_concerned", urlValue, callback);

sendQuery实现：

function sendQuery(req, value, next) {
  db.any(req, value)
    .then(function (data) {
      next({
        status: 'success',
        data: data,
        message: "it works",
      });
    })
    .catch(function (err) {
      console.log('Error : ');
      console.log(err);
      next({
        status: 'error',
        data: null,
        message: 'an error occured'
      });
    });
}

问题分析与解决方案 #

核心问题：json_object_agg的键重复导致数据覆盖

你使用json_object_agg('title', ...)时，给所有聚合的对象指定了同一个固定键title。而JSON对象的键是唯一的，当同一date_concerned分组下有多条记录时，后面的记录会直接覆盖前面的，最终每个分组只保留最后一条记录。

pgAdmin的显示其实是一个误导——它错误地将聚合后的JSON对象内容展开成了单独的行，看起来像是返回了三条记录，但实际上PostgreSQL执行查询后只返回了两行（对应两个不同的date_concerned），每个行的details里只有被覆盖后的最后一条记录。

修复方案：改用数组聚合或唯一键

根据你的需求，有两种常见的修复方式：

1. 改用json_agg聚合为数组（推荐）
   如果希望同一日期下的所有记录以数组形式返回，将json_object_agg替换为json_agg，这样details会是一个包含所有对应记录的数组，不会丢失数据：

   SELECT 
     report.date_concerned, 
     json_agg(json_build_object(
       'title', report.title, 
       'duration', report.duration, 
       'fk_category_id', report.fk_category_id, 
       'fk_client_id', report.fk_client_id, 
       'category_name', category.name, 
       'client_name', client.name
     )) AS details 
   FROM report
   JOIN category ON report.fk_category_id = category.id 
   JOIN client ON report.fk_client_id = client.id 
   WHERE report.fk_user_id = $1 
   GROUP BY report.date_concerned 
   ORDER BY report.date_concerned
   

   修复后，返回的details会是数组形式，比如：

   {
     "date_concerned": "2021-08-01T22:00:00.000Z",
     "details": [
       {
         "title": "élément 1 du 2 aout",
         "duration": null,
         "fk_category_id": 1,
         "fk_client_id": 2,
         "category_name": "Trotinettes",
         "client_name": "James Bond"
       },
       {
         "title": "élément 2 du 2 août",
         "duration": "02:15:00",
         "fk_category_id": 1,
         "fk_client_id": 2,
         "category_name": "Trotinettes",
         "client_name": "James Bond"
       }
     ]
   }
   

2. 使用唯一键进行对象聚合
   如果确实需要以对象形式存储，确保每个键唯一（比如用report.id作为键），这样不会出现覆盖：

   SELECT 
     report.date_concerned, 
     json_object_agg(report.id, json_build_object(
       'title', report.title, 
       'duration', report.duration, 
       'fk_category_id', report.fk_category_id, 
       'fk_client_id', report.fk_client_id, 
       'category_name', category.name, 
       'client_name', client.name
     )) AS details 
   FROM report
   JOIN category ON report.fk_category_id = category.id 
   JOIN client ON report.fk_client_id = client.id 
   WHERE report.fk_user_id = $1 
   GROUP BY report.date_concerned 
   ORDER BY report.date_concerned
   

额外建议

• 使用显式JOIN语法代替逗号分隔表名，让查询逻辑更清晰，避免隐式连接带来的潜在问题。
• pg-promise的调用本身没有问题，db.any适合返回多行结果的查询，问题根源在SQL的聚合逻辑上。

内容的提问来源于stack exchange，提问作者Neta