Join on nested obj attribute in JSONB column along and a varchar column I have table connections where both columns are varchar:
+---------+---------------+
| user_id | other_user_id |
+=========+===============+
| foo     | bar           |
+---------+---------------+
| baz     | asdf          |
+---------+---------------+

And table events where user_id references connections.user_id:
+---------+----------------+
| user_id | payload        |
+=========+================+
| foo     | {              |
|         |   otherUser: { |
|         |     id: &quot;bar&quot;  |
|         |   }            |
|         | }              |
+---------+----------------+
| baz     | {              |
|         |   otherUser: { |
|         |     id: &quot;asdf&quot; |
|         |   }            |
|         | }              |
+---------+----------------+

I would like to JOIN the tables on connections.user_id = events.user_id and connections.other_user_id = events.payload.otherUser-&gt;&gt;id
Though, I'm not sure how to access the id field in the nested otherUser object in the JSONB column.
This is the most I've come up with:
SELECT * FROM events ev 
JOIN connections con ON con.user_id = ev.user_id AND con.other_user_id = ev.payload-&gt;otherUser-&gt;&gt;id

also tried con.other_user_id = (ev.payload)::jsonb-&gt;otherUser-&gt;&gt;id
for these ^ two options I get &quot;column otheruser doesn't exist&quot;.
Then also tried:
con.other_user_id = text(jsonb_extract_path(ev.payload::jsonb, 'otherUser', 'id'))
Which throws no error but also no rows are returned.
But I get error about the syntax.
The keys for the -&gt; or -&gt;&gt; operators need to be supplied as strings:
SELECT * 
FROM events ev 
  JOIN connections con 
    ON con.user_id = ev.user_id 
   AND con.other_user_id = ev.payload -&gt; 'otherUser' -&gt;&gt; 'id'

Or use the #&gt;&gt; operator and pass an array:
SELECT * 
FROM events ev 
  JOIN connections con 
    ON con.user_id = ev.user_id 
   AND con.other_user_id = ev.payload #&gt;&gt; '{otherUser,id}';

Which is basically the same as using jsonb_extract_path_text
SELECT * 
FROM events ev 
  JOIN connections con 
    ON con.user_id = ev.user_id 
   AND con.other_user_id = jsonb_extract_path_text(ev.payload, 'otherUser','id');

Online example