I’m working on a personal project implying to represent hierarchical data with permission management on each node level (inheritance allowed) which I’ll call containers here for simplicity. Like a structure of folder and files in standard file system. I am working on a stack which is PostgreSQL on one side and go(lang) as backend language. I’m a complete beginner with PostgreSQL so don’t hesitate to tell me if my design is completely wrong…

My problem comes from querying this kind of structure in PGSQL since each container has common columns but has also specific columns. So there is the idea of inheritance and recursivity but also polymorphism since each level is a container with common columns. One point to note is that since I’m using separate tables for each container, my structure is static and there will be no infinite sublevels.

Here is a simplified diagram:

Each container on this diagram has a dedicated join table with a permission table to handle permission by container level. But I don’t think this is relevant here.

In the project I’m building I’m trying to query this structure all at once by representing the tree as objects containing sub objects just like json would do it. I currently succeed to query and get the results I want by using a combination of json_object and json_agg functions in one query but since I’m using golang I would like to use standard SQL or PL/SQL query to return result in native types and avoid a json marshalling. The idea would be to have a struct for each container type with the corresponding fields and for each container level (here A and B) having an array or slice containing the children containers.

Due to this problem, I started to question my structure and I imagined having only one table container with a JSON column for all the specific data values inside of it but it would implies treatment in golang to dispatch the children structs in each corresponding parent struct sinbce the result of the query would be at the same level…

I think this kind of situations is pretty common but I don’t find any ressources on it… So I’m wondering how the others are getting their objects

So please if you have any idea… or maybe critics about the model don’t hesitate to comment. Thank you so much.