Exploring the IBM ACE SELECT, ROW and THE functions in ESQL

Let’s talk about SELECT in ESQL — and I don’t mean database queries (although they are rather similar). I mean using SELECT to slice and dice message data inside IBM App Connect Enterprise (ACE). Think of it like SQL, but for navigating the message tree instead of a table.

This post is all about the different ways you can use SELECT in ACE: returning arrays, rows, or just that one perfect value. To clarify, I’ve built out a quick demo flow with a very simple message structure, grabbed the outputs, and broken it all down for you.

We’ll look at:

Using plain SELECT to return a list of array items
Wrapping SELECT with ROW to structure the reply as a single array
Using THE to pull just one value (hence the title 😉)

Here’s the input JSON I’m working with:

{

“contact”: {

“info”: {

“name”: “John”,

“lastName”: “Wick”,

“title”: “baba yaga”

},

“details”: {

“phone”: [{ “type”: “business”, “number”: “911” }],

“email”: [

{ “type”: “personal”, “address”: “john@wick.com” },

{ “type”: “business”, “address”: “baba@yaga.com” },

{ “type”: “personal”, “address”: “shootemup@aol.com” }

]

}

Catchy, right?

All the source code and resources used in this blog can be downloaded from my GitHub repository.

Plain SELECT – returning a message tree

Let’s start simple. We want to grab all the personal email addresses and return them as a JSON array.

I’ll be using a very simple flow (the same setup for all 3 examples):

Parsing the input data is done by the following ESQL code from the RetrieveEmailList Compute Node:

ACE - ESQL code

The code block itself:

— create proper json structure

CREATE FIELD OutputRoot.JSON.Data.emailList IDENTITY(JSON.Array);

— select required data

SET OutputRoot.JSON.Data.emailList.Item[] = (

SELECT U.address

FROM InputRoot.JSON.Data.contact.details.email.Item[] AS U

WHERE U.type = ‘personal’

);

And here’s what we get back:

{

“emailList”: [

{ “address”: “john@wick.com” },

{ “address”: “shootemup@aol.com” }

]

}

Note:

That CREATE FIELD with IDENTITY(JSON.Array) is key — without it, you might run into JSON formatting weirdness or errors. Always prep your output structure when working with arrays in JSON.

If you remove the JSON Array creation block, your output would look like this

{

“emailList” : {

“Item” : {

“address” : “shootemup@aol.com”

}

Since the fields are not known to be an array, they overwrite each other on output, and you only get to see the last value.

SELECT + ROW – returning a tree structure

Now let’s return the same data, but as a ROW. This can be useful when you want to work with a single structured block of data, not loose array elements.

The test flow:

The ESQL code from the RetrieveEmailRow Compute Node:

ESQL code IBM ACE

The code block:

— create proper json structure

CREATE FIELD OutputRoot.JSON.Data.emailRow IDENTITY(JSON.Array);

— select required data into a row

SET OutputRoot.JSON.Data.emailRow = ROW (

SELECT U.address

FROM InputRoot.JSON.Data.contact.details.email.Item[] AS U

WHERE U.type = ‘personal’

);

Output:

{

“emailRow”: [

{ “address”: “john@wick.com” },

{ “address”: “shootemup@aol.com” }

]

}

Functionally this is still returning multiple items, but we’ve wrapped them in a ROW constructor. It’s subtly different in how ACE handles the result internally, especially if you plan to reuse or reference the result as a single variable downstream. Even though the resulting JSON looks the same, with or without the ROW cast, there is a difference in the internal structure.

Internally, using ROW(…) changes how the data is handled in memory – it’s treated as a single row structure rather than a collection of elements. This can be important when you’re manipulating the result later or passing it between compute nodes.

Below you can see the structure without the ROW cast on the left, with the ROW cast on the right.

SELECT + THE – grab one specific value

This is where it gets fun. Let’s say we only want one business email – just the first match. This is where THE comes in handy. It returns a single scalar value from the result set — no array, no row, just the value.

The flow I’ll be using:

IBM ACE flow

The RetrieveEmail ESQL code:

The code block:

— select the first match

SET Environment.Variables.businessEmail = THE (

SELECT U.address

FROM InputRoot.JSON.Data.contact.details.email.Item[] AS U

WHERE U.type = ‘business’

);

— return the value of the selected data

SET OutputRoot.JSON.Data.businessEmail = Environment.Variables.businessEmail.address;

Result:

{

“businessEmail”: “baba@yaga.com”

}

Note that I assign Environment.Variables.businessEmail.address to the OutputRoot and not Environment.Variables.businessEmail. THE gives you the first matching row, but when assigned to a variable, the structure is nested. That’s why we explicitly extract address to flatten the response.

If I did the same but changed the SELECT to retrieve personal emails, not the business emails, I get the following result:

Which is the first personal email in the returned list. I didn’t update the variable name – got a bit lazy 😊 – but you get the idea.

THE is your go-to when you’re expecting only one value. If there are multiple matches, it’ll return the first. If there are none – well, we all know what ESQL does with NULL values.

SELECT THE ROW – recap

Technique	Use Case	Output Structure
SELECT	Retrieve a list of values	JSON array elements
ROW(SELECT…)	Wrap the multiple values as a single row	JSON array (internally treated as row)
THE(SELECT…)	Return a single value (first match	Single value

Beyond This Demo: SELECT Can Do More

While this post focused on using SELECT to extract and shape data within a message, it’s worth noting that SELECT in ESQL is far more powerful. You can use it to:

Transform message content into different structures
Join elements across multiple parts of a message tree
Translate values based on conditions or lookups
And even combine it with other ESQL features for more complex flows

It’s one of the most flexible and expressive tools in the ESQL toolbox — and this post is just a slice of what it can do.

If you want to explore further, check out the links below.

What about Java

Not a fan of ESQL? No worries – you can do the same thing using a Java Compute Node. The syntax is different, but the logic is the same: extract values, structure your output, and go. Below are two ways to approach it: manual traversal and XPath (for XML only).

Let’s say we want to do the following:

Get all personal email addresses (similar to our SELECT … WHERE U.type = ‘personal’)
Get the first business email (like THE(SELECT …))

To do this in Java, you can use XPath or manually walk the MbElement tree

Working with JSON (manual tree traversal)

Extracting all personal emails while walking the MbElement tree:

MbElement current = emailArrayInput.getFirstChild();

while (current != null) {

MbElement typeElem = current.getFirstElementByPath(“type”);

MbElement addressElem = current.getFirstElementByPath(“address”);

if (typeElem != null && addressElem != null) {

String type = typeElem.getValueAsString();

String address = addressElem.getValueAsString();

if (“personal”.equals(type)) {

MbElement item = emailArray.createElementAsLastChild(MbElement.TYPE_NAME, “Item”, null);

item.createElementAsLastChild(MbElement.TYPE_NAME_VALUE, “address”, address);

}

if (“business”.equals(type) && businessEmail == null) {

businessEmail = address;

}

current = current.getNextSibling();

}

Extracting the first business email (single value) with XPath is unfortunately only possible with XML messages and doesn’t work for JSON. That is why in the above example I’ve added the business email code inside the loop.

The above code results in the following message

message flow