Create OBJECT with column values as Key in Snowflake

Question

I have multiple priority columns FROM Table A.

Priority_A    Priority_B      SOURCE      KEY
    1            1              A         Z
   NULL          NULL          NULL       Z
   NULL          2              B         Z
   NULL          NULL          NULL       Y
   NULL          NULL          NULL       Y
   NULL          3              C         Y
   NULL          3              C         Z

I want to create a single OBJECT in Snowflake using this column as its KEY.

When I do

SELECT OBJECT_CONSTRUCT(KEY, OBJECT_CONSTRUCT(PRIORITY_A, SOURCE
                                              PRIORITY_B, SOURCE)) AS Prio FROM A

I do not get a single object. I get 7 OBJECTS on 7 different rows. I want Snowflake to output one OBJECT with the following output.

{'Z':{
PRIORITY_B:{
      1:A
      2:B
      3:C
      
            }
PRIORITY_A:{
      1:A
            }
      }
'Y':{PRIORITY_B:{3:C}
     }
}

So we have an object with some keys from column key. This key has two OBJECTS as its value, with keys equal to the columnName, namely Priority_A and Priority_B.

Is this possible in Snowflake?

If not, I am also okay with the following output, with two different OBJECTS on two rows:

Object 1:

{'Z':{
PRIORITY_B:{
      1:A
      2:B
      3:C
            }
PRIORITY_A:{
      1:A
            }
      }
}

Object 2 on row 2:

{'Y':{PRIORITY_B:{3:C}}}

Answer 1

You need to use group by to group on Key, and then aggregate. Try this:

SELECT 
   object_construct(KEY, OBJECT_AGG(priority,source::variant))
FROM 
    A 
where priority is not null and source is not null
GROUP BY 
    KEY;

updated solution for the updated question:

WITH PriorityB AS (
    SELECT 
        KEY, 
        OBJECT_AGG(PRIORITY_B, SOURCE::variant) as PRIORITY_B_JSON
    FROM A
    WHERE PRIORITY_B IS NOT NULL
    GROUP BY KEY
),
PriorityA AS (
    SELECT 
        KEY, 
        OBJECT_AGG(PRIORITY_A, SOURCE::variant) as PRIORITY_A_JSON
    FROM A
    WHERE PRIORITY_A IS NOT NULL
    GROUP BY KEY
)
SELECT 
    COALESCE(a.KEY, b.KEY) as KEY,
    OBJECT_CONSTRUCT(
        'PRIORITY_B', b.PRIORITY_B_JSON, 
        'PRIORITY_A', a.PRIORITY_A_JSON
    ) AS result
FROM PriorityA a
FULL OUTER JOIN PriorityB b ON a.KEY = b.KEY;