Postgres/JSON - translate parent child relation to a json

Question

I have the following records in a postgres database. The parent_pk is related to the pk in a parent-child relation.

pk             name             type            parent_pk
---            ----             ----            ---------
1              hnumber101       house           0
2              hnumber201       house           0
101            dodge_charger    vehicle         1
102            mustang          vehicle         1
103            civic            vehicle         2
301            john             user            101
302            edward           user            102
303            john             user            102
304            john             user            103

And I want to generate a json out of the above in the following format-

[  {
   "id": 1,
   "name": "hnumber101",
   "type": "house"
   "child": [ {
              "id": 101,
              "name": "dodge charger",
              "type": "vehicle"
              "child": [{
                       "id": 301,
                       "name": "john",
                       "type": "user"
                       }],                            
              },
              {
              "id": 102,
              "name": "mustang",
              "type": "vehicle"
              "child": [{
                       "id": 303,
                       "name": "john",
                       "type": "user"
                       },
                       {
                       "id": 302,
                       "name": "edward",
                       "type": "user"
                       }],                            
              }],
  },
 {
   "id": 2,
   "name": "hnumber201",
   "type": "house"
   "child": [ {
              "id": 103,
              "name": "civic",
              "type": "vehicle"
              "child": [{
                       "id": 304,
                       "name": "john",
                       "type": "user"
                       }],

              }],
  }]

I have tried using WITH RECURSIVE query, but that yields the list of entries and then i have to do a lot of looping around/hash maps in my java code to get that child block inside the parent. What would be the most efficient way to do this?

The JSON support in 9.2 is very limited. The `row_to_json` constructor doesn't support anonymous rows, there's no `scalar_to_json` or `json_escape` to wrap a single value up as JSON, and there are no aggregates or merge/append functions. This makes building values that don't correspond to a rowtype very hard :-( — Craig Ringer, Aug 09 '12 at 23:35
DO you have a code that load the JSON file into the database table, keeping ID and parentID ? — Dumitru Gutu, Dec 12 '15 at 18:16

score 4 · Accepted Answer · answered Aug 09 '12 at 22:32

4

Something along these lines:

SELECT things.pk, things.name, things.type, array_agg(sub.pk) AS children
FROM things
LEFT JOIN things sub ON sub.parent_pk = things.pk
GROUP BY things.pk, things.name, things.type;

 pk  |     name      |  type   | children  
-----+---------------+---------+-----------
   1 | hnumber101    | house   | {102,101}
 101 | dodge charger | vehicle | {301}
 102 | mustang       | vehicle | {303,302}
 301 | john          | user    | {NULL}
 302 | edward        | user    | {NULL}
 303 | john          | user    | {NULL}

Gather the rows into Map keyed by pk Map<Integer, Thing>.

class Thing {
  Integer id;
  String name;
  String type;
  Integer[] children;

  public String printMe(Map<Integer, Thing> allThings) {
    String ret = "... format json stuff here";
    for(Integer childId in children) {
      Thing child = allThings.get(childId);
      ret += child.printMe(allThings);
    }
    ret += "Format json stuff here";
    return ret;
  } 
};

answered Aug 09 '12 at 22:32

jkj

2,561
1
17
24

Yep. If you want them as JSON straight out of the database you have to `CREATE TYPE` a new rowtype like `CREATE TYPE blah(id integer,name text,type text,children int[])` then wrap the above query in a SQL function that `RETURNS SETOF blah`. You can then `select row_to_json(x) FROM the_function() AS x` or `SELECT array_to_json(array_agg(x)) from the_function() as x`. Painfully roundabout compared to the XML or hstore support because json doesn't have any useful aggregates or operators yet. – Craig Ringer Aug 09 '12 at 23:38
That works great. I like the query, that makes things much more simple. I have another question. Is it possible to fill the 'null' child for users with parent ids? So for users the child is the list of parents. It is like querying what cars john drives in that house? Just trying to think if its possible to get all info in one query. – blue01 Aug 10 '12 at 19:56
@jkj: i have similar requirement, how could i write above SQL in Oracle. – JDev May 07 '13 at 21:58
@Harpreet: i have a similar question under http://stackoverflow.com/questions/16364413/generate-json-for-the-parent-child-rows-parent-child-depth-is-dynamic?noredirect=1#comment23454535_16364413.. now my SQL output is same as that of yours. But i am not sure how to create parent child structure in JSON string. i think you are doing it in. I would really appreciate if you could please let me know how are you doing it in Java. – JDev May 09 '13 at 23:35
Can It handle , nested Childs. In otherwords if child contains child, how it can handle – Ajaya Nayak Oct 01 '20 at 08:13

Postgres/JSON - translate parent child relation to a json

1 Answers1