Join Tables Using a Value Inside a JSONb Column

How to join on a nested value from a jsonb column?

jsonb_object_keys() is a set-returning function which cannot be used the way you do - as the error messages tells you. What's more, json_object_keys() returns top-level key(s), but it seems you are only interested in the value. Try jsonb_each() instead:

SELECT s.id
     , s.uid
     , s.date
     , jsonb_build_object(l.uuid::text, o.value) AS new_data
FROM   stats s
CROSS  JOIN LATERAL jsonb_each(s.data) o  -- defaults to column names (key, value)
LEFT   JOIN links l ON l.path = o.value->>'source';

db<>fiddle here

jsonb_each() returns top-level key and value. Proceed using only the value.

The nested JSON object seems to have the constant key name 'source'. So the join condition is l.path = o.value->>'source'.

Finally, build the new jsonb value with jsonb_build_object().

While this works as demonstrated, a couple of questions remain:

The above assumes there is always exactly one top-level key in stats.data. If not, you'd have to define what to do ...
The above assumes there is always exactly one match in table links. If not, you'd have to define what to do ...
Most importantly:
If data is as regular as you make it out to be, consider a plain "uuid" column (or drop it as the value is in table links anyway) and a plain column "source" to replace the jsonb column. Much simpler and more efficient.

Postgres: How to join table with values from jsonb[] column

Edited for jsonb[] type:

Demo

select
  r.data ->> 'name' as name
from
  accounts a
  cross join unnest(a.locations) al
  inner join regions r on r.id = (al ->> 'id')::int

P.S: for jsonb type:

You can use jsonb_to_recordset function and CROSS JOIN to join JSON array record with table.

Demo

select
  r.data ->> 'name' as name
from
  accounts a
  cross join jsonb_to_recordset(a.locations) as al(id int)
  inner join regions r on r.id = al.id

Postgres join tables array JSONB column

Instead of JOINing on a @>, I would recommend to do joins against lateral subqueries that unnest the json:

SELECT bonuses.rules, bonuses.bonus_id, provider_games.provider_game_id
FROM bonuses,
LATERAL jsonb_array_elements(bonuses.rules) rules(obj),
LATERAL jsonb_array_elements_text(obj -> 'rule' -> 'providers') rule_provider(id)
INNER JOIN provider_games ON provider_games.provider_game_id = rule_provider.id::bigint;

^{(Online demo)}

Join tables with values from JSON

Your column records on table code_table has problem in json data and i fixed problem and change to below format:

{"color_cd":{"30":{"code":"30","color":"yellow"},"55":{"code":"55","color":"green"},"60":{"code":"60","color":"red"}}}

You can see query structure and result in dbfiddle


select
 f.id,
 f.name,
 j_cd.value ->> 'color' as color
from 
  code_table ct 
  cross join jsonb_each(records::jsonb -> 'color_cd') j_cd
  inner join fruits f on f.type_cd :: jsonb ->> 'color_cd' = j_cd.key::text
where 
  ct.name = 'color_cd'

How to Join PostgreSQL JSONB Data with Foreign Table

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
)