Rows (structs)

Tables can include columns of the ROW data type.

Tables can include columns of the ROW data type. A ROW, sometimes called a struct, is a set of typed property-value pairs.

Consider a table of customers with columns for name, address, and an ID. The address is a ROW with fields for the elements of an address (street, city, and postal code). As shown in this example, ROW values are returned in JSON format:

=> 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)

Most values are cast to UTF-8 strings, as shown for street and city here. Integers and booleans are cast to JSON Numerics and thus not quoted.

Use dot notation (column.field) to access individual fields:

=> SELECT FROM customers;

(6 rows)

In the following example, the contact information in the customers table has an email field, which is an array of addresses:

=> SELECT name, FROM customers;
        name        |                    email
 Missy Cooper       | ["",""]
 Sheldon Cooper     | ["",""]
 Leonard Hofstadter | [""]
 Leslie Winkle      | []
 Raj Koothrappali   | [""]
 Stuart Bloom       |
(6 rows)

You can use ROW columns or specific fields to restrict queries, as in the following example:

=> SELECT address FROM customers WHERE ='Pasadena';
 {"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}
(4 rows)

You can use the ROW syntax to specify literal values, such as the address in the WHERE clause in the following example:

=> 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)

You can join on field values as you would from any other column:

=> SELECT accountID,department from customers JOIN employees
 accountID | department
       139 | Physics
       142 | Physics
       294 | Astronomy

You can join on full structs. The following example joins the addresses in the employees and customers tables:

=> SELECT,customers.accountID FROM employees
JOIN customers ON employees.personal.address=customers.address;
        name        | accountID
 Sheldon Cooper     |       139
 Leonard Hofstadter |       142
(2 rows)

You can cast structs, optionally specifying new field names:

20]) FROM customers;

 {"str":"911 San Marcos St","city":"Austin","zip":"73344","email":["missy@mit.ed
 {"str":"100 Main St Apt 4B","city":"Pasadena","zip":"91001","email":["shelly@me",""]}
 {"str":"100 Main St Apt 4A","city":"Pasadena","zip":"91001","email":["hofstadte"]}
 {"str":"23 Fifth Ave Apt 8C","city":"Pasadena","zip":"91001","email":[]}

(6 rows)

You can use structs in views and in subqueries, as in the following example:

=> CREATE VIEW neighbors (num_neighbors, area(city, zipcode))
AS SELECT count(*), ROW(, address.zipcode)
FROM customers GROUP BY, address.zipcode;

=> SELECT, neighbors.area FROM neighbors, employees
WHERE employees.personal.address.zipcode=neighbors.area.zipcode AND
m_neighbors > 1;
        name        |                area
 Sheldon Cooper     | {"city":"Pasadena","zipcode":91001}
 Leonard Hofstadter | {"city":"Pasadena","zipcode":91001}
(2 rows)

If a reference is ambiguous, Vertica prefers column names over field names.

You can use many operators and predicates with ROW columns, including JOIN, GROUP BY, ORDER BY, IS [NOT] NULL, and comparison operations in nullable filters. Some operators do not logically apply to structured data and are not supported. See the ROW reference page for a complete list.