0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.