Skip to main content

Scalar functions

The ability to format and/or perform calculations on row-level columns can be accomplished through Scalar Functions. These scalar functions enhance the versatility and convenience of J1QL by providing powerful tools for transforming and combining data in queries.

CONCAT​

The scalar function CONCAT() empowers users to concatenate or join one or more values into a single string. Currently, CONCAT can be used in the RETURN to the clause of your function, will future development is planned for use in the WHERE clause.

If this function receives a number or boolean value, the CONCAT intuitively converts these values to strings. Additionally, if CONCAT processes an empty selector field, it evaluates that field as an empty string.

CONCAT supports the following parameters, separated by commas:

  • Selector Fields: selector.field
  • String values: 'your string' or "your string"
  • Number values: 123
  • Boolean values: true
Example using CONCAT
FIND
  aws_s3_bucket as s3
RETURN
  CONCAT(s3.bucketSizeBytes, ' bytes') as size

MERGE​

The scalar function MERGE() allows you to merge multiple properties into a single property list. You can now combine multiple (defined) properties into a single property without having to choose to return one property or another.

Example using MERGE
FIND UNIQUE User AS u
THAT IS Person AS p
RETURN p.displayName, merge(p.email, u.email, u.publicEmail) AS "Email List"

DATETIME​

The scalar function DATETIME() allows you to format timestamp values into human-readable date and time strings using custom format patterns. This function requires two arguments: a timestamp field and a formatting string.

The DATETIME function accepts:

  • Timestamp field: A property containing an epoch timestamp in milliseconds
  • Format string: A string specifying the desired date/time format pattern (required)
Example using DATETIME
FIND DataStore AS ds
RETURN
  ds.displayName,
  DATETIME(ds._createdOn, "yyyy-MM-dd HH:mm:ss.SSSSZ") AS "Created Date",
  DATETIME(ds._beginOn, "hh:mm") AS "Time Modified"

You can also combine DATETIME with other scalar functions:

Combining DATETIME with CONCAT
FIND * AS s
RETURN CONCAT("Created at ", DATETIME(s._createdOn, "hh:mm")) AS "Creation Time"
LIMIT 10

For more information about the format string patterns, see the Java DateTimeFormatter documentation.

Contents