I am trying to join a normal table to a table with a jsonb field via a property on that jsonb field in Postgres. Example:
Normal table:
create table Res
(
id uuid default uuid_generate_v4() not null
constraint res_pkey
primary key
)
;
Table with jsonb:
create table res_rem_sent
(
body jsonb not null,
search tsvector,
created_at timestamp with time zone default now(),
id uuid default uuid_generate_v4() not null
constraint res_rem_sent_pkey
primary key
);
create index idx_res_rem_sent
on res_rem_sent (body)
;
create index idx_search_res_rem_sent
on res_rem_sent (search)
;
an the body
field could look like: {"resId": <GUID>, ....}
I would like to join the res
table to the res_rem_sent
table on resId
where a res
id is equal to a give GUID id.