1

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.

1 Answer 1

5

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

1
  • I maybe used not enough test rows, but for me the 5. part is as fast as creating two indexes (one for the json resId and one text index on the uuid column). Would be interesting how this scales to a few million rows. Thanks for your solution!
    – Jan Peter
    Jan 23, 2019 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.