SQL combines arbitrary information to form a mixed list

I have a list of elements of different types with their corresponding identifier. Elements are grouped together with the group identifier:

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. Schemes are not completely connected with each other, therefore UNIONit 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 the data combined into one list. Over time, we will add more types, and lists will be loaded for each group. Querying the above dataset for Group 1 will 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:

. , . , Type1, Type1 .. .

+4
2

, JSON. UNION, type_1, type_2 type_3 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 ;

, :

|                                            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"}] |

SQLFiddle


. SQL . , ORDER BY.

+4

: , , .

UNION. , , , .

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

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

, :

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

... ..

, , . - description || ' ' || foo || ' ' || bar, , , - . SQL- , , .

+4

Source: https://habr.com/ru/post/1668680/


All Articles