Join JSONB column to normal PK column in Postgres

Question

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.

Answer 1

You can use the following query:

SELECT
    *
FROM
    res
    JOIN res_rem_sent ON res_rem_sent.body->>'resId' = text(res.id) ;

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:

1. The ->> operator gets a JSON object field as text.

2. 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.

3. If you want the query to be performant, you need to have at least a (functional) index on the body->>'resId' part

   CREATE INDEX idx_uuid_res_rem_sent ON res_rem_sent((body->>'resId')) ;
-- NOTE: the double parenthesis aren't optional

4. You get the maximum performance if you also have a text index on Res.id:

   CREATE INDEX idx_uuid_res ON Res(text(id)) ;

5. 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