json - 格式 JSON Postgresql

Question

我正在使用内部联接来联接 3 个表，Owner、Store 和 Machine。我正在尝试从多个表中查看输出 JSON:

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;

我想要这样的 JSON 格式:

{
    "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"
            }]
        }]
}

但是返回的JSON格式不是这样的我正在使用 PostgreSQL。

Answer 1

最简单(也可能是唯一明智的)方法是在表级别从单个记录构建 JSON 子文档，然后才分层连接它们:

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);