Postgres - Cannot Join Json Array Values on UUID

Question

So I have one table Profession:

UUID UUID,
Name TEXT

Where UUID is uuid1, uuid2, uuid3, etc.

Then another table Degrees:

 DegreeName TEXT,
 Record JSON

Where the Record->'Professions' json record may contain a list of Profession.uuid, such as {"Professions":"[uuid1,uuid2]"}, though the field could be NULL such as {"Professions":"[]"}.

Here is how I am trying to join the two tables:

SELECT prof.uuid, prof.Name, d.DegreeName

FROM Profession prof
LEFT JOIN (
        SELECT DegreeName, json_array_elements(Record->'Professions') prof_uuid
        FROM  Degrees
    ) d
ON d.prof_uuid::TEXT=prof.uuid::TEXT

When I do this, I only get Nulls. Note that I cast to TEXT because I cannot cast the Professions array to a UUID because of blanks.

I have tried manually searching for the Profession table using a value from the Professions list and it works:

SELECT *
FROM Profession
WHERE uuid = 'someprofuuid'

Is there some cast I missing?

Answer 1

I figured it out. The problem was I was using json_array_elements instead of json_array_elements_text. When I switched that, the join worked:

SELECT prof.uuid, prof.Name, d.DegreeName

FROM Profession prof
LEFT JOIN (
        SELECT DegreeName, json_array_elements_text(Record->'Professions') prof_uuid
        FROM Degrees
    ) d
ON d.prof_uuid::TEXT=prof.uuid::TEXT