Reference to a field of a row object

Question

I'm having trouble accessing the fields of row objects which I have created in Presto. The Presto documentation claims "fields... are accessed with field reference operator." However that doesn't seem to work. This code reproduces the problem:

CREATE TABLE IF NOT EXISTS data AS
SELECT * FROM (VALUES
    (1, 'Adam', 17),
    (2, 'Bill', 42)
) AS x (id, name, age);

CREATE TABLE IF NOT EXISTS ungrouped_data AS
 WITH grouped_data AS (
  SELECT
    id,
    ROW(name, age) AS name_age
  FROM data
 )
SELECT
  id,
  name_age.1 AS name,
  name_age.2 AS age
FROM grouped_data;

Which returns an "extraneous input '.1'" error.

Answer 1

ROW(name, age) will create an row without field names. Today to access the fields in such row, you need to cast it into a row with field names. Try this:

WITH grouped_data AS ( 
    SELECT 
      id, 
      CAST(ROW(name, age) AS ROW(col1 VARCHAR, col2 INTEGER)) AS name_age 
    FROM data 
) 
SELECT 
  id, 
  name_age.col1 AS name, 
  name_age.col2 AS age 
FROM grouped_data;

Result:

 id | name | age
----+------+-----
  1 | Adam |  17
  2 | Bill |  42

See https://github.com/prestodb/presto/issues/7640 for discussions on this.