Join 1 row out of multiple 1:1 tables and return as array of json objects

Question

I have a database structure like so (in Postgres):

An entity has a list of actions associated with it. Each Action has one associated row out of all action-specific tables. Each of these action types are separated into different tables because they store significantly different data. All common data is stored on the Action table, but the data specific to the type of action is in the table for that action type.

Ideally, what I am looking to do is create a query that would return the actions for an entity like so. Do you know of a way to query these to produce a result like this?

[
    ...
    {
        action: {
            heat: 450
        }
    }
]

The best I've gotten so far was this...

[
    {
        json: [{ action: {...} }]
    }
]

with a combination of json_agg and json_build_object. Of course, I'm open to alternatives. My SQL skillset is a bit green, so there's a good chance that there's a better way of structuring or querying this.

Thanks!

Answer 1

I should just use a JSON column on the Action table to store the action-specific data.