'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 |
---|