How to "zip" multiple nested JSON arrays without using id key?

Question

I'm trying to merge some nested JSON arrays without looking at the id. Currently I'm getting this when I make a GET request to /surveyresponses:

{
"surveys": [
    {
        "id": 1,
        "name": "survey 1",
        "isGuest": true,
        "house_id": 1
    },
    {
        "id": 2,
        "name": "survey 2",
        "isGuest": false,
        "house_id": 1
    },
    {
        "id": 3,
        "name": "survey 3",
        "isGuest": true,
        "house_id": 2
    }
],
"responses": [
    {
        "question": "what is this anyways?",
        "answer": "test 1"
    },
    {
        "question": "why?",
        "answer": "test 2"
    },
    {
        "question": "testy?",
        "answer": "test 3"
    }
]
}

But I would like to get it where each survey has its own question and answers so something like this:

{
"surveys": [
    {
        "id": 1,
        "name": "survey 1",
        "isGuest": true,
        "house_id": 1
        "question": "what is this anyways?",
        "answer": "test 1"
    }
]
}

Because I'm not going to a specific id I'm not sure how to make the relationship work. This is the current query I have that's producing those results.

export function getSurveyResponse(id: number): QueryBuilder {
return db('surveys')
    .join('questions', 'questions.survey_id', '=', 'surveys.id')
    .join('questionAnswers', 'questionAnswers.question_id', '=', 'questions.id')
    .select('surveys.name', 'questions.question', 'questions.question', 'questionAnswers.answer')
    .where({ survey_id: id, question_id: id })
}

Please start by declaring your version of Postgres and the table definition. `json` or `jsonb`? — Erwin Brandstetter, Feb 21 '19 at 01:59
I took the liberty to fix terminology and syntax to make the question consistent. Did I get it right? — Erwin Brandstetter, Feb 21 '19 at 02:59

score 0 · Answer 1 · answered Feb 21 '19 at 02:48

Assuming jsonb in current Postgres 10 or 11, this query does the job:

SELECT t.data, to_jsonb(s) AS new_data
FROM   t
LEFT   JOIN LATERAL (
   SELECT jsonb_agg(s || r) AS surveys
   FROM  (
      SELECT jsonb_array_elements(t.data->'surveys') s
           , jsonb_array_elements(t.data->'responses') r
      ) sub
   ) s ON true;

db<>fiddle here

I unnest both nested JSON arrays in parallel to get the desired behavior of "zipping" both directly. The number of elements in both nested JSON arrays has to match or you need to do more (else you lose data).

This builds on implementation details of how Postgres deals with multiple set-returning functions in a SELECT list to make it short and fast. See:

What is the expected behaviour for multiple set-returning functions in select clause?

One could be more explicit with a ROWS FROM expression, which works properly since Postgres 9.4:

SELECT t.data
     , to_jsonb(s) AS new_data
FROM   tbl t
LEFT   JOIN LATERAL (
   SELECT jsonb_agg(s || r) AS surveys
   FROM   ROWS FROM (jsonb_array_elements(t.data->'surveys') 
                   , jsonb_array_elements(t.data->'responses')) sub(s,r)
   ) s ON true;

The manual about combining multiple table functions.

Or you could use WITH ORDINALITY to get original order of elements and combine as you wish:

PostgreSQL unnest() with element number

How to "zip" multiple nested JSON arrays without using id key?

1 Answers1