title: PostgreSQL 9.5: Combine json_agg results into single json object
body: <p>I am struggling with the following SQL query:</p>
  
  <p>There is a table <strong>data_tracks</strong> with coordinates describing a trip. Each trip is uniquely identified by a <strong>trip_log_id</strong>. After reaching the trip's destination, the user needs to participate to a survey. The answers of the survey are stored in a table <strong>crowd_sourcing_answers</strong>. Each answer belongs to a question, located in table <strong>crowd_sourcing_questions</strong>.</p>
  
  <p>I have written two SQL queries - one to get all points of a trip as JSON, another one to get all question-answers pairs:</p>
  
  <p>Query for fetching all question-answer pairs of a trip:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>and its output:</p>
  
  <pre><code>[
    {
      "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"
        }
      ]
    }
  ]     
  </code></pre>
  
  <p>Query for fetching all points belonging to a trip:</p>
  
  <pre><code>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; 
  </code></pre>
  
  <p>and its output:</p>
  
  <pre><code>[
    [
      {
        "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
      }
    ]
  ]
  </code></pre>
  
  <h1>Goal</h1>
  
  <p>Now I need to merge these two results such that there is one JSON object for each trip ID, containing the question-answer pairs (key: "crowdsourcing"; array) and the points of the trip (key: "trip"; array). Following an example:</p>
  
  <pre><code>[
    {  // 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
        }
      ]
    }
  ]
  </code></pre>
  
  <h1>Approach</h1>
  
  <p>I created a query, please see <a href="https://www.db-fiddle.com/f/dZcusjqZG1j9uY9jYbacDW/0" rel="nofollow noreferrer">DB Fiddle</a>. However, it returns duplicate records within the two arrays (question-answers pairs, points of trip). I was thinking it has to do something with the <code>JOIN</code> but all my trials failed.</p>