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 1 - 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 2 - 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"]"}