GREL functions
GREL, the General Refine Expression Language, is the expression language used in Transform…, Add column based on this…, custom facets and templates. OxRefine runs it like OpenRefine 3.10.1: the same functions, controls, operators, errors and number formatting. 117 of OpenRefine's 118 functions and all 14 controls are available; not yet: wikibaseIssues.
The descriptions below are OpenRefine's own, from OpenRefine 3.10.1.
Variables
| Variable | Meaning |
|---|---|
value | The value of the current cell |
cell | The current cell: cell.value, and cell.recon for its reconciliation data |
cells | The row's cells by column name: cells["Name"].value |
row | The current row: row.index, row.starred, row.flagged, row.cells, row.record |
row.record | The record the row belongs to: index (the record's number), fromRowIndex, toRowIndex (the row after its last), rowCount, and cells["Name"].value, the record's non-blank values in that column as an array. Records are defined by the first column, as in records mode, and this works in rows mode too |
rowIndex | The row's position, from 0 |
Operators
+ - * / % for numbers (+ also joins text), == != < <= > >= for comparisons, and(…), or(…) and not(…) for logic, [index] and [from, to] for elements and slices of text and arrays, and . to call a function on a value: value.trim() is trim(value).
Controls
filter
filter(expression a, variable v, expression test) → array
Evaluates expression a to an array. Then for each array element, binds its value to variable name v, evaluates expression test which should return a boolean. If the boolean is true, pushes v onto the result array.
forEach
forEach(expression a, variable v, expression e) → array
Evaluates expression a to an array. Then for each array element, binds its value to variable name v, evaluates expression e, and pushes the result onto the result array.
forEachIndex
forEachIndex(expression a, variable i, variable v, expression e) → array
Evaluates expression a to an array. Then for each array element, binds its index to variable i and its value to variable name v, evaluates expression e, and pushes the result onto the result array.
forNonBlank
forNonBlank(expression o, variable v, expression eNonBlank, expression eBlank) → Depends on actual arguments
Evaluates expression o. If it is non-blank, binds its value to variable name v, evaluates expression eNonBlank and returns the result. Otherwise (if o evaluates to blank), evaluates expression eBlank and returns that result instead.
forRange
forRange(number from, number to, number step, variable v, expression e) → array
Iterates over the variable v starting at "from", incrementing by "step" each time while less than "to". At each iteration, evaluates expression e, and pushes the result onto the result array.
if
if(expression o, expression eTrue, expression eFalse) → Depends on actual arguments
Evaluates expression o. If it is true, evaluates expression eTrue and returns the result. Otherwise, evaluates expression eFalse and returns that result instead.
isBlank
isBlank(expression o) → boolean
Returns whether o is null or an empty string
isEmptyString
isEmptyString(expression o) → boolean
Returns whether o is an empty string
isError
isError(expression o) → boolean
Returns whether o is an error
isNonBlank
isNonBlank(expression o) → boolean
Returns whether o is not null and not an empty string
isNotNull
isNotNull(expression o) → boolean
Returns whether o is not null
isNull
isNull(expression o) → boolean
Returns whether o is null
isNumeric
isNumeric(expression o) → boolean
Returns whether o can represent a number
Not in OxRefine yet.
with
with(expression o, variable v, expression e) → Depends on actual arguments
Evaluates expression o and binds its value to variable name v. Then evaluates expression e and returns that result
Functions
abs
abs(number n) → number
Returns the absolute value of a number.
acos
acos(number n) → number
Returns the arc cosine of an angle, in the range 0 through PI.
and
and(boolean a, boolean b, ...) → boolean
Uses the logical operator AND on two or more booleans to output a boolean. Evaluates multiple statements into booleans, then returns true if all the statements are true. For example, (1 < 3).and(1 < 0) returns false because one condition is true and one is false.
asin
asin(number n) → number
Returns the arc sine of an angle in the range of -PI/2 through PI/2.
atan
atan(number n) → number
Returns the arc tangent of an angle in the range of -PI/2 through PI/2.
atan2
atan2(number n1, number n2) → number theta
Converts rectangular coordinates (n1, n2) to polar (r, theta). Returns number theta.
ceil
ceil(number n) → number
Returns the ceiling of a number.
chomp
chomp(string s, string sep) → string
Returns a copy of string s with string sep removed from the end if s ends with sep; otherwise, returns s.
coalesce
coalesce(o1, o2, ...) → object or null
Returns the first non-null from a series of objects (meaning any data type - string, date, number, boolean, etc.).
combin
combin(number n1, number n2) → number
Returns the ceiling of a number.
contains
contains(string s, string or pattern sub) → boolean
Returns a boolean indicating whether s contains sub, which is either a substring or a regex pattern. For example, "food".contains("oo") returns true.
cos
cos(number n) → number
Returns the trigonometric cosine of an angle.
cosh
cosh(number n) → number
Returns the hyperbolic cosine of a value.
cross
cross(cell or value, string projectName (optional), string columnName (optional)) → array
Looks up the given value in the target column of the target project, returns an array of matched rows. Two values match if and only if they have the same string representation. The first argument will be interpreted as cell.value if set to cell. The second argument will be interpreted as the current project name if omitted or set to "". The third argument will be interpreted as the index (starts from 0) column if omitted or set to "".
datePart
datePart(date d, string timeUnit) → date
Returns part of a date. The data type returned depends on the unit. See https://openrefine.org/docs/manual/grelfunctions/#datepartd-s-timeunit, https://openrefine.org/docs/manual/grelfunctions#date-functions for a table.
decode
decode(string s, string encoding) → string
Decodes a string using the specified encoding. Encodings include Base16, Base32Hex, Base32, Base64, and Base64Url.
degrees
degrees(number n) → number
Converts an angle from radians to degrees.
detectLanguage
detectLanguage(string s) → string
Detects the language of the given string and provides the language code.
In OxRefine: returns an error for now, because OxRefine has no language-identification model yet.
diff
diff(o1, o2, time unit (optional)) → string for strings, number for dates
For strings, takes two strings and compares them, returning a string. Returns the remainder of o2 starting with the first character where they differ. For dates, returns the difference in given time units. See the time unit table at https://openrefine.org/docs/manual/grelfunctions/#datepartd-s-timeunit.
encode
encode(string s, string encoding) → string
Encodes a string using the specified encoding. Encodings include Base16, Base32Hex, Base32, Base64, and Base64Url.
endsWith
endsWith(string s, string sub) → boolean
Returns a boolean indicating whether s ends with sub. For example, "food".endsWith("ood") returns true, whereas "food".endsWith("odd") returns false.
escape
escape(string s, string mode ['html','xml','csv','url','javascript','urlpath','urlform','urlfragment']) → string
Escapes s in the given escaping mode. The mode can be one of: 'html', 'xml', 'csv', 'url', 'javascript', 'urlpath', 'urlform', 'urlfragment'. Note that quotes are required around your mode.
even
even(number n) → number
Rounds the number up to the nearest even integer.
exp
exp(number n) → number
Returns e to the power of n.
facetCount
facetCount(choiceValue, string facetExpression, string columnName) → number
Returns the facet count corresponding to the given choice value, by looking for the facetExpression in the choiceValue in columnName.
fact
fact(number n) → number
Returns the factorial of a number, starting from 1.
factn
factn(number n1, number n2) → number
Returns the factorial of n1, starting from n2.
find
find(string s, substring or regex p) → array of strings
Outputs an array of all consecutive substrings inside string s that match the substring or regex pattern p. You can supply a substring by putting it in quotes. See also match().
fingerprint
fingerprint(string s) → string
Returns the fingerprint of s, a string that is the first step in fingerprint clustering methods: it will trim whitespaces, convert all characters to lowercase, remove punctuation, sort words alphabetically, etc.
floor
floor(number n) → number
Returns the floor of a number.
gcd
gcd(number n1, number n2) → number
Returns the greatest common denominator of two numbers.
get
get(o, number or string from, optional number to) → Depends on actual arguments
If o has named fields, returns the field named 'from' of o. If o is an array, returns a sub-array o[from, to]. if o is a string, returns o.substring(from, to).
hasField
hasField(o, string name) → boolean
Returns a boolean indicating whether o has a member field called name.
htmlAttr
htmlAttr(string s, element e) → string attribute value
Returns a string from an attribute on an XML or HTML element. Use it in conjunction with parseHtml() or parseXml() to point to an element first.
htmlText
htmlText(Element e) → String text
Returns a string of the text from within an HTML or XML element (including all child elements), removing tags and line breaks inside the string. Use it in conjunction with parseHtml() or parseXml() and select() to provide an element.
inArray
inArray(array a, string s) → boolean
Returns true if the array contains the desired string, and false otherwise. Will not convert data types.
inc
inc(date d, number n, string unit) → date
Returns a date changed by the given amount in the given unit of time, in quotes. See https://openrefine.org/docs/manual/grelfunctions/#incd-n-s-timeunit for a table. The default unit is 'hour'. A positive value increases the date, and a negative value moves it back in time.
indexOf
indexOf(string s, string sub) → number
Returns the first character index of sub as it first occurs in s; or, returns -1 if s does not contain sub. For example, "internationalization".indexOf("nation") returns 5.
innerHtml
innerHtml(element e) → string innerHtml
Returns the inner HTML of an HTML element. This will include text and children elements within the element selected. Use it in conjunction with parseHtml() and select() to provide an element.
innerXml
innerXml(element e) → string innerXml
Returns the inner XML elements of an XML element. Does not return the text directly inside your chosen XML element - only the contents of its children. Use it in conjunction with parseXml() and select() to provide an element.
join
join(array a, string sep) → string
Joins the items in the array with sep, and returns it all as a string.
jsonize
jsonize(o) → JSON literal value
Quotes a value as a JSON literal value.
lastIndexOf
lastIndexOf(string s, string sub) → number
Returns the first character index of sub as it last occurs in s; or, returns -1 if s does not contain sub. For example, "parallel".lastIndexOf("a") returns 3 (pointing at the second 'a').
lcm
lcm(number n1, number n2) → number
Returns the least common multiple of two numbers.
length
length(array a or string s) → number
Returns the length of string s as a number, or the size of array a, meaning the number of objects inside it. Arrays can be empty, in which case length() will return 0.
levenshteinDistance
levenshteinDistance(string s1, string s2) → number
Calculate the number of edits required to make one value match another.
ln
ln(number n) → number
Returns the natural logarithm of n.
log
log(number n) → number
Returns the base 10 logarithm of n.
match
match(string s, regex p) → array of strings
Attempts to match the string s in its entirety against the regex pattern p and, if the pattern is found, outputs an array of all capturing groups (found in order). See also find().
max
max(number n1, number n2) → number
Returns the greater of two numbers.
md5
md5(string s) → string
Returns the MD5 hash of an object. If fed something other than a string (array, number, date, etc.), md5() will convert it to a string and deliver the hash of the string.
min
min(number n1, number n2) → number
Returns the smaller of two numbers.
mod
mod(number n1, number n2) → number
Returns n1 modulus n2.
multinomial
multinomial(number n1, number n2, ...) → number
Calculates the multinomial of one number or a series of numbers.
ngram
ngram(string s, number n) → array of strings
Returns an array of the word n-grams of s. That is, it lists all the possible consecutive combinations of n words in the string.
ngramFingerprint
ngramFingerprint(string s, number n) → string
Returns the n-gram fingerprint of s.
normalize
normalize(string s) → string
Returns a normalized version of the input string by removing diacritics and normalizing extended western characters to their ASCII representation.
not
not(boolean b) → boolean
Uses the logical operator NOT on a boolean to output a boolean. For example, not(1 > 7) returns true because 1 > 7 itself is false.
now
now() → date
Returns the current time according to your system clock, in the ISO 8601 extended format (converted to UTC). For example, 10:53am (and 00 seconds) on November 26th 2020 in EST returns [date 2020-11-26T15:53:00Z].
odd
odd(number n1) → number
Rounds the number up to the nearest odd integer.
or
or(boolean a, boolean b, ...) → boolean
Uses the logical operator OR on two or more booleans to output a boolean. For example, (1 < 3).or(1 > 7) returns true because at least one of the conditions (the first one) is true.
ownText
ownText(element e) → string ownText
Returns the text directly inside the selected XML or HTML element only, ignoring text inside children elements (for this, use innerXml()). Use it in conjunction with a parser and select() to provide an element.
parent
parent(string s, element e) → HTML/XML Element
Returns the parent node or null if no parent. Use it in conjunction with parseHtml() and select() to provide an element.
parseHtml
parseHtml(string s) → HTML object
Given a cell full of HTML-formatted text, parseHtml() simplifies HTML tags (such as by removing ' /' at the end of self-closing tags), closes any unclosed tags, and inserts linebreaks and indents for cleaner code. A cell cannot store the output of parseHtml() unless you convert it with toString(): for example, value.parseHtml().toString().
parseJson
parseJson(string s) → JSON object
Parses a string as JSON.
parseUri
parseUri(string url) → JSON object
Parses a URI and extracts its components.
parseXml
parseXml(string s) → XML object
Given a cell full of XML-formatted text, parseXml() returns a full XML document and adds any missing closing tags.
partition
partition(string s, string or regex fragment, optional boolean omitFragment) → array
Returns an array of strings [ a, fragment, z ] where a is the substring within s before the first occurrence of fragment, and z is the substring after fragment. Fragment can be a string or a regex. If omitFragment is true, frag is not returned.
phonetic
phonetic(string s, string encoding (optional, defaults to 'metaphone3')) → string
Returns a phonetic encoding of a string, based on an available phonetic algorithm. Can be one of the following supported phonetic methods: metaphone, doublemetaphone, metaphone3, soundex, cologne. Defaults to 'metaphone3'.
pow
pow(number n1, number n2) → number
Returns n1 raised to the power of n2.
quotient
quotient(number numerator, number denominator) → number
Returns the integer portion of a division (truncated, not rounded), when supplied with a numerator and denominator.
radians
radians(number n) → number
Converts an angle in degrees to radians.
random
random(number lowerBound, number upperBound) → number
Returns a random integer in the interval between the lower and upper bounds (inclusively). Will output a different random number in each cell in a column.
randomNumber
randomNumber(number lowerBound, number upperBound) → number
Returns a random integer in the interval between the lower and upper bounds (inclusively). Will output a different random number in each cell in a column.
range
range(A single string 'a', 'a, b' or 'a, b, c' or one, two or three integers a or a, b or a, b, c) → array
Returns an array where a and b are the start and the end of the range respectively and c is the step (increment).
reinterpret
reinterpret(string s, string target encoding, string source encoding) → string
Returns s reinterpreted through the given character encoders. You must supply one of the supported encodings for each of the original source and the target output: https://docs.oracle.com/javase/1.5.0/docs/guide/intl/encoding.doc.html. Note that quotes are required around character encoders.
replace
replace(string s, string or regex find, string replace) → string
Returns the string obtained by replacing the find string with the replace string in the inputted string. For example, 'The cow jumps over the moon and moos'.replace('oo', 'ee') returns the string 'The cow jumps over the meen and mees'. Find can be a regex pattern. For example, 'The cow jumps over the moon and moos'.replace(/\s+/, "_") will return 'The_cow_jumps_over_the_moon_and_moos'
replaceChars
replaceChars(string s, string find, string replace) → string
Returns the string obtained by replacing a character in s, identified by find, with the corresponding character identified in replace. You cannot use this to replace a single character with more than one character.
replaceEach
replaceEach(string, array of strings to replace, array of replacement strings) → string
Replace each occurrence of a substring in a string with another substring.
reverse
reverse(array a) → array
Reverses array a.
round
round(number n) → number
Rounds a number to the nearest integer.
rpartition
rpartition(string s, string or regex fragment, optional boolean omitFragment) → array
Returns an array of strings [ a, fragment, z ] where a is the substring within s before the last occurrence of fragment, and z is the substring after the last instance of fragment. If omitFragment is true, frag is not returned.
scriptText
scriptText(element e) → string
Returns the combined data of an HTML/XML Element. Data is e.g. the inside of a <script> tag. Note that data is NOT the text of the element. Use htmlText() to get the text that would be visible to a user, and scriptText() for the contents of <script>, <style>, etc. Use scriptText() in conjunction with parseHtml() and select().
select
select(element e, string s) → HTML Elements
Returns an array of all the desired elements from an HTML or XML document, if the element exists. Elements are identified using the Jsoup selector syntax: https://jsoup.org/apidocs/org/jsoup/select/Selector.html.
sha1
sha1(string s) → string
Returns the SHA-1 hash of an object. If fed something other than a string (array, number, date, etc.), sha1() will convert it to a string and deliver the hash of the string.
sin
sin(number n) → number
Returns the trigonometric sine of an angle.
sinh
sinh(number n) → number
Returns the hyperbolic sine of an angle.
slice
slice(string s or array a, number from, number to (optional)) → string, array, or array item (number, string, etc.)
Given a string, returns the substring starting from character index from, and up to character index to. If the to argument is omitted, will output to the end of s. Remember character indices start from zero. Given an array, returns a sub-array from the first index provided up to and including the last index provided. If the to value is omitted, it is understood to be the end of the array. Slice only.
smartSplit
smartSplit(string s, optional string sep) → array
Returns the array of strings obtained by splitting s by sep, or by guessing either tab or comma separation if there is no sep given. Handles quotes properly and understands cancelled characters. The separator can be either a string or a regex pattern.
sort
sort(array a of uniform type) → array
Sorts the array in ascending order. Sorting is case-sensitive, uppercase first and lowercase second.
split
split(string s, string or regex sep, optional boolean preserveTokens) → array
Returns the array of strings obtained by splitting s by sep. The separator can be either a string or a regex pattern. If preserveTokens is true, empty segments are preserved.
splitByCharType
splitByCharType(string s) → array
Returns an array of strings obtained by splitting s into groups of consecutive characters each time the characters change Unicode categories.
splitByLengths
splitByLengths(string s, number n1, number n2, ...) → array
Returns the array of strings obtained by splitting s into substrings with the given lengths. For example, "internationalization".splitByLengths(5, 6, 3) returns an array of 3 strings: [ "inter", "nation", "ali" ]. Excess characters are discarded from the output array.
startsWith
startsWith(string s, string sub) → boolean
Returns a boolean indicating whether s starts with sub. For example, "food".startsWith("foo") returns true, whereas "food".startsWith("bar") returns false.
strip
strip(string s) → string
Returns a copy of string s with leading and trailing whitespace removed.
substring
substring(string s or array a, number from, number to (optional)) → string, array, or array item (number, string, etc.)
Given a string, returns the substring starting from character index from, and up to character index to. If the to argument is omitted, will output to the end of s. Remember character indices start from zero. Given an array, returns a sub-array from the first index provided up to and including the last index provided. If the to value is omitted, it is understood to be the end of the array. Slice only.
sum
sum(array a) → number
Return the sum of the numbers in the array. Ignores non-number items. Returns 0 if the array does not contain numbers.
tan
tan(number n) → number
Returns the trigonometric tangent of an angle.
tanh
tanh(number n) → number
Returns the hyperbolic tangent of an angle.
timeSinceUnixEpochToDate
timeSinceUnixEpochToDate(number n, string unit (optional, defaults to 'seconds')) → date(OffsetDateTime)
Returns a number converted to a date based on Unix Epoch Time. The number can be Unix Epoch Time in one of the following supported units: second, millisecond, microsecond. Defaults to 'second'.
toDate
toDate(o, boolean monthFirst, string format1, string format2, ...)) → date
Returns the inputted object converted to a date object. Without arguments, it returns the ISO 8601 extended format. With arguments, you can control the output format. With monthFirst: set false if the date is formatted with the day before the month. With formatN: attempt to parse the date using an ordered list of possible formats. Supply formats based on the SimpleDateFormat syntax: SimpleDateFormat.
toLowercase
toLowercase(string s) → string
Returns string s converted to all lowercase characters.
toNumber
toNumber(o) → number
Returns a string converted to a number. Will attempt to convert other formats into a string, then into a number. If the value is already a number, it will return the number.
toString
toString(object o, string format (optional)) → string
Takes any value type (string, number, date, boolean, error, null) and gives a string version of that value. You can convert numbers to strings with rounding, using an optional string format. See https://docs.oracle.com/javase/8/docs/api/java/util/Formatter.html. You can also convert dates to strings using date parsing syntax. See https://openrefine.org/docs/manual/grelfunctions/#date-functions.
toTitlecase
toTitlecase(string s, string delimiters (optional)) → string
Returns string s converted into titlecase: a capital letter starting each word, and the rest of the letters lowercase. For example, 'Once upon a midnight DREARY'.toTitlecase() returns the string 'Once Upon A Midnight Dreary'. Optionally, a string of delimiter characters used to separate words. The first character and the first non-delimiter character after a delimiter will be captialized. For example, "jean-michel jarre".toTitlecase(" -") returns "Jean-Michel Jarre".
toUppercase
toUppercase(string s) → string
Returns string s converted to all uppercase characters.
trim
trim(string s) → string
Returns a copy of string s with leading and trailing whitespace removed.
type
type(object o) → string
Returns a string with the data type of o, such as undefined, string, number, boolean, etc.
unescape
unescape(string s, string mode ['html','xml','csv','url','javascript']) → string
Unescapes s in the given escaping mode. The mode can be one of: 'html', 'xml', 'csv', 'url', 'javascript'. Note that quotes are required around your mode.
unicode
unicode(string s) → string
Returns an array of strings describing each character of s in their full Unicode notation.
unicodeType
unicodeType(string s) → string
Returns an array of strings describing each character of s by their Unicode type.
uniques
uniques(array a) → array
Returns the array with duplicates removed. Case-sensitive.
wholeText
wholeText(element e) → string
Selects the (unencoded) text of an element and its children, including any new lines and spaces, and returns a string of unencoded, un-normalized text. Use it in conjunction with parseHtml() and select() to provide an element.
wikibaseIssues
wikibaseIssues() → returns the list of Wikibase issue aggregation identifiers for the edits generated by the current row
function taking no arguments and computing the Wikibase quality issues for the current row
Not in OxRefine yet.
xmlAttr
xmlAttr(string s, element e) → string attribute value
Returns a string from an attribute on an XML or HTML element. Use it in conjunction with parseHtml() or parseXml() to point to an element first.
xmlText
xmlText(Element e) → String text
Returns a string of the text from within an HTML or XML element (including all child elements), removing tags and line breaks inside the string. Use it in conjunction with parseHtml() or parseXml() and select() to provide an element.
xor
xor(boolean a, boolean b, ...) → boolean
Uses the logical operator XOR (exclusive-or) on two or more booleans to output a boolean. Evaluates multiple statements, then returns true if only one of them is true. For example, (1 < 3).xor(1 < 7) returns false because more than one of the conditions is true.
zip
zip(array a, array b, ...) → array of arrays
Combines two or more arrays into a single array of arrays, where the elements at the same index from the input arrays are grouped together to form a sub-array in the output array.