Hi all,
I'd like to propose a new SQL data type, OBJECT, for structured objects. It is
a structured sibling of STRUCT in which attribute names are part of the type,
so a value is matched by name, not by position.
The problem
Today STRUCT matches fields by position. With ANSI mode on, inserting
named_struct('salary', 150000, 'is_contractor', true) into a column of type
STRUCT<name STRING, title STRING> succeeds and stores name = '150000' and title
= 'true'. Comparison behaves the same way: named_struct('a', 1, 'b', 2) =
named_struct('b', 1, 'a', 2) returns true. INSERT ... BY NAME helps, but it is
per statement, the table owner cannot require it, and comparison, CAST, and set
operations remain positional.
The proposal
OBJECT keeps STRUCT's shape (ordered, typed attributes, field access, nesting).
Assignment and comparison require names to match at each position, with no
implicit reordering. Reordering needs an explicit cast that remaps by name:
CAST(object('firstname', 'Ada', 'lastname', 'Lovelace')
AS OBJECT<lastname STRING, firstname STRING>);
It would come with OBJECT_APPEND, OBJECT_DELETE, and OBJECT_PICK, and explicit
casts to and from STRUCT. It is gated by spark.sql.objectType.enabled, default
false. STRUCT is unchanged, and no new on-disk format is introduced.
Not in scope
changing STRUCT, Snowflake-style open semi-structured objects (VARIANT and MAP
already cover those), CREATE TYPE UDTs, or a new storage format.
Estimate
about 3 months. The proposal lists mid-term and final checkpoints in the SPIP.
The full SPIP (motivation, design, risks, rejected alternatives) is in the JIRA
ticket: [https://www.google.com/s2/favicons?domain=apache.org&sz=64]
SPARK-61931<https://issues.apache.org/jira/browse/SPARK-61931>. I would welcome
feedback on the problem statement, the naming, and whether the rejected
alternatives are the right ones to consider.
Thanks, Serge Rielau