Regular Expression Conditions

A regular expression is a pattern that the regular expression engine attempts to match with an input string. The regex_like function performs regular expression matching. The regex_like function provides functionality similar to the LIKE operator in standard SQL, that is, it can be used to check if an input string matches a given pattern. The input string and the pattern are computed by the first and second arguments, respectively. A third, optional, argument specifies a set of flags that affect how the matching is done.

The pattern string is the regular expression against which the input text is matched. The period (.) is a meta-character that matches every character except a new line. The greedy quantifier (*) is a meta-character that indicates zero or more occurrences of the preceding element. For example, the regex "D.*" matches any string that starts with the character 'D' and is followed by zero or more characters.

Example 1: Fetch baggage information of passengers whose names start with 'Z'.

SELECT bag.fullname,bag.bagInfo[].tagNum 
FROM BaggageInfo bag
WHERE regex_like(fullName, "Z.*")

Explanation: You fetch the full name and tag numbers of passengers whose full name starts with Z. You use a regular expression and specify that the first character in the full name should be "Z" and the rest can be anything else.

Output:

{"fullname":"Zina Christenson","tagNum":"17657806228676"}
{"fullname":"Zulema Martindale","tagNum":"17657806288937"}

Example 2: Fetch baggage information of passengers whose flight source location has an "M" in it.

Option 1:

SELECT bag.fullname,bag.bagInfo[].tagNum, 
bag.bagInfo[].flightLegs[0].fltRouteSrc 
FROM BaggageInfo bag 
WHERE regex_like(bag.bagInfo.flightLegs[0].fltRouteSrc, ".*M.*")

Explanation: The assumption here is that the first record of the flightLegs array has the details of the source location. You fetch the full name and tag numbers of passengers whose flight source has an "M" in it. You use a regular expression and specify that one of the characters in the source field should be "M" and the rest can be anything else.

You can also use different approaches to write queries to solve the above problem.

Option 2: Instead of hard coding the index of the flightLegs array, you use the regex_like function to determine the correct index.

SELECT bag.fullname,bag.bagInfo[].tagNum, 
bag.bagInfo[].flightLegs[].fltRouteSrc 
FROM BaggageInfo bag
WHERE EXISTS (bag.bagInfo.flightLegs[regex_like($element.fltRouteSrc, ".*M.*")])

Option 3: You use the substring of the "routing" field to extract the source and then use regex_like function to search the letter M in the source.

SELECT bag.fullname,bag.bagInfo[].tagNum,
substring(bag.bagInfo[].routing,0,3) 
FROM BaggageInfo bag WHERE
regex_like(substring(bag.bagInfo[].routing,0,3), ".*M.*")

Output:

{"fullname":"Raymond Griffin","tagNum":"17657806243578","fltRouteSrc":"MSQ"}
{"fullname":"Elane Lemons","tagNum":"1765780623244","fltRouteSrc":"MXP"}
{"fullname":"Zina Christenson","tagNum":"17657806228676","fltRouteSrc":"MIA"}
{"fullname":"Zulema Martindale","tagNum":"17657806288937","fltRouteSrc":"MIA"}
{"fullname":"Adam Phillips","tagNum":"17657806255240","fltRouteSrc":"MIA"}
{"fullname":"Joanne Diaz","tagNum":"17657806292518","fltRouteSrc":"MIA"}
{"fullname":"Teena Colley","tagNum":"17657806255823","fltRouteSrc":"MSQ"}
{"fullname":"Omar Harvey","tagNum":"17657806234185","fltRouteSrc":"MEL"}
{"fullname":"Fallon Clements","tagNum":"17657806255507","fltRouteSrc":"MXP"}