Document store on top of existing PostgreSQL

Question

We have a database based on PostgreSQL. An entity is stored in one table with its core attributes. There is a separated table that contains names of attributes and another table for the value of attributes.

To be more specific we have something like this:

usersTable: [id, name, surname] - [1, Johny, Doe]
attrNamesTable: [attrId, attrName] - [1, titleBefore]
attrValuesTable: [attrId, attrValue, userId] - [1, prof, 1]

I need a framework that will be on top of this database and will allow access to the records as if they were documents, i.e.:

user: {
    id: 1,
    name: Johny,
    surname: Doe,
    attributes: {
        titleBefore: prof
    }
}

Does anybody have an idea about such framework? Any suggestion might help!

Answer 1

Use jsonb functions and operators:

select to_jsonb(t) || jsonb_build_object('attributes', json_object_agg("attrName", "attrValue")) as "user"
from "usersTable" t
join "attrValuesTable" a on a."userId" = t."id"
join "attrNamesTable" n using ("attrId")
group by t.id

Test it on DbFiddle.