Data Mapping

The Utilihive SDK includes a mapping library based on the JSONiq query language and initially based on a subset fork of the open source RumbleDB project. It is used in conjunction with the map processor to transform payloads in a flow as JSON documents, where the JSON document is made up of objects, arrays, strings, numbers, booleans, and null values. JSONiq borrows heavily from the XQuery query language, so you can equally refer to the XQuery specification for help as you can the JSONiq spec.

Basic Usage

Most features native to JSONiq are also available in the data mapper. Keep in mind, however, that the $ character in standard JSONiq is used to denote variables. When you write a data mapping as a Kotlin string (i.e., when using the Lowcode DSL), you would need to use ${'$'} syntax to insert an actual $ character. Thus, it is recommended to use the mapper’s alias character, #, for JSONiq variables.

For example, the payload for the map processor in a flow is available in the data mapping as #input.payload. Because a JSON document is, itself, a valid JSONiq expression, you can map an object payload in the following manner:

{
  "firstName" : #input.payload.name
}

{
  "name" : "Anne"
}

Select the "Try It" button to see how this mapping works with real payload values!

In an actual flow, this mapping is applied to the map processor in the following manner:

map {
    id = "map-name-from-payload"
    mapSpec = """
        {
          "firstName" : #input.payload.name
        }
    """.trimIndent()
}

Variables

Global variables in JSONiq are created with the syntax declare variable #variableName := <value>;. Such variables must be declared at the beginning of the mapping and end with a semicolon, as they are technically not part of the JSONiq query but rather the prolog that optionally sets up the query environment. In the following example, the query is still a single JSON document that becomes the processor’s output:

declare variable #firstName := #input.payload.name;

{
  "firstName" : #firstName
}

{
  "name" : "Anne"
}

Other variables can be created at any point in the mapping with let clauses. The syntax follows the pattern let #variableName := <value>.

Once you introduce let clauses to your mapping, however, it is no longer a single JSON document. As such, you will need to explicitly return the value that you want to become the processor’s output. For example:

let #firstName := #input.payload.name

return {
  "firstName" : #firstName
}

{
  "name" : "Anne"
}

Conditionals

Values can be assigned via conditional expressions, as the following example shows:

let #firstName := #input.payload.name
let #company := #input.payload.company

