加入 JSONB 列中的嵌套 obj 屬性和 varchar 列

我有connections兩個列的表varchar：

+---------+---------------+
| user_id | other_user_id |
+=========+===============+
| foo     | bar           |
+---------+---------------+
| baz     | asdf          |
+---------+---------------+

以及參考events的表格：user_id``connections.user_id

+---------+----------------+
| user_id | payload        |
+=========+================+
| foo     | {              |
|         |   otherUser: { |
|         |     id: "bar"  |
|         |   }            |
|         | }              |
+---------+----------------+
| baz     | {              |
|         |   otherUser: { |
|         |     id: "asdf" |
|         |   }            |
|         | }              |
+---------+----------------+

我想加入表格connections.user_id = events.user_id和connections.other_user_id = events.payload.otherUser->>id

不過，我不確定如何訪問JSONB 列id中嵌套對像中的欄位。otherUser

這是我想出的最多的：

SELECT * FROM events ev 
JOIN connections con ON con.user_id = ev.user_id AND con.other_user_id = ev.payload->otherUser->>id

也試過con.other_user_id = (ev.payload)::jsonb->otherUser->>id

對於這 ^ 兩個選項，我得到“列otheruser不存在”。

然後也嘗試了：

con.other_user_id = text(jsonb_extract_path(ev.payload::jsonb, 'otherUser', 'id'))

這不會引發錯誤，但也不會返回任何行。

但我收到有關語法的錯誤。

->or運算符的鍵->>需要作為字元串提供：

SELECT * 
FROM events ev 
 JOIN connections con 
   ON con.user_id = ev.user_id 
  AND con.other_user_id = ev.payload -> 'otherUser' ->> 'id'

或者使用#>>運算符並傳遞一個數組：

SELECT * 
FROM events ev 
 JOIN connections con 
   ON con.user_id = ev.user_id 
  AND con.other_user_id = ev.payload #>> '{otherUser,id}';

這與使用基本相同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');

線上範例

引用自：https://dba.stackexchange.com/questions/279235