How to populate an array of ids in a select?-CodePudding

Suppose I have the following two tables:

-- table_1

|   id    |  name  |
|   1000  |  name1 |
|   1001  |  name2 |
|   1002  |  name3 |
|   1003  |  name4 |

-- table_2
_______________________________
|  id  |   table_1_fk_list    |
|  101 |       [1003]         |

My query returns the following:

[
    {
        "id": "101",
        "table_1_elements_list": [ "1003" ],
    },
]

But I would like to return this instead:

[
    {
        "id": "101",
        "table1ElementsList": [
            {
                "id": "1003",
                "name": "name4"
            }
        ],
    },
]

Is it possible to do this? Here is my query:

SELECT
     id,
     table_1_fk_list as "table1ElementsList"
FROM table_2

I've tried joining these two tables, like so, but it didn't work:

SELECT
     id,
     table_1_fk as "table1ElementsList"
FROM table_2 t2
JOIN table_1 t1
ON (t1.id = ANY(t2.table_1_fk_list))

With MongoDB, I can use $unwind with aggregation or just a simple populate will do, but I'm a little new to Postgres.

CodePudding user response：

You have two levels of aggregation in your expected json result. Try something like this :

SELECT jsonb_agg(jsonb_build_object('id', t2.id, 'table1ElementsList', t3.json_data)
  FROM table_2 AS t2
 CROSS JOIN LATERAL
     ( SELECT jsonb_agg(jsonb_build_object('id', t1.id, 'name', t1.name)) AS json_data
         FROM unnest(t2.table_1_fk_list) AS fkl(id)
        INNER JOIN table_1 AS t1
           ON t1.id = fkl.id
     ) AS t3