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.
Best Answer
The keys for the
->
or->>
operators need to be supplied as strings:Or use the
#>>
operator and pass an array:Which is basically the same as using
jsonb_extract_path_text
Online example