行（结构）
表可以包含 ROW 数据类型的列。ROW（有时称为结构）是一组类型化的属性值对。
考虑一个包含 name、address 和 ID 列的 customers 表。地址是一个 ROW，其中包含地址元素的字段（街道、城市和邮政编码）。如本例所示，ROW 值以 JSON 格式返回：
=> SELECT * FROM customers ORDER BY accountID;
name | address | accountID
--------------------+--------------------------------------------------------------------+-----------
Missy Cooper | {"street":"911 San Marcos St","city":"Austin","zipcode":73344} | 17
Sheldon Cooper | {"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":91001} | 139
Leonard Hofstadter | {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001} | 142
Leslie Winkle | {"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":91001} | 198
Raj Koothrappali | {"street":null,"city":"Pasadena","zipcode":91001} | 294
Stuart Bloom | | 482
(6 rows)
大部分的值都强制转换为 UTF-8 字符串，如此处的 street 和 city 所示。整数和布尔值强制转换为 JSON 数字，因此不被引用。
使用点表示法 (column.field) 访问各个字段：
=> SELECT address.city FROM customers;
city
----------
Pasadena
Pasadena
Pasadena
Pasadena
Austin
(6 rows)
在以下示例中，customers 表中的联系信息有一个 email 字段，该字段是地址数组：
=> SELECT name, contact.email FROM customers;
name | email
--------------------+---------------------------------------------
Missy Cooper | ["missy@mit.edu","mcooper@cern.gov"]
Sheldon Cooper | ["shelly@meemaw.name","cooper@caltech.edu"]
Leonard Hofstadter | ["hofstadter@caltech.edu"]
Leslie Winkle | []
Raj Koothrappali | ["raj@available.com"]
Stuart Bloom |
(6 rows)
您可以使用 ROW 列或特定字段来限制查询，如下例所示：
=> SELECT address FROM customers WHERE address.city ='Pasadena';
address
--------------------------------------------------------------------
{"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":91001}
{"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001}
{"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":91001}
{"street":null,"city":"Pasadena","zipcode":91001}
(4 rows)
您可以使用 ROW 语法来指定字面量值，例如以下示例中 WHERE 子句中的地址：
=> SELECT name,address FROM customers
WHERE address = ROW('100 Main St Apt 4A','Pasadena',91001);
name | address
--------------------+-------------------------------------------------------------------
Leonard Hofstadter | {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001}
(1 row)
您可以像从任何其他列中一样联接字段值：
=> SELECT accountID,department from customers JOIN employees
ON customers.name=employees.personal.name;
accountID | department
-----------+------------
139 | Physics
142 | Physics
294 | Astronomy
您可以联接完整的结构。以下示例联接 employees 和 customers 表中的地址：
=> SELECT employees.personal.name,customers.accountID FROM employees
JOIN customers ON employees.personal.address=customers.address;
name | accountID
--------------------+-----------
Sheldon Cooper | 139
Leonard Hofstadter | 142
(2 rows)
您可以强制转换结构，且可选择指定新的字段名称：
=> SELECT contact::ROW(str VARCHAR, city VARCHAR, zip VARCHAR, email ARRAY[VARCHAR,
20]) FROM customers;
contact
--------------------------------------------------------------------------------
----------------------------------
{"str":"911 San Marcos St","city":"Austin","zip":"73344","email":["missy@mit.ed
u","mcooper@cern.gov"]}
{"str":"100 Main St Apt 4B","city":"Pasadena","zip":"91001","email":["shelly@me
emaw.name","cooper@caltech.edu"]}
{"str":"100 Main St Apt 4A","city":"Pasadena","zip":"91001","email":["hofstadte
r@caltech.edu"]}
{"str":"23 Fifth Ave Apt 8C","city":"Pasadena","zip":"91001","email":[]}
{"str":null,"city":"Pasadena","zip":"91001","email":["raj@available.com"]}
(6 rows)
您可以在视图和子查询中使用结构，如下例所示：
=> CREATE VIEW neighbors (num_neighbors, area(city, zipcode))
AS SELECT count(*), ROW(address.city, address.zipcode)
FROM customers GROUP BY address.city, address.zipcode;
CREATE VIEW
=> SELECT employees.personal.name, neighbors.area FROM neighbors, employees
WHERE employees.personal.address.zipcode=neighbors.area.zipcode AND neighbors.nu
m_neighbors > 1;
name | area
--------------------+-------------------------------------
Sheldon Cooper | {"city":"Pasadena","zipcode":91001}
Leonard Hofstadter | {"city":"Pasadena","zipcode":91001}
(2 rows)
如果引用不明确，Vertica 会优先选择列名而不是字段名。
您可以对 ROW 列使用许多运算符和谓词，包括 JOIN、GROUP BY、ORDER BY、IS [NOT] NULL 以及可 null 筛选器中的比较操作。某些运算符在逻辑上不适用于结构化数据并且不受支持。有关完整列表，请参阅 ROW 参考页面。