Page History

...

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.idasuser_id,

     jsonb_extract_path_text (users.jsonb,'active')::BOOLEAN aspatron_status,  ----- this gets the patron status, which is a boolean value; note that I have to cast the extract as "boolean"

      CASE

            WHEN jsonb_extract_path_text (users.jsonb,'active')::BOOLEAN = true

            THEN 'Active'

            ELSE 'Expired'

            END AS patron_status_name,  -------- this Case When statement is just for displaying the boolean value as "Active" or "Expired"

     jsonb_extract_path_text (users.jsonb,'personal','lastName') aslast_name,  ------- this extrcts the last name from the "personal" object (second-level extraction)

     jsonb_extract_path_text (users.jsonb,'personal','firstName') asfirst_name,  ------- this extracts the first name from the "personal' object (second-level extraction)

      jsonb_extract_path_text (users.jsonb,'username') asnet_id,  ------ this extracts "username" from the table (first-level extraction)

      jsonb_extract_path_text (users.jsonb,'barcode') asuser_barcode,  ------ this extracts "barcode" from the users table (first-level extraction)

      depts.jsonb #>> '{}' as dept_id,   ----- this is the shortcut method of displaying the values from the "depts" json object created by 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.nameasdepartment_name,

      depts.ordinalityasdept_ordinality   ------ this displays the ordinality of the departments 

FROM folio_users.users

      CROSS JOIN LATERAL  jsonb_array_elements (jsonb_extract_path (users.jsonb,'departments'))

      WITHordinalityAS depts (jsonb)

LEFTJOINfolio_users.departments__tASud   -------- 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:

Image Added