*** 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.
SELECT
...
users.id
...
ASuser_id,
...
(users.jsonb
...
#>> '{active}')::
...
BOOLEANASpatron_
...
status,
...
...
CASE
...
WHEN (users.jsonb
...
#>> '{active}')::
...
BOOLEAN = true
...
THEN'Active'
...
ELSE'Expired'
...
END
...
ASpatron_status_name,
...
-------- this Case When statement is just for displaying the boolean value as "Active" or "Expired"
...
users.jsonb
...
#>>'{personal
...
,
...
lastName}'
...
ASlast_name,
...
------- this extrcts the last name from the "personal" object (second-level extraction)
...
users.jsonb
...
#>>'{personal
...
,
...
firstName}'
...
ASfirst_name,
...
------- this extracts the first name from the "personal' object (second-level extraction)
...
users.jsonb
...
#>>'{username}'
...
ASnet_id,
...
------ this extracts "username" from the table (first-level extraction)
...
users.jsonb
...
#>>'{barcode}'
...
ASuser_barcode,
...
------ this extracts "barcode" from the users table (first-level extraction)
...
depts.jsonb #>>'{}'
...
ASdept_id,
...
----- this
...
extracts the array values from the "depts" json object created
...
through the cross join
...
. Note that the curly brackets are empty; this is because there is no tag for the values, it's just the values themselves. If there were a tag for the values, the tag name would go in the curly brackets
...
ud.name
...
ASdepartment_name,
...
depts.ordinality
...
ASdept_
...
ordinality------ this displays the ordinality of the departments (the sequence of
...
occurrence in the record)
...
FROMfolio_users.users
...
CROSSJOIN
...
LATERALjsonb_array_elements (jsonb_extract_path (users.jsonb,'departments'))
...
WITHordinality
...
ASdepts (jsonb)
LEFTJOINfolio_users.departments__tAS
...
ud-------- I am joining to the the departments__t table to get the department name
...
ON (depts.jsonb #>> '{}')::UUID = ud.
...
id-------- note that I'm using the shortcut expression to join to the id in the departments__t table
RESULT:
