Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

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

VariableMeaning
valueThe value of the current cell
cellThe current cell: cell.value, and cell.recon for its reconciliation data
cellsThe row's cells by column name: cells["Name"].value
rowThe current row: row.index, row.starred, row.flagged, row.cells, row.record
row.recordThe 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
rowIndexThe 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.