Join on nested obj attribute in JSONB column along and a varchar column

Question

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: "bar" |
| | } |
| | } |
+---------+----------------+
| baz | { |
| | otherUser: { |
| | id: "asdf" |
| | } |
| | } |
+---------+----------------+

I would like to JOIN the tables on connections.user_id = events.user_id and connections.other_user_id = events.payload.otherUser->>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->otherUser->>id

also tried con.other_user_id = (ev.payload)::jsonb->otherUser->>id

for these ^ two options I get "column otheruser doesn't exist".

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.

Answer 1

The keys for the -> or ->> 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 -> 'otherUser' ->> 'id'

Or use the #>> 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 #>> '{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