Joins in the same bucket

SQL++
1

Hello everyone!

I need some help with N1QL joins and all that stuff arround. My problem is, that all the docs are only available in english.
So my problem:

I have two docs in the same bucket called “signs”.

Doc 1 (ID: goldrush):
{ "displayName": "GoldRush", "layout": "game", "signs": [ { "x": 2, "y": 73, "z": 70 }, { "x": 0, "y": 73, "z": 70 }, { "x": -2, "y": 73, "z": 70 }, { "x": 2, "y": 72, "z": 70 }, { "x": 0, "y": 72, "z": 70 }, { "x": -2, "y": 72, "z": 70 } ] }

Doc 2 (ID: layouts):
{ "game": { "lines": [ { "1": "sign.id" }, { "2": "sign.id" }, { "3": "$metadata.arena" }, { "4": "$metadata.players / $metadata.maxPlayers" } ], "onClick": "JOIN_SERVER" }, ... Some other layouts }

And what i want as an result should be:
{ "id":"goldrush" (<- DOC ID) "displayName": "GoldRush", "layout": { (<- The hard part: replace "game" to the field "game" from the layouts doc) "lines": [ { "1": "sign.id" }, { "2": "sign.id" }, { "3": "$metadata.arena" }, { "4": "$metadata.players / $metadata.maxPlayers" } ], "onClick": "JOIN_SERVER" }, "signs": [ { "x": 2, "y": 73, "z": 70 }, { "x": 0, "y": 73, "z": 70 }, { "x": -2, "y": 73, "z": 70 }, { "x": 2, "y": 72, "z": 70 }, { "x": 0, "y": 72, "z": 70 }, { "x": -2, "y": 72, "z": 70 } ] }

So the field “layout” from Doc 1 should be replaced with the “game” field from Doc 2 (layouts), because Doc 1 says its layout is “game”.

I tried it for hours but couldn’t get the solution. I would be very thenkful if you can halp me.

Sincerely yours!

2

Hi,

Here is the general pattern. However, in order to perform JOINs in N1QL, one document must contain the ID of the other document. It is not clear if your data is structured that way…

SELECT
goldrush.displayName,
goldrush.signs,
layouts.onClick,
layouts.[goldrush.layout] AS layout
FROM signs goldrush
JOIN signs layouts ON KEYS ...