'JOIN 2 tables with an array as a result in the object

I have the following tables:

Table 'images':

file_name
file1.jpg
file2.jpg

Table 'types':

type
type_a
type_b

Table 'images_types_bridge':

file_name type
file1.jpg type_a
file2.jpg type_a
file2.jpg type_b

I'd like to run a query where I would get the following array returned:

results = [
  {
    "file_name": "file1.jpg",
    "type": ["type_a"]
  },
  {
    "file_name": "file2.jpg",
    "type": ["type_a", "type_b"]
  }
]

Note the "type" returns as an array. I could get this result if I run multiple queries, one to get an array from the 'images' table then run a loop through those results and query 'images_types_bridge' for each file_name. Unfortunately, this would become very resource-intensive for larger image arrays.

If I do a basic JOIN, such as:

SELECT images.file_name AS file_name, images_types_bridge.type AS type
  FROM images
  LEFT JOIN images_types_bridge USING (file_name)

I would get 3 results, two for file2.jpg and one for file1.jpg, each with a single string for "type".

I need the results to be as "results" shown above.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source