从json转换为uuid的postgres

我有两张很简单的桌子 sessions 哪个有 json 像这样的列:

+----+-------------------------------------------------------+
| id |                         data                          |
+----+-------------------------------------------------------+
|  1 | { "user_id": "bc2166dd-ca1d-41c6-9c66-610f844ca139" } |
+----+-------------------------------------------------------+

还有一个 users 像这样的桌子:

+--------------------------------------+--------------------+
|                  id                  |       email        |
+--------------------------------------+--------------------+
| bc2166dd-ca1d-41c6-9c66-610f844ca139 | foobar@example.com |
+--------------------------------------+--------------------+

有没有办法加入 杰森 像这样的列类型?

with recursive active_sessions AS (
  select
  data->'users_id' as sid
  from sessions
)

select *
from active_sessions
join users ON active_sessions.sid = users.id
where sid is not null;

在我的基本尝试中,我陷入了困境,似乎无法将json文本用于连接,我得到了以下错误:

[22P02] ERROR: invalid input syntax for uuid: ""bc2166dd-ca1d-41c6-9c66-610f844ca139""

注意双引号…?

我也试过 user_id 像这样选择 (data->'user_id')::uuid 但是没有运气。