Looking for a "join-like" or "lookup-like" expression.

[exvito]

Hello,

First and foremost, thanks in advance for sharing JMESPath which I find very elegant and powerful.

I have recently been faced with a challenge for which I could not build a single JMESPath expression that solves it for me. Consider the case of a JSON document like:

{
    "things": [{
        "id": 1,
        "name": "Thing #1",
        "owner_id": 1
    }, {
        "id": 2,
        "name": "Thing #2",
        "owner_id": 2
    }, {
        "id": 3,
        "name": "Thing #3",
        "owner_id": 1
    }],
    "users": [{
        "id": 1,
        "name": "User #1"
    }, {                                                                                  
        "id": 2,
        "name": "User #2"
    }]
}

The objective would be to return a the list of things where each owner_id wold be "resolved", so to speak, from the entries in the users array. To put it In SQL-ish terms, I'm looking to join things and users on things[*].owner_id and users[*].id.

After trying multiple things, the best and cleanest (albeit non-working!) solution I came up with was something like:

things[*].{id: id, owner_id:owner_id, owner:users[?id == owner_id]}

Unfortunatelly, within the multiselect hash, the expression for owner can't resolve users. It's clearly a matter of scope. Having investigated furter, I found out about JEP 11 jmespath/jmespath.site#6 and it seems like the let function could be a possible solution.

Facts:

• I could create a custom function to handle my particular need.
• With JEP 11 in place, I could build an expression to solve this particular challenge.

Other than those two options, am I missing something that could be used currently?

Thanks in advance.
Regards.