Format JSON Postgresql

Question

I'm using inner join to join 3 tables, Owner, Store and Machine. I'm trying to view output JSON from multiple tables like this:

SELECT ow.*, st.*, ma.* 
FROM owner ow 
   INNER JOIN st.store ON ow.OwnerId = st.OwnerId 
   INNER JOIN machine ma ON ma.StoreId = st.StoreId;

I want JSON formatted like this:

{
    "OwnerId": "1d2dd",
    "Name": "name test",
    "Store":[{
        "StoreId": "s3ss5",
        "Name": "Store1",
        "Code": "bla",
        "Machine":[{
            "MachineId": "axpeo",
            "Name": "Machine1",
            "Type": "type1"
            }]
        },
        {
        "StoreId": "ddf22",
        "Name": "Store2",
        "Code": "ble",
        "Machine":[{
            "MachineId": "weds",
            "Name": "Machine2",
            "Type": "type2"
            },
            {
            "MachineId": "axdso",
            "Name": "Machine3",
            "Type": "type3"
            }]
        }]
}

but the return JSON is not formatted like this I'm using PostgreSQL.

Answer 1

The easiest (and probably only sensible) way to do this is to build JSON sub-documents from individual records at table level and only then hierarchically joining them:

SELECT json_build_object('OwnerId', ownerid,
                         'Name', name,
                         'Store', stores)
FROM owner
JOIN (
    SELECT ownerid,
           json_agg(
               json_build_object('StoreId', storeid,
                                 'Name', name,
                                 'Code', code,
                                 'Machine', machines)) AS stores
    FROM store
    JOIN (
        SELECT storeid,
               json_agg(
                   json_build_object('MachineId', machineid,
                                     'Name', name,
                                     'Type', type)) AS machines
        FROM machine
        GROUP BY storeid) m USING (storeid)
    GROUP BY ownerid) s USING (ownerid);

Answer 2

The output is not exactly what i want, but it is better...this is the output

[{
    "OwnerId": "1d2dd",
    "Name": "name test",
    "Store":{
        "StoreId": "s3ss5",
        "Name": "Store1",
        "Code": "bla",
        "Machine":{
            "MachineId": "axpeo",
            "Name": "Machine1",
            "Type": "type1"
            }
        }
},
{
    "OwnerId": "1d2dd",
    "Name": "name test",
    "Store":{
        "StoreId": "ddf22",
        "Name": "Store2",
        "Code": "ble",
        "Machine":{
            "MachineId": "weds",
            "Name": "Machine2",
            "Type": "type2"
            }
        }

},
{
    "OwnerId": "1d2dd",
    "Name": "name test",
    "Store":{
        "StoreId": "ddf22",
        "Name": "Store2",
        "Code": "ble",
        "Machine":{
            "MachineId": "axdso",
            "Name": "Machine3",
            "Type": "type3"
            }
        }
}]

it does not join the machines from the same store yet like an array

Answer 3

For one-to-many relationships formatted to JSON try something like this:

SELECT "owner"."id",
    json_agg(DISTINCT "store".*) AS "stores", 
    json_agg(DISTINCT "machine".*) AS "machines"
FROM "owners"
INNER JOIN "stores"
ON "stores"."ownerId" = "owners"."id"
INNER JOIN "machines"
ON "machines"."storeId" = "stores"."id"
WHERE "owner" = 1
GROUP BY "owner"."id";