SnowPro Advanced Data Engineer

By Snowflake · Question Mode

QuestionQ27

Transform Data

A Data Engineer must share customer information with a partner application through an API. The API accepts a JSON payload with the following structure:

Question Image

All information is available in the CUSTOMER table. A single customer can have multiple rows with distinct contact details.

Which query prepares the JSON payload in the required format?

Explanation

OBJECT_CONSTRUCT builds the nested address and contact objects, while ARRAY_AGG(contacts) OVER (PARTITION BY username) collects every contact object for the same customer into the required contactDetails array. The outer OBJECT_CONSTRUCT creates the required top-level username and contactDetails fields. Snowflake documents that OBJECT_CONSTRUCT creates an object from key/value pairs and that ARRAY_AGG returns input expressions as an array and supports OVER (PARTITION BY ...).

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!