Two dimensional join or derived table from JSON

SQL++
1

Hello all, my first post here so be kind.

I have a collection of paired elements, bookingId and passengerId that i need to match with records in a document where both elements exist. I need a query that does something like this:

SELECT *
FROM `MY_BUCKET` gc 
WHERE gc.docType = "GUEST_DOCUMENT"
AND [gc.bookingId,gc.passengerId] IN [{204154,361350701},{354563,362782522}]

Even better is something that can take formatted JSON a i can create a derived table and join it.

SELECT * 
FROM (select [{"bookingId":204154,"guestId":361350701},{"bookingId":354563,"guestId":362782522}]) derviedTable
JOIN `MY_BUCKET` gc on (gc.docType = "GUEST_DOCUMENT" AND gc.bookingId = derviedTable.bookingId AND gc.guestId = derviedTable.guestId)

Neither of these work above. My N1QL is weak still and I am still learning, but having a hard time finding examples to do this type of two dimensional join.

2 
CREATE INDEX ix1 ON `MY_BUCKET`  (  [bookingId,passengerId]) WHERE docType = "GUEST_DOCUMENT"
SELECT *
FROM `MY_BUCKET` gc 
WHERE gc.docType = "GUEST_DOCUMENT"
AND [gc.bookingId,gc.passengerId] IN [[204154,361350701],[354563,362782522]]
1 Like