Json in Postgresql

Question

I'm learning Postgresql and Json. I have for example database like that:

CREATE TABLE employees (
  employee_id serial primary key,
  department_id integer references departments(department_id),
  name text,
  start_date date,
  fingers integer,
  geom geometry(point, 4326)
  );

CREATE TABLE departments (
  department_id bigint primary key,
  name text
  );

INSERT INTO departments
 (department_id, name)
VALUES
 (1, 'spatial'),
 (2, 'cloud');

INSERT INTO employees
 (department_id, name, start_date, fingers, geom)
VALUES
 (1, 'Paul',   '2018/09/02', 10, 'POINT(-123.32977 48.40732)'),
 (1, 'Martin', '2019/09/02',  9, 'POINT(-123.32977 48.40732)'),
 (2, 'Craig',  '2019/11/01', 10, 'POINT(-122.33207 47.60621)'),
 (2, 'Dan',    '2020/10/01',  8, 'POINT(-122.33207 47.60621)');

How could i do so i could get the data like this:

[
    {
        "department_name": "cloud",
        "employees": [
            {
                
                "name": "Craig",
                "start_date": "2019-11-01"
            },
            {
                
                "name": "Dan",
                "start_date": "2020-10-01"
            }
        ]
    },
    {
        "department_name": "spatial",
        "employees": [
            {
                
                "name": "Paul",
                "start_date": "2018-09-02"
            },
            {
                
                "name": "Martin",
                "start_date": "2019-09-02"
            }
        ]
    }
]

Answer 1

follow this link: https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg/200240#200240

---

CREATE TEMP TABLE x (
    name text,
    start_date date
);

WITH cte AS (
    SELECT
        d.name AS department_name,
        json_agg((e.name, e.start_date)::x) AS employees
    FROM
        departments d
        JOIN employees e ON d.department_id = e.department_id
    GROUP BY
        1
)
SELECT
    json_agg((row_to_json(cte.*)))
FROM
    cte;