Building JSON in Snowflake with OBJECT_CONSTRUCT and ARRAY_AGG

1.OBJECT_CONSTRUCT: from columns to key-value pairs

Going from relational data to semi-structured data uses two building blocks: one function makes objects and another makes arrays. OBJECT_CONSTRUCT returns an OBJECT built from alternating arguments, OBJECT_CONSTRUCT(key, value, key, value, ...). Each key is a VARCHAR and each value can be any data type, including the result of an expression or a scalar subquery. That means one call can mix a column, a computed epoch value and a COUNT(*) from another table into the same JSON document. The function returns a value of type OBJECT, so everything you know about reading objects with the colon, dot and bracket notations applies to its output.

That NULL rule decides whether a downstream consumer sees a key at all. Snowflake separates three cases. A SQL NULL value drops the pair. A JSON null, written PARSE_JSON('NULL'), is kept as null. The string 'null' is just text and is kept as "null". If your target schema requires a key to always be present, pass a JSON null instead of a SQL NULL. Also, don't rely on key order: the constructed object doesn't necessarily keep the order you wrote the pairs in.

SELECT OBJECT_CONSTRUCT(
  'Key_One', PARSE_JSON('NULL'),
  'Key_Two', NULL,
  'Key_Three', 'null') AS obj;

Sources1

2.Whole rows to objects: the wildcard, ILIKE, EXCLUDE and object constants

Listing every column by hand gets tedious. OBJECT_CONSTRUCT(*) turns the entire row into an object, using the column names as keys and the column values as values. You can qualify the wildcard with a table name or alias, as in mytable.*, to take only that table's columns from a join. With no column names available, as with a bare VALUES list, Snowflake uses the keys COLUMN1, COLUMN2 and so on. Unquoted identifiers are stored in uppercase, so the keys come out as PROVINCE and CREATED_DATE. Because the result is an OBJECT, you can read a field back with bracket notation, which is why the examples sort by oc['PROVINCE'].

SELECT OBJECT_CONSTRUCT(*) AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

Two keywords narrow the wildcard. ILIKE 'prov%' keeps only columns whose names match the pattern, and only one pattern is allowed. EXCLUDE province or EXCLUDE (col1, col2) removes the named columns. You can't use both in the same call. With this function, they're valid only in a SELECT list or GROUP BY clause. An OBJECT constant does the same job in a shorter form: {* EXCLUDE province} produces the same output as the OBJECT_CONSTRUCT version.

SELECT OBJECT_CONSTRUCT(*  ?  province) AS oc
  FROM demo_table_1
  ORDER BY oc['PROVINCE'];

Sources1

3.ARRAY_AGG: pivoting rows into arrays

Objects cover a single row. Many-to-one relationships, such as all the order keys for a status or all the clerks for a region, need arrays. ARRAY_AGG (alias ARRAYAGG) is an aggregate that collects its input values into one ARRAY per group and returns an empty array when there is no input. Combined with GROUP BY, it rebuilds the nested shape that FLATTEN takes apart. The input is typically a column name, and the function can also run as a window function when you add an OVER clause. LIMIT is not supported.

SELECT
    o_orderstatus,
    ARRAYAGG(o_clerk) WITHIN GROUP (ORDER BY o_totalprice DESC)
  FROM orders
  WHERE o_totalprice > 450000
  GROUP BY o_orderstatus
  ORDER BY o_orderstatus DESC;

That query uses two separate ORDER BY clauses, and the difference between them is a common exam point. WITHIN GROUP (ORDER BY ...) sets the order of elements inside each array. Without it, element order is unpredictable. The outer ORDER BY sets only the order of the output rows. Write expressions inside WITHIN GROUP, not numbers: a number is read as a constant, not as a column position. A few other rules: DISTINCT removes duplicates, but when you combine it with WITHIN GROUP both must refer to the same column. NULL values are left out of the array. A single call can return at most 128 MB.

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.OBJECT_CONSTRUCT keeps every key you pass, so a SQL NULL value shows up as "key": null.Why is that wrong?

   A pair with a SQL NULL key or value is dropped. Only a JSON null created with PARSE_JSON('NULL') is kept as null.

   Covered in OBJECT_CONSTRUCT: from columns to key-value pairs

2. 2.Putting ORDER BY at the end of the query sorts the elements inside each ARRAY_AGG array.Why is that wrong?

   Only WITHIN GROUP(ORDER BY) sets element order. Without it, the order is unpredictable.

   Covered in ARRAY_AGG: pivoting rows into arrays

Practise it for real

Turn a small relational table into JSON objects, then filter which columns go into them

1. 1.Create demo_table_1 (province VARCHAR, created_date DATE) and insert ('Manitoba', '2024-01-18'::DATE) and ('Alberta', '2024-01-19'::DATE).

   Why: This gives you a known two-row relational source to convert.

   You should see: SELECT province, created_date FROM demo_table_1 returns two rows.

2. 2.Run SELECT OBJECT_CONSTRUCT(*) AS oc FROM demo_table_1 ORDER BY oc['PROVINCE'];

   Why: The wildcard turns column names into keys and values into values.

   You should see: Two objects, each with the keys CREATED_DATE and PROVINCE.

3. 3.Run the same query with OBJECT_CONSTRUCT(* ILIKE 'prov%'), then with the object constant {* EXCLUDE province}.

   Why: This shows the two ways to narrow the wildcard and the shorter object-constant syntax.

   You should see: The ILIKE version keeps only PROVINCE. The EXCLUDE version keeps only CREATED_DATE.

4. 4.Run SELECT OBJECT_CONSTRUCT('Key_One', PARSE_JSON('NULL'), 'Key_Two', NULL, 'Key_Three', 'null') AS obj;

   Why: This confirms the NULL-handling rules for yourself.

   You should see: Key_One is null, Key_Three is "null", and Key_Two is missing.