*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
Because "departments" is an array, you still have to extract it with a cross join statement*, as in the previous example. Then you need to display the array elements using the "shortcut" method in the Select stanza.
...
ON (depts.jsonb #>> '{}')::UUID = ud.id -------- note that I'm using the shortcut expression to join to the id in the departments__t table
- you don't need the cross join lateral expression if you use nested json extract statements in the Select stanza; the cross join makes it less complicated, but the result is the same. However, you DO need to use a cross join if you are including ordinality of values in the results.
RESULT:
