1

I am having hard time trying to figure out this join. I have two related tables:

Reservations

 id | data                 
----+------------
  1 | something

CalendarEvents

 id |                 relates_to                                      | time
----+-------------------------------------------------------------------------------
  1 | {"type:": "", "relations": [{"#type": "", "relatesToId": "1"}]} | "2022-02-02"

I'd like to select Reservations and all CalendarEvents matching them by id, like this (simplified)

SELECT r, ce FROM reservations r
LEFT JOIN calendar_events ce ON r.id = ce.relates_to.relations.relatesToId
WHERE ce.time > '2022-01-01';

relates_to is in jsonb

1 Answer 1

0

You need to first unnest the JSONB array using jsonb_array_elements. You can do this using a CROSS JOIN LATERAL, which you can place inside the JOIN, allowing you to use it as part of the join condition

SELECT r, ce
FROM reservations r
LEFT JOIN calendar_events ce
    CROSS JOIN LATERAL jsonb_array_elements(ce.relates_to->'relations') rel(relation)
  ON r.id = (rel.relation->>'relatesToId')::int
WHERE ce.time > '2022-01-01';

db<>fiddle

Note the use of -> to retrieve jsonb and ->> to retrieve text. You also need to convert the text to int

1
  • Thank you very much!
    – Niph
    Oct 3, 2022 at 8:30

Your Answer

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

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