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-90 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"}