SQL join arbitrary information to form a mixed list

Question

I have a list of items of different Types with their respective ID. Items will be grouped together by a group ID:

Type  | ID   | GroupID
-----------------------
Type1 | 1234 | 1 
Type1 | 2345 | 1 
Type2 | 1234 | 1 
Type3 | 1234 | 1 
Type1 | 1234 | 2 

I have separate tables for each Type. The schemas are completely unrelated to one another, therefore UNION is not an option:

Type1 <id, name, status>
Type2 <id, name, description, foo, bar>
Type3 <id, name, foobar, barfoo, foofoo, hoodeedoo>

Is there a way to get all of the data joined in a single list. Over time, we will be adding more types and lists will be loaded on a per group basis. Querying the above data set for Group 1 would return the following [pseudo] data:

[
  { 1234, name, status }, // Type 1
  { 2345, name, status }, // Type 1
  { 1234, name, description, foo, bar }, // Type 2
  { 1234, name, foobar, barfoo, foofoo, hoodeedoo } // Type 3
]

(update) Purpose of this request:

Our UI will display this list of items. We will have conditional logic to change the behaviors of items clicked. For example, if a Type1 item is clicked, run the behaviors for Type1 - and so on for other types.

Answer 1

If you want to have a result which has a variable structure, your best bet is JSON. The following query would UNION the results of looking at type_1, type_2 and type_3 and give you the result as JSON:

SELECT 
    json_agg(r) AS result
FROM
(
    SELECT row_to_json(t) AS r
       FROM type_1 t
            JOIN item_list il ON il.type='Type1' AND il.id = t.id
    UNION ALL
    SELECT row_to_json(t) AS r
       FROM type_2 t
            JOIN item_list il ON il.type='Type2' AND il.id = t.id
    UNION ALL
    SELECT row_to_json(t) AS r
       FROM type_3 t
            JOIN item_list il ON il.type='Type3' AND il.id = t.id
) AS s0 ;

This is what you'd get:

|                                            result                                                         |
|-----------------------------------------------------------------------------------------------------------|
| [{"id":1234,"name":"name","status":"status"},                                                             |
|  {"id":1234,"name":"name","status":"status"},                                                             |
|  {"id":2345,"name":"name-2","status":"status-2"},                                                         |
|  {"id":1234,"name":"name","description":"description","foo":"foo","bar":"bar"},                           |
|  {"id":1234,"name":"name","foobar":"foobar","barfoo":"barfoo","foofoo":"foofoo","hoodeedoo":"hoodeedoo"}] |

You can check it a SQLFiddle

---

NOTE: The way the SQL is written does not guarantee any ordering. If needed, you should add the appropriate ORDER BY before aggregating.

Answer 2

Disclaimer: even with your clarification, I don't know if this is a good solution for you, just that it's a solution.

You could use string concatenation and UNION. It complicates your query nontrivially, but it's certainly possible.

Instead of

SELECT * FROM type1
  UNION ALL
SELECT * FROM type2
  UNION ALL
  ...

You could do

SELECT id, name, status AS info FROM type1
  UNION ALL
SELECT id, name, description || foo || bar AS info FROM type2
  UNION ALL
SELECT id, name, foobar || barfoo || foofoo || hoodeedoo AS info from type3
  ...

That returns you table data that looks like:

  id  |  name  |            info
------+--------+-----------------------------
 1234 |  name  | status
 2345 |  name  | status
 1234 |  name  | descriptionfoobar
 1234 |  name  | foobarbarfoofoofoohoodeedoo

... and so forth.

For readability, you'd probably want to concat some spaces in there. Something like description || ' ' || foo || ' ' || bar, or maybe some other sort of separator. SQL string manipulation isn't exactly great, but it's possible, and it might solve this problem for you.