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.
Best Answer
You can use the following query:
JSON represents all of its contents as text, and that's the reason you have to be very careful on how the UUIDs are converted to text by PostgreSQL.
Things to take into consideration:
The
->>
operator gets a JSON object field as text.On the JSON(B) side, you need to represent the UUIDs using the standard form (as explained in UUID Type), so that conversions between UUID type to the JSON(B) text representation produces the same results.
If you want the query to be performant, you need to have at least a (functional) index on the
body->>'resId'
partCREATE INDEX idx_uuid_res_rem_sent ON res_rem_sent((body->>'resId')) ;
-- NOTE: the double parenthesis aren't optional
You get the maximum performance if you also have a text index on
Res.id
:CREATE INDEX idx_uuid_res ON Res(text(id)) ;
Performance could possibly be improved if, instead of using the text representations, comparisons and indexes are done using UUID representations:
(res_rem_sent.body->>'resId')::uuid = res.id
In this case, if the conversion fails, you will not be able to insert the data into the table.
DBfiddle here to see a practical simulation of this scenario