PostgreSQL json function get array result + fk array for a complete reference

Question

I would like a function get a result look like this:

Student:

ID	Name	ID_TEACHER
1	Jhon	1
2	Maria	1

Teacher:

ID	Name
1	Peter
2	Joseh

Query:

select json_fk_full(t) from Student t;

Result:

{student: [{"ID":1, "Name":"Jhon", "ID_TEACHER": 1},
           {"ID":1, "Name":"Jhon", "ID_TEACHER": 1}],
 teacher: [{"ID":1, "Name":"Peter"}]}

Answer 1

You can cast whole rows to jsonb using to_jsonb(), or cast&aggregate them with jsonb_agg(): demo

select jsonb_build_object('student',jsonb_agg(student),
                          'teacher',to_jsonb(teacher))
from teacher left join student 
  on teacher."ID"=student."ID_TEACHER"
group by to_jsonb(teacher);

jsonb_build_object
{"student": [null], "teacher": {"ID": 2, "Name": "Joseh"}}
{"student": [ {"ID": 1, "Name": "Jhon", "ID_TEACHER": 1},       {"ID": 2, "Name": "Maria", "ID_TEACHER": 1}], "teacher": {"ID": 1, "Name": "Peter"}}

Normally this type of group by isn't a good idea but that's just to demonstrate how you can let Postgres automagically deduce the desired jsonb structure based on row type passed into these functions.

I'm not sure what rules you have in mind as to how exactly you want to map these tables to the jsonb, so I went with an intuitive "one row=one teacher and all of their students". If that's not the case, you're welcome to add a correction.