Substituting column names in a query

You can use a different name for a column during a SELECT statement. Substituting a name in a query does not change the column name, but uses the substitute in the data returned.

Example: The following query returns the phone number as CONTACTAT in the result.

SELECT contactPhone AS CONTACTAT FROM BaggageInfo

Explanation: Here you want to fetch the contact phone of the passengers and display it as CONTACTAT.

Output:

{"CONTACTAT":"960-428-3843"}
{"CONTACTAT":"368-769-5636"}
{"CONTACTAT":"364-610-4444"}
{"CONTACTAT":"395-837-3772"}
{"CONTACTAT":"893-324-1064"}
{"CONTACTAT":"289-564-3497"}
{"CONTACTAT":"334-679-5105"}
{"CONTACTAT":"978-191-8550"}
{"CONTACTAT":"849-731-1334"}
{"CONTACTAT":"796-709-9501"}
{"CONTACTAT":"539-097-5220"}
{"CONTACTAT":"575-781-6240"}
{"CONTACTAT":"131-183-0560"}
{"CONTACTAT":"619-956-8760"}
{"CONTACTAT":"364-610-4444"}
{"CONTACTAT":"421-272-8082"}
{"CONTACTAT":"567-710-9972"}
{"CONTACTAT":"600-918-8404"}
{"CONTACTAT":"987-210-3029"}
{"CONTACTAT":"666-302-0028"}
{"CONTACTAT":"165-742-5715"}

You can combine columns using the concatenation operator "||" as shown below.

Example: For all customers, fetch the last place where the bag was seen and the time when it was seen.

Approach 1: Use the concatenation operator and fetch column names and static text as output of the SELECT command.

SELECT "The bag was last seen at " || 
bag.bagInfo[].lastSeenStation || " on " ||
bag.bagInfo[].bagArrivalDate AS Bag_Details FROM BaggageInfo bag

Output:

{"Bag_Details":"The bag was last seen at BZN on 2019-03-15T10:13:00Z"}
{"Bag_Details":"The bag was last seen at MEL on 2019-02-04T10:08:00Z"}
{"Bag_Details":"The bag was last seen at MEL on 2019-02-25T20:15:00Z"}
{"Bag_Details":"The bag was last seen at MAD on 2019-03-07T13:51:00Z"}
{"Bag_Details":"The bag was last seen at FRA on 2019-03-02T13:18:00Z"}
{"Bag_Details":"The bag was last seen at VIE on 2019-02-12T07:04:00Z"}
{"Bag_Details":"The bag was last seen at JTRJTR on 2019-03-12T15:05:00Z2019-03-12T16:25:00Z"}
{"Bag_Details":"The bag was last seen at JTR on 2019-03-07T16:01:00Z"}
{"Bag_Details":"The bag was last seen at MEL on 2019-02-01T16:13:00Z"}
{"Bag_Details":"The bag was last seen at MXP on 2019-03-22T10:17:00Z"}
{"Bag_Details":"The bag was last seen at MEL on 2019-02-16T16:13:00Z"}
{"Bag_Details":"The bag was last seen at MIA on 2019-03-02T16:09:00Z"}
{"Bag_Details":"The bag was last seen at BZN on 2019-02-21T14:08:00Z"}
{"Bag_Details":"The bag was last seen at SGN on 2019-02-10T10:01:00Z"}
{"Bag_Details":"The bag was last seen at JTR on 2019-02-02T23:59:00Z"}
{"Bag_Details":"The bag was last seen at BLR on 2019-03-14T06:22:00Z"}
{"Bag_Details":"The bag was last seen at VIE on 2019-03-05T12:00:00Z"}
{"Bag_Details":"The bag was last seen at JTR on 2019-03-12T15:05:00Z"}
{"Bag_Details":"The bag was last seen at SEA on 2019-02-15T21:21:00Z"}
{"Bag_Details":"The bag was last seen at HKG on 2019-02-03T08:09:00Z"}
{"Bag_Details":"The bag was last seen at HKG on 2019-02-13T11:15:00Z"}

The result is cluttered if there is more than one bag per customer/reservation number as shown above.

Approach 2: You can overcome this issue by printing as the value of elements of the bagInfo array as shown below.

SELECT "The bag was last seen at " || [bag.bagInfo[].lastSeenStation] || " on " ||
[bag.bagInfo[].bagArrivalDate] AS Bag_Details FROM BaggageInfo bag

Note:

Column names and static text can also be concatenated using the "||" operator.

Explanation: You are concatenating a part of the document in the bagInfo JSON with various static text and displaying it as elements of an array.

Output:

{"Bag_Details":"The bag was last seen at [\"MIA\"] on [\"2019-03-02T16:09:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"BZN\"] on [\"2019-02-21T14:08:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"SGN\"] on [\"2019-02-10T10:01:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"HKG\"] on [\"2019-02-13T11:15:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"JTR\"] on [\"2019-02-02T23:59:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"BLR\"] on [\"2019-03-14T06:22:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"VIE\"] on [\"2019-03-05T12:00:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"JTR\"] on [\"2019-03-12T15:05:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"SEA\"] on [\"2019-02-15T21:21:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"HKG\"] on [\"2019-02-03T08:09:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"BZN\"] on [\"2019-03-15T10:13:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MEL\"] on [\"2019-02-04T10:08:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MEL\"] on [\"2019-02-25T20:15:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MAD\"] on [\"2019-03-07T13:51:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"FRA\"] on [\"2019-03-02T13:18:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"VIE\"] on [\"2019-02-12T07:04:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"JTR\",\"JTR\"] on [\"2019-03-12T15:05:00Z\",
\"2019-03-12T16:25:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"JTR\"] on [\"2019-03-07T16:01:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MEL\"] on [\"2019-02-01T16:13:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MXP\"] on [\"2019-03-22T10:17:00Z\"]"}
{"Bag_Details":"The bag was last seen at [\"MEL\"] on [\"2019-02-16T16:13:00Z\"]"}