[FIXED] How to Join PostgreSQL JSONB Data with Foreign Table

Issue

I have a PostgreSQL database table with a JSONB column that contains keys to a foreign table.

The structure looks something like this.

Events
_ _ _ _

id:uuid
name: uuid
fields: JSONB { 
      date {},
      activities: ['uuid','uuid']
           }
...others columns

Activities
_ _ _ _ _ _ 

id: UUID
name: varchar

I am trying to use the query below to join the tables but I get cannot cast type JSONB to uuid[] or set-returning functions are not allowed

SELECT
  to_json(src) :: json AS event
FROM
  (
    SELECT
      events.id AS id,
      events.name AS name,
      events.fields -> 'time' AS time,
      events.fields ->> 'poster' AS poster,
      events.fields ->> 'address' AS address,
      categories.name AS category,
      -- array_agg(DISTINCT activities.name) AS activities,
      events.age_limit AS age_limit,
      min(tickets.price) AS least_ticket,
      events.description AS description,
      to_json(profiles.*) AS organizer,
      timezones.short_code AS timezone,
      array_agg(DISTINCT tickets.*) AS tickets,
      to_json(currencies.*) AS currency
    FROM
      events
      LEFT JOIN categories ON categories.id = (events.fields ->> 'category_id') :: uuid
      LEFT JOIN activities ON activities.id = Any(
        jsonb_build_array(events.fields ->> 'activities') :: uuid [ ]
      ) 
      LEFT JOIN tickets ON tickets.event_id = events.id
      LEFT JOIN profiles ON events.user_id = profiles.user_id
      LEFT JOIN currencies ON currencies.id = events.currency_id
      LEFT JOIN timezones ON events.timezone_id = timezones.id
    WHERE
      events.slug = 'annes-wedding-nqbh'
    GROUP BY
      events.name,
      categories.id,
      events.id,
      profiles.id,
      currencies.id,
      timezones.id
  ) src;

Please what's the best way I can join these tables and also in the case where the activities field has an array with keys & values.

Solution

You cannot cast jsonb to uuid, but you should be able to cast the jsonb array element to text, and that to uuid (assuming that's how you store UUIDs in JSON). But notice you will need to do that individually, not for the whole array at once:

…
LEFT JOIN activities ON activities.id IN (
  SELECT el::uuid
  FROM jsonb_array_elements_text(events.fields -> 'activities') AS el
)

Answered By - Bergi

Answer Checked By - Pedro (PHPFixing Volunteer)