Postgres cast to UUID from JSON

Question

I have two very simple tables sessions which has a json column like this:

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

And also a users table like this:

+--------------------------------------+--------------------+
|                  id                  |       email        |
+--------------------------------------+--------------------+
| bc2166dd-ca1d-41c6-9c66-610f844ca139 | [email protected] |
+--------------------------------------+--------------------+

Is there a way to join on the contents of the json column type like this?

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;

In my basic attempts, I'm stuck and cannot seem to get the json text to be able to be used in a join, I get this error:

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

note the double quotes...?

I've also tried casting the user_id in the select like (data->'user_id')::uuid but without luck.

Answer 1

The issue is that the -> operator returns JSON, which is why you get a string back with quotes as part of the string instead of the string you'd expect. See details in https://www.postgresql.org/docs/9.5/functions-json.html .

You should use the ->> operator instead as it will return an integer or text value. The returned text will not include the quotes. You can then cast the text to a uuid and Postgres will recognize it.

SQL Fiddle: http://sqlfiddle.com/#!17/d9e06/3/0

create table sessions (id integer, data json);
insert into sessions values(1, ' { "user_id": "bc2166dd-ca1d-41c6-9c66-610f844ca139" }');
create table users (id uuid, email text);
insert into users values('bc2166dd-ca1d-41c6-9c66-610f844ca139','[email protected]');

--query using ->> operator to return an integer or text instead of JSON
select (data->>'user_id')::uuid 
, * 
from sessions 
join users on (data->>'user_id')::uuid = users.id;