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

Reply via email to