How to achieve ansi join here

SELECT b.locationName FROM transport b USE KEYS (SELECT RAW META().id FROM transport WHERE type=’_transport_location’ AND ifmissing(deleted, false) = false)
JOIN transport k ON KEYS (
select Raw MAX([lastUpdatedAt, id])[1] as id from (select MAX([updatedAt, updatedAt])[1] as lastUpdatedAt,MAX([updatedAt, locationName])[1] as locationName,
MAX([updatedAt, meta().id])[1] as _id from transport where type = ‘_transport_task’ and locationName is not null and locationName NOT IN [‘OOTY’]
and status in [‘START’, ‘END’] and ifmissing(deleted, false) = false group by locationName
union
select MAX([updatedAt, updatedAt])[1] as lastUpdatedAt, MAX([updatedAt, destinationlocationName])[1] as locationName, MAX([updatedAt, meta().id])[1] as _id from transport
where type = ‘_transport_task’ and destinationlocationName is not null and destinationlocationName NOT IN [‘OOTY’]
and status in [‘START’, ‘END’] and ifmissing(deleted, false) = false group by destinationlocationName) as i group by locationName
)

Please provide relation with sample documents.

Use the following query and JOIN with _transport_location document based on your releation

        SELECT MAX([d.UpdatedAt, d])[1].*
         FROM (SELECT updatedAt, locationName, META().id
               FROM transport
               WHERE type = "_transport_task"
                     AND locationName NOT IN ["OOTY"]
                     AND status IN ["START", "END"] AND IFMISSING(deleted, false) = false
               UNION
               SELECT updatedAt, destinationlocationName AS locationName, META().id
               FROM transport
               WHERE type = "_transport_task"
                     AND destinationlocationName NOT IN ["OOTY"]
                     AND status IN ["START", "END"] AND IFMISSING(deleted, false) = false
               ) AS d
        GROUP BY d.locationName
        )

Sorry, For not being clear.

I have a transport database. Where it tracks the journeys of the users.
I have list of locations for this transport database.
For admin we need to show that which user is visited the last location.

Here
_transport_location → Locations document type we support in transport database
_transport_task → Journeys document type that user creates a journey

Requirement :-
I need to get all locations include the last known Journey.
I know it can be achieved with Ansi right hand in 6.6 but how can we achieve the same using CB 6.0

SELECT b.locationName, b.user.userName, b.status, b.updatedAt FROM transport b USE KEYS (SELECT RAW META().id FROM transport WHERE type=’_transport_location’ AND ifmissing(deleted, false) = false)
JOIN transport k ON KEYS (
select Raw MAX([lastUpdatedAt, id])[1] as id from (select MAX([updatedAt, updatedAt])[1] as lastUpdatedAt,MAX([updatedAt, locationName])[1] as locationName,
MAX([updatedAt, meta().id])[1] as _id from transport where type = ‘_transport_task’ and locationName is not null and locationName NOT IN [‘OOTY’]
and status in [‘START’, ‘END’] and ifmissing(deleted, false) = false group by locationName
union
select MAX([updatedAt, updatedAt])[1] as lastUpdatedAt, MAX([updatedAt, destinationlocationName])[1] as locationName, MAX([updatedAt, meta().id])[1] as _id from transport
where type = ‘_transport_task’ and destinationlocationName is not null and destinationlocationName NOT IN [‘OOTY’]
and status in [‘START’, ‘END’] and ifmissing(deleted, false) = false group by destinationlocationName) as i group by locationName
)

Location Document
{
“coordinates”: ,
“createdAt”: “2021-02-19T08:24:34.222Z”,
“locationName”: “OOTY”,
“priorityForLocation”: “High”,
“status”: “ACTIVE”,
“type”: “_transport_location”,
“updatedAt”: “2021-02-25T12:03:48.134Z”
}

Journey Document
{
“createdAt”: “2021-03-15T06:14:11.307Z”,
“destinationLocation”: {
“_id”: “location:OOTY”,
“locationName”: “OOTY”,
“status”: “ACTIVE”
},
“location”: {
“_id”: “location:DELHI”,
“locationName”: “DELHI”,
“status”: “ACTIVE”
},
“operator”: {
“_id”: “user:OGRICK”,
“firstName”: “Rick”,
“lastName”: “Sanchez”,
“username”: “ogrick”
},
“plannedEndDate”: “2021-03-15T08:13:53.896Z”,
“plannedStartDate”: “2021-03-15T06:13:53.896Z”,
“actualStartDate”: “2021-03-15T06:13:53.896Z”,
“status”: “START”,
“type”: “_transport_task”,
“updatedAt”: “2021-03-15T06:14:11.307Z”,
}

Output Document
{
“locationName”: “OOTY”,
“userName”: “ogrick”
“status”: “END”,
“updatedAt”: “2021-03-10T05:16:47.732Z”
}

CB 6.0 right side of JOIN requires the bucket.
You can use UNNEST and do join.

OR

SELECT  tl.*, ARRAY v FOR v IN tltasks WHEN v.locationName = tl.locationName END AS users
FROM  transport AS tl
LET  tltasks = SELECT MAX([d.UpdatedAt, d])[1].*
         FROM (SELECT updatedAt, locationName, META().id
               FROM transport
               WHERE type = "_transport_task"
                     AND locationName NOT IN ["OOTY"]
                     AND status IN ["START", "END"] AND IFMISSING(deleted, false) = false
               UNION
               SELECT updatedAt, destinationlocationName AS locationName, META().id
               FROM transport
               WHERE type = "_transport_task"
                     AND destinationlocationName NOT IN ["OOTY"]
                     AND status IN ["START", "END"] AND IFMISSING(deleted, false) = false
               ) AS d
        GROUP BY d.locationName
        )
WHERE tl.type = "_transport_location" AND  ifmissing(tl.deleted, false) = false