Previous Next JavaScript must be enabled to correctly display this content

## Column References

Syntax

    column_reference ::= id ["." id]

Semantics

A column-reference expression returns the item stored in the specified column within the context row (the row that an SELECT expression is currently working on). Syntactically, a column-reference expression consists of one identifier, or 2 identifiers separated by a dot. If there are 2 ids, the first is considered to be a table alias and the second the name of a column in that table. We call this form a qualified column name. A single id is resolved to the name of a column in some of the tables referenced inside the FROM clause. However, in this case there must not be more than one tables that participate in the query and have a column with this name. We call this form an unqualified column name.

Example 6-91 Fetch the bag arrival date for a passenger from the airline baggage tracking application data

    SELECT fullname, bag.bagInfo[0].bagArrivalDate
    FROM BaggageInfo bag
    WHERE ticketNo=1762392135540

Explanation: In the `BaggageInfo` table, the `bagArrivalDate` field contains the date on which the checked baggage is delivered to the destination airport. To fetch the bag arrival date, you use a qualified column name in your query, that is, `bag.bagInfo[0].bagArrivalDate`. For more details, see Variable Declaration.

Here, the first reference bag is a table alias to the `BaggageInfo` table. The second reference `bagInfo[0]` is to the first element of the `bagInfo` JSON array column. The third reference is to the actual field `bagArrivalDate` that is nested inside the `bagInfo` JSON column.

You use only the first element of the `bagInfo` JSON array to avoid duplication of results for passengers carrying more than one bag.

Output:

    {"fullname":"Adelaide
        Willard","bagArrivalDate":"2019-02-15T21:21:00Z"}