Extract Expressions

The EXTRACT expression extracts a component from a timestamp.

extract_expression ::= EXTRACT "(" id FROM expression ")"

The expression after the FROM keyword must return at most one timestamp or NULL. If the result of this expression is NULL or empty, the result of EXTRACT is also NULL or empty, respectively. Otherwise, the component specified by the id is returned. This id must be one of the following keywords: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND, WEEK, ISOWEEK.

Example 1: What is the full name and baggage arrival year for the customer with ticket number 1762383911861.

SELECT fullName, 
EXTRACT (YEAR FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0)))
AS YEAR FROM BaggageInfo bag 
WHERE ticketNo=1762383911861

Explanation: You first use CAST to convert the bagArrivalDate to a TIMESTAMP and then fetch the YEAR component from the Timestamp.

Output:

{"fullName":"Joanne Diaz","YEAR":2019}

Example 2: Retrieve all bags that travelled through MIA between 10:00 am and 10:00 pm in February 2019.

SELECT bag.bagInfo[].tagNum,bag.bagInfo[].flightLegs[].fltRouteSrc,
$t1 AS HOUR FROM BaggageInfo bag,
EXTRACT(HOUR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t1,
EXTRACT(YEAR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t2,
EXTRACT(MONTH FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t3
WHERE bag.bagInfo[].flightLegs[].fltRouteSrc=any "MIA" AND
$t2=2019 AND $t3=02 AND ($t1>10 AND $t1<20)

Explanation: You want to know the details of flights that traveled through MIA between 10:00 am and 10:00 pm in February 2019. You use a number of filter conditions here. First, the flight should have originated or traversed through MIA. The year of arrival should be 2019 and the month of arrival should be 2 (February). Then you filter if the hour of arrival is between 10:00 am and 10:00 pm (20 hours).

Output:

{"tagNum":"17657806255240","fltRouteSrc":["MIA","LAX"],"HOUR":16}
{"tagNum":"17657806292518","fltRouteSrc":["MIA","LAX"],"HOUR":16}

Example 3: Which year and month did the passenger with the reservation code PQ1M8N receive the baggage?

SELECT fullName, 
EXTRACT(YEAR FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0))) AS YEAR, 
EXTRACT(MONTH FROM CAST (bag.bagInfo.bagArrivalDate AS Timestamp(0))) AS MONTH 
FROM BaggageInfo bag WHERE bag.confNo="PQ1M8N"

Explanation: You first use CAST to convert the bagArrivalDate to a TIMESTAMP and then fetch the YEAR component and MONTH component from the Timestamp.

Output:

{"fullName":"Kendal Biddle","YEAR":2019,"MONTH":3}

Example 4: Group the baggage data based on the month of arrival and display the month and the number of baggage that arrived that month.

SELECT EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0))) AS MONTH,
count(EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0)))) AS COUNT
FROM BaggageInfo $bag, $bag.bagInfo[].bagArrivalDate $bag_arr_date 
GROUP BY EXTRACT(MONTH FROM CAST ($bag_arr_date AS Timestamp(0)))

Explanation: You want to group the data based on the month of the arrival of baggage. You use an unnest array to flatten the data. The bagInfo array is flattened and the value of bag arrival date is fetched from the array. You then use CAST to convert the bagArrivalDate to a TIMESTAMP and then fetch the YEAR component and MONTH component from the Timestamp. You then use the count function to get the total baggage corresponding to every month.

Note:

One assumption in the data is that all the baggage has arrived in the same year. So you group the data only based on the month.

Output:

{"MONTH":2,"COUNT":11}
{"MONTH":3,"COUNT":10}