PostgreSQL 9.5：将json_agg结果组合成单个json对象

Question

PostgreSQL 9.5：将json_agg结果组合成单个json对象

3

我正在努力解决以下的SQL查询问题：

有一个名为data_tracks的表格，其中包含描述一次旅行的坐标。每个旅程都由一个trip_log_id唯一标识。在到达旅程目的地后，用户需要参与一项调查。调查结果存储在名为crowd_sourcing_answers的表格中。每个答案都属于一个问题，这些问题位于crowd_sourcing_questions表格中。

我编写了两个SQL查询 - 一个用于获取旅程的所有点（以JSON格式），另一个用于获取所有问题-答案对：

用于获取旅程的所有问题-答案对的查询：

SELECT json_agg(answer_single_trip)
FROM (SELECT json_agg(
               json_build_object(
                 'tripId', trip_log_id,
                 'question', qt.question,
                 'answeringOption', qt."answeringOptions",
                 'answer', at.answer
                   )
                 ) as crowdsourcing
      FROM crowd_sourcing_questions as qt
             INNER JOIN crowd_sourcing_answers as at ON at.crowd_sourcing_question_id = qt.id
      GROUP BY trip_log_id) answer_single_trip;

及其输出：

[
  {
    "crowdsourcing": [
      {
        "tripId": 92,
        "question": "Gab es auf der Strecke teilweise schlecht befahrbare Streckenabschnitte?",
        "answeringOption": [
          "Ja",
          "Nein"
        ],
        "answer": "2"
      }
    ]
  },
  {
    "crowdsourcing": [
      {
        "tripId": 91,
        "question": "Gab es auf der Strecke teilweise schlecht befahrbare Streckenabschnitte?",
        "answeringOption": [
          "Ja",
          "Nein"
        ],
        "answer": "1"
      }
    ]
  },
  {
    "crowdsourcing": [
      {
        "tripId": 90,
        "question": "Gab es auf der Strecke teilweise schlecht befahrbare Streckenabschnitte?",
        "answeringOption": [
          "Ja",
          "Nein"
        ],
        "answer": "0"
      }
    ]
  }
]

查询获取与旅行相关的所有点：

SELECT json_agg(
         json_build_object(
           'tripId', trip_log_id,
           'trackId', id,
           'recorded_at', created_at,
           'latitude', latitude,
           'longitude', longitude
             )
           ) as trips
FROM data_tracks
GROUP by trip_log_id;

以及其输出：

[
  [
    {
      "trip_log_id": 91,
      "recorded_at": "2018-10-05T14:11:44.847",
      "latitude": 52.5242370846803,
      "longitude": 13.3443558528637
    },
    {
      "trip_log_id": 91,
      "recorded_at": "2018-10-05T14:11:44.911",
      "latitude": 52.5242366166393,
      "longitude": 13.3443558656828
    }
  ],
  [
    {
      "trip_log_id": 90,
      "recorded_at": "2018-10-05T13:28:24.452",
      "latitude": 52.5242370846803,
      "longitude": 13.3443558528637
    },
    {
      "trip_log_id": 90,
      "recorded_at": "2018-10-05T13:28:24.489",
      "latitude": 52.5242366166393,
      "longitude": 13.3443558656828
    }
  ]
]

目标

现在我需要将这两个结果合并，以便每个行程ID都有一个JSON对象，其中包含问题-答案对（键名：“crowdsourcing”; 数组）和该行程的点数（键名：“trip”; 数组）。以下是示例：

[
  {  // DATA FOR TRIP 1
    "crowdsourcing": [
      {
        "question": "Bitte bewerten Sie die Sicherheit der Radroute!",
        "answeringOption": [
          "Sehr sicher",
          "Eher sicher",
          "Neutral",
          "Eher unsicher",
          "Sehr unsicher"
        ],
        "answer": "2"
      },
      {
        "question": "Würden Sie die gefahrene Route anderen Radfahrenden weiterempfehlen?",
        "answeringOption": [
          "Ja",
          "Nein"
        ],
        "answer": "1"
      }
    ],
    "trip": [
      {
        "recorded_at": "2018-10-11T15:16:33",
        "latitude": 52.506785999999998,
        "longitude": 13.398065000000001
      },
      {
        "recorded_at": "2018-10-11T15:16:32.969",
        "latitude": 52.50647,
        "longitude": 13.397856000000001
      },
      {
        "recorded_at": "2018-10-11T15:16:32.936",
        "latitude": 52.506166,
        "longitude": 13.397593000000001
      }
    ]
  },
  { // DATA FOR TRIP 2
    "crowdsourcing": [
      {
        "question": "Bitte bewerten Sie die Sicherheit der Radroute!",
        "answeringOption": [
          "Sehr sicher",
          "Eher sicher",
          "Neutral",
          "Eher unsicher",
          "Sehr unsicher"
        ],
        "answer": "2"
      }
    ],
    "trip": [
      {
        "recorded_at": "2018-10-11T15:33:33.971999",
        "latitude": 52.506785999999998,
        "longitude": 13.398065000000001
      },
      {
        "recorded_at": "2018-10-11T15:33:33.929",
        "latitude": 52.50647,
        "longitude": 13.397856000000001
      }
    ]
  }
]

方法

我创建了一个查询，请参见DB Fiddle。但是，它返回了两个数组中的重复记录（问题-答案对和旅程点）。我认为这与JOIN有关，但是我所有的尝试都失败了。

- Patrick

你的最终结果中丢失了trip_id。这是一个错误吗？ - S-Man

抱歉造成困惑，上面列出的输出（没有任何ID）是最终应该看起来的样子。我只是为了测试目的而添加它，以查看重复记录的ID是否相同。 - Patrick

好的，那就是我的意思。最后你不再需要trip_id了吗？是这样的吗？ - S-Man

是的，没错！非常感谢您的支持。 - Patrick

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- S-Man · Accepted Answer

在您的子查询中，您已经将trip_log_id包含在json部分中。但是，如果您将它们作为单独的列放置，您将有机会针对它连接两个部分：

演示：db<>fiddle

SELECT
    json_agg(
        json_build_object('crowdsourcing', cs.json_agg, 'trip', t.json_agg)
    )
FROM
(
    SELECT 
        trip_log_id,                          -- 1
        json_agg(
            json_build_object('question', question, 'answeringOption', "answeringOptions", 'answer', answer)
        )
    FROM 
        crowd_sourcing_answers csa
    JOIN crowd_sourcing_questions csq ON csa.crowd_sourcing_question_id = csq.id
    GROUP BY trip_log_id
) cs

JOIN                                         -- 2

(
    SELECT
        trip_log_id,                         -- 1
        json_agg(
           json_build_object('recorded_at', created_at, 'latitude', latitude, 'longitude', longitude)
        )
    FROM data_tracks
    GROUP by trip_log_id 
) t

USING (trip_log_id)                          -- 2

获取trip_log_id
用于连接

此外：请注意，在PostgreSQL中，所有列名都应该没有任何大写字母。我建议将addionalOptions重命名为类似additional_options的名称。这样就不需要额外的"字符了。