What is the nested function call syntax for IoT Rule Engine's SQL?

Question

I want to republish messages from a topic to another.

When there's a message on topic: `node/node_id`  
it should resolve this expression:  
`user/get(${aws_lambda("arn:aws:lambda:us-east-1:1234567890:function:GetUserIDsForNode", {"data":{"node_id":"topic(2)"}} )}, 0)`  
To give me something like: `user/`

It has 3 levels of nesting:
[get](https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html#iot-sql-function-get) contains a [aws_lambda](https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html#iot-func-aws-lambda) call, which in-turn contains a [topic](https://docs.aws.amazon.com/iot/latest/developerguide/iot-sql-functions.html#iot-function-topic) call.

The issue is that it currently resolves the `aws_lambda` function, but not the rest. As they are not wrapped in `${}`.  
But when I wrap the other functions, like:  
`user/${get(${aws_lambda("arn:aws:lambda:us-east-1:1234567890:function:GetUserIDsForNode", {"data":{"node_id":"${topic(2)}"}} )}, 0)}`

The SQL is apparently problematic. It complains:
```
SqlParseException
Unexpected character '$' ${aws_lambda("arn:aws:lambda:us-east-1:1234567890:function:GetUserIDsForNode", {"data":{"node_id":"${topic(2)}"}} )}, 0)}/params ----^ at 1:5
```

When I use:  
`user/get(${aws_lambda("arn:aws:lambda:us-east-1:1234567890:function:GetUserIDsForNode", {"data":{"node_id":"${topic(2)}"}} )}, 0)`.  
and print the `data` payload inside the Lambda, I see that the `node_id` field is not resolved, it says:  
```
{
    "data": {
        "node_id": "${topic(2)}"
    }
}
```

So this time it does not complain about nesting, but it does not resolve to the actual value either.

What is the syntax for nested SQL function calls?

Giovanni Lauria · Answer

Hello,

there are several things that may go wrong :)

First of all, make sure the sintax is ok.

`user/${get(${aws_lambda("arn:aws:lambda:us-east-1:1234567890:function:GetUserIDsForNode", {"data":{"node_id":"${topic(2)}"}})}, 0)}
`

Varun Gawande · Answer

Turns out this is one of those things where I had to get out of my own way.

There's no need to think about how many places I have to add `${}`.  
The trick was to build a complete SELECT clause first (like a normal SQL statement), which means forgetting the `${}` part.

For me that looked like:  
`SELECT get(aws_lambda("LambdaARN", { "data":{"node_id": topic(2) }}), 0)`

THEN removing the `SELECT` and wrap the `${}` only around this:  
`${get(aws_lambda("LambdaARN", { "data":{"node_id": topic(2) }}), 0)}`

Finally I could add any other string literals I wanted around it:  
`user/${get(aws_lambda("LambdaARN", { "data":{"node_id": topic(2) }}), 0)}`

And that worked!

What is the nested function call syntax for IoT Rule Engine's SQL?

Relevant content