How to write postgresql query to build the json output like this?

Question

I have a postgresql rational table which has 2 level hierarchical data, and i want to write a query to build the json output like below, i tried some time but cannot figure out. i guess need use some functions like row_to_json, json_agg, json_build_object, json_build_array, etc. but i really cannot get it done successfully. Anyone can help, thanks!

Have this rational table as input:

select * from team;
+------------+------------+------------+
| country    | city       | member     |
|------------+------------+------------|
| china      | bejing     | betty      |
| china      | bejing     | bruce      |
| china      | shanghai   | scott      |
| usa        | chicago    | cindy      |
| usa        | newyork    | nancy      |
| usa        | newyork    | nathan     |
+------------+------------+------------+
SELECT 6

Want a json like below as output:

[
    {
        "name": "china",
        "type": "country",
        "children": [
            {
                "name": "beijing",
                "type": "city",
                "children": [
                    { "name": "betty", "type": "member" },
                    { "name": "bruce", "type": "member" }
                ]
            },
            {
                "name": "shanghai",
                "type": "city",
                "children": [
                    { "name": "scott", "type": "member" }
                ]
            }
        ]
    },
    {
        "name": "usa",
        "type": "country",
        "children": [
            {
                "name": "chicago",
                "type": "city",
                "children": [
                    { "name": "cindy", "type": "member" }
                ]
            },
            {
                "name": "newyork",
                "type": "city",
                "children": [
                    { "name": "nancy", "type": "member" },
                    { "name": "nathan", "type": "member" }
                ]
            }
        ]
    }
]

Answer 1

I would probably write

SELECT json_agg(json_build_object(
   'type', 'country',
   'name', country,
   'children', children
)) AS root
FROM (
   SELECT country, json_agg(json_build_object(
       'type', 'city',
       'name', city,
       'children', children
   )) AS children
   FROM (
       SELECT country, city, json_agg(json_build_object(
           'type', 'member',
           'name', member
       )) as children
       FROM team
       GROUP BY country, city
   ) cities
   GROUP BY country
) countries

(online demo)

Unfortunately, I did not find a way to write this with subqueries in the json_build_object.