About Formulas

Formulas allow you to use Ruby code to generate data based on custom logic. For example:

times_reached_base / at_bats + slugging

Operators

+ - * / %

Logic

< > <= >= == != and or

Conditional Statements

if my_num % 2 == 0 then 'even' else 'odd' end

if score > 10 then "high" elsif score > 5 then "medium" else "low" end

Functions

base64(str) => encodes a string as base64

bcrypt('some password') => Returns a bcrypt hash of the provided string.

code("CURRENT_TIMESTAMP") => returns a value that is will not be wrapped in quotes in the downloaded file. Use this to inject code into your data.

concat(first_name, " ", last_name) => concatenates all arguments into a single string.

date('7/4/2015') => July 4, 2015. You can optionally pass a format string as a second argument. The default format is "%m/%d/%Y". See Ruby Date.strptime for more info on formats.

date_diff('days', my_date, date('2015-01-01')) => The numbers of days between my_date and Jan 1, 2015. 'days', 'hours', 'minutes', and 'seconds' are also supported.

day(my_date_field) => The day of my_date_field as an integer. Optionally specify true as a second argument to return the day as string in 2 digit format.

digest(str, 'MD5|HMAC|RMD160|SHA1|SHA256|SHA384|SHA512', 'hex|base64') => digest of str with specific algorithm and encoding

epoch(datetime) => Converts a datetime field to an epoch.

field("My Field Name") => gets the value of a field. Use this to access fields that start with an uppercase letter or contain non-alphanumeric characters.

from_dataset("dataset", "column", join_criteria) => Fetches a value from a dataset.
For example, from_dataset("People", "name", id: person_id) returns the value of the "name" column from the "People" dataset where the id column matches the person_id column in the current schema.

generate(data_type, **options) => generates a value using one of Mockaroo's built-in datatypes. See Types for a list of available data types and their parameters. For example, generate("First Name") generates a random first name. generate('Number', min: 0, max: 100, decimals: 0) generates a random integer between 0 and 100.

hex(str) => encodes a string as hex

lower("XYZ") => xyz

mongo_object_id(my_id) => Output the value of my_id in MongoDB ObjectId JSON format

month(my_date_field) => The month of my_date_field as an integer. Optionally specify true as a second argument to return the month as a string in 2 digit format.

my_date + years(2) => the date 2 years after my_date. months, days, hours, minutes, and seconds are also available.

naughty(my_field, 10) => returns a naughty string 10% of the time and the value of my_field 90% of the time. Use with hidden fields (name starting with "__") to turn nice fields into naughty fields!

now() => The current date and time.

normal_dist(mean, std_dev, decimals) => Generates a random number using a normal disribution. Mean and std_dev are required. Decimals is optional and defaults to 2.

pad(field, length, fill_string, "left|right|center") => pads a string to a fixed length with filler.

format(number_value, decimals) => formats a number as a string with a fixed number of decimal places. For example: format(1.2, 3) => "1.200"

random(min, max) => generates a random number between min and max.

round(x / y, 2) => rounds to 2 decimal places

time(my_datetime_field => Returns only the time part of a datetime field.

upper("xyz") => XYZ

uuid_v5("dns", "www.mockaroo.com") => generates a v5 UUID
The first parameters should be "dns", "url", "oid", or "x500". The second parameter is any string

year(my_date_field) => The year of my_date_field as an integer

Accessing Request Parameters when Used in a Mock API

request_params['name_of_parameter'] => returns the value of a URL or query string parameter specified in the request

request_entity['name_of_parameter'] => returns the value of a key in the JSON request entity body

Handling Blanks

my_num + 1 if my_num => returns my_num + 1, or blank if my_num is blank

a + b if a and b => returns a + b, or blank if either is blank

(my_num || 0) + 1 => returns my_num + 1, or 1 if my_num is blank

if my_field.nil? then 'blank' else 'not blank' end => "blank" when my_field is blank, otherwise "not blank"

Accessing Nested JSON Objects

When a formula field is inside of a JSON array it has access to all fields in that array as well as all fields on parent objects. Both fields in the array and parent fields are accessed without a qualifier. For example, if a formula is in an array named "myArray" that has nested field named "myArray.myField", the formula can access that field as "myField" NOT "myArray.myField". When formulas need to access fields in nested objects (not in an array), however, the fully qualified field name must be used.

Generating Data using Regular Expressions

You can use Ruby's Regexp.gen method to generate data based on regular expressions. For example:/(A|B|C)-\d3/.gen

Using built-in Ruby classes and methods

For security purposes, not all of the Ruby language is available in Mockaroo Formulas. All methods of the following modules are whitelisted whitelisted:

  • Array
  • BasicObject
  • BigDecimal
  • BSON::ObjectId
  • Date
  • DateAndTime::Calculations
  • DateTime
  • Enumerable
  • Fixnum
  • Float
  • Hash
  • Integer
  • Math
  • NilClass
  • Range
  • Regexp
  • String
  • Time
Additionally you can also declare and call lambdas. Formulas use Ruby 2.7.3.