A Data Engineer needs to share customer data using an API with a partner application. The API will accept the JSON payload using this structure: All information can be found in table, CUSTOMER. There

Question

Exhibits

Options

• Aselect object_construct( 'username', username, 'contactDetails', object_construct( 'email', email, 'address', object_construct( 'City', city_name, 'Street', street_name, 'House_no', house_no, 'ZIP', zip_code ) ) ) ) as API_PAYLOAD from CUSTOMER;
• Bwith customerSummary as ( select object_construct( 'email', email, 'address', object_construct( 'City', city_name, 'Street', street_name, 'House_no', house_no, 'ZIP', zip_code ) ) contacts, username from CUSTOMER ) select object_construct( 'username', username, 'contactDetails', array_agg(contacts) over (partition by username) ) as API_PAYLOAD from customerSummary;
• Cselect array_agg(object_construct( 'email', email, 'address', object_construct( 'City', city_name, 'Street', street_name, 'House_no', house_no, 'ZIP', zip_code ) )) over (partition by username) as ContactDetails, username from CUSTOMER;

• A
  10% (2)
• B
  70% (14)
• C
  20% (4)

Explanation

Because a single customer can have multiple rows with different contact details, the payload's contactDetails field must be a JSON array. Option B correctly uses a CTE to first build individual contact objects (combining email and nested address object), then applies ARRAY_AGG as a window function partitioned by username to collect all contacts per customer into an array, and finally wraps everything in OBJECT_CONSTRUCT. Option A builds a single OBJECT_CONSTRUCT per row without aggregating across rows, so customers with multiple contacts would produce duplicate top-level objects instead of one object with an array. Option C returns an array of contacts and the username as separate columns rather than a single nested JSON payload object - it does not produce the required structure.

No community discussion yet for this question.