return {
  "firstName" : #firstName,
  "company" : if (#company != null) then #company else "{company-name}"
}

{
  "name" : "Anne"
}

Data mappings also support switch expressions. For example:

let #firstName := #input.payload.name
let #locationId := #input.payload.locationId

return {
  "firstName" : #firstName,
  "location" : switch(#locationId)
      case 1 return "Norway"
      case 2 return "USA"
      default return "Spain"
}

{
  "name" : "Anne",
  "locationId" : 1
}

Functions

JSONiq provides many built-in functions, such as the concat() function that can be used in the following manner:

let #person := #input.payload

return {
  "fullName" : concat(#person.firstName, " ", #person.lastName)
}

{
  "firstName" : "Anne",
  "lastName" : "Smith"
}

For a list of supported functions, see the map processor documentation.

You can also define your own custom functions in the mapping, for example a function to wrap the concat() built-in function with a hardcoded phone prefix:

either as a named function using the declare function keyword:

declare function formatPhone(#phone) {
  concat("+47", #phone)
};

or as an anonymous function using variable declarations:

declare variable #formatPhone := function(#phone) {
  concat("+47", #phone)
};

Recursive functions must be defined as named functions to be available in the function body, for example the following factorial function:

declare function factorial ($i as integer) as integer {
    if(($i = 0 or $i = 1))
        then 1
    else
        $i * factorial ($i - 1)
};

It means that recursive functions can be declared only in the prolog. Anonymous functions, on the other hand, can be also declared with let clauses in the main query as shown in the following example:

let #formatPhone := function(#phone) {
  concat("+47", #phone)
}

return {
  "name" : #input.payload.name,
  "phone" : #formatPhone(#input.payload.phone)
}

{
  "name" : "Allen",
  "phone" : "12345678"
}

Arrays and Sequences

Even though arrays are a valid type in JSONiq, it is helpful to think in terms of JSONiq’s own sequence type. Sequences, like arrays, are an ordered list of items but declared with parentheses. For example:

let #myArray := [1, 2, 3]
let #mySequence := (1, 2, 3)

Sequences are JSONiq’s preferred construct. They have a simpler lookup selector (e.g., #mySequence[1]) compared to arrays that use double square brackets (e.g., #myArray[[1]]). In JSONiq, however, array and sequence indexes start at 1, not 0. In the following example, the name property is retrieved from the first object in a payload array:

{
  "firstName" : #input.payload[[1]].name
}

[
  {
    "name" : "Anne"
  },
  {
    "name" : "Allen"
  }
]

Sequences have no meaning in the context of an integration flow. Incoming payloads that contain arrays will remain as arrays, and any sequences returned in the mapping will become arrays in the output. Except for the case where the sequence only have one item (a singleton sequence), and that item itself will be returned instead of an array. Therefore, in order to enforce a consistent array output, you need to wrap the sequence in an array constructor (e.g., []). See the Sequences vs. Arrays section for more information.

To perform array-like operations in the mapping itself, you need to "unbox" the array as a sequence using single brackets (e.g., []). A sequence can then be iterated over or implicitly mapped.

The following example unboxes an array of objects and extracts the name properties into a new sequence of strings, and then returns the sequence as an array consistently using array constructor:

let #names := #input.payload[].name

return [#names]

[
  {
    "name" : "Anne"
  },
  {
    "name" : "Allen"
  }
]

An unboxed array can also be filtered on with the help of a predicate. In the following example, a predicate is used to filter on the objects' name properties:

let #person := #input.payload[][##.name = "Allen"]

return #person

[
  {
    "name" : "Anne",
    "company" : "{company-name}"
  },
  {
    "name" : "Allen",
    "company" : "{company-name}"
  }
]

Note that the predicate, [##.name = "Allen"], uses the syntax ## (an alias of $$) to capture the context item (i.e., the current item being evaluated). If only one object is found by the predicate, the result is a single object. If more than one object is found, the result is a sequence of objects. In the example above, change the predicate to [##.company = "{company-name}"] to see the difference.

FLWOR Expressions

FLWOR expressions are the most powerful feature of JSONiq. The FLWOR acronym encompasses for clauses, let, where, order by, and return. At the very least, a FLWOR expression must start with a let or for clause and end with a return clause. A mapping that uses those minimums might look like the following:

let #people := for #person in #input.payload[]
  return {
    "firstName" : #person.name
  }

return [#people]

[
  {
    "name" : "Anne"
  },
  {
    "name" : "Allen"
  }
]

Recall that arrays can be unboxed into sequences. Sequences, in turn, can be iterated over using a for clause. In the iteration above, we return a new object to essentially map each item in the array/sequence.

for clauses can also iterate over more than one sequence. In the following example, the for clause is used with the payload array and an internal array:

let #countries := [
  { "name" : "Norway" },
  { "name" : "USA" }
]

let #people := for #person in #input.payload[], #country in #countries[]
  return {
    "name" : #person.name,
    "location" : #country.name
  }

return [#people]

[
  {
    "name" : "Anne",
    "locationId" : 1
  },
  {
    "name" : "Allen",
    "locationId" : 2
  }
]

If you run the above code and look at the output, you’ll see that every item in the payload sequence also iterated over the countries sequence, producing a new sequence with four items. You can think of it like a nested for loop.

On its own, this might not seem practical, but when you bring in the remaining FLWOR clauses, you can accomplish a SQL-like joining of two arrays. In the following example, we’ve added a relational ID that the arrays can be "joined" on:

let #countries := [
  { "id" : 1, "name" : "Norway" },
  { "id" : 2, "name" : "USA" }
]

let #people := for #person in #input.payload[], #country in #countries[]
  where #person.locationId = #country.id
  order by #person.name
  return {
    "name" : #person.name,
    "location" : #country.name
  }

return [#people]

[
  {
    "name" : "Anne",
    "locationId" : 1
  },
  {
    "name" : "Allen",
    "locationId" : 2
  }
]

The countries array was hardcoded for this example, but in a real world project, these values could potentially come from a Dynamic Table.

FLWOR expressions also support group by and count clauses for performing other SQL-like queries.

Was this page helpful?