PostGIS - Add Column of Lines that Intersect Point

Question

New to PostGIS. I have a road network which I have self-intersected/inner joined to generate nodes at each intersection.

I would like to add a column to the junctions table which lists all of the lines that intersect the generated node. I'm removing duplicate geometries, so at currently pulling the road_id's from the input data ends up with two identifiers, even when junctions have 3+ roads.

Is it best to not remove duplicates upon initial creation, and then alter table to remove duplicates and concatenate the road_ids into a single column? Or can the code be adjusted to do this on creation?

Have searched but can't find anything.

EDIT: Code used to create point geomteries below.

CREATE TABLE junctions as
SELECT 
    ROW_NUMBER() OVER () AS junction_id,
    ST_Intersection(a.geom, b.geom) AS geom
FROM roads a
INNER JOIN roads b
  ON a.geom && b.geom
  AND ST_Intersects(a.geom, b.geom)
  AND a.road_id != b.road_id
GROUP BY
    ST_Intersection(a.geom, b.geom);
;

Answer 1

You can use array_agg to collect the line IDs at the intersection point. The number of lines can be more then two, so they can be collected in an array ba array_agg function:

CREATE TABLE junctions as
SELECT 
    ROW_NUMBER() OVER () AS junction_id, 
    array_agg(b.road_id) AS road_ids,
    ST_Intersection(a.geom, b.geom) AS geom
FROM roads a
INNER JOIN roads b
  ON a.geom && b.geom
  AND ST_Intersects(a.geom, b.geom)
  AND a.road_id != b.road_id
GROUP BY
    ST_Intersection(a.geom, b.geom);