*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
In the example below, 'requester_last_name' and 'requester_first_name' are both "second-level" (subordinate) fields in the folio_users.users table. They are found under the "first level" field called "personal." The "personal" field contains many additional "second-level" subfields in the JSON data array (e.g., first_name, middle_name, last_name, id, etc.).
SELECT
...
folio_users.users__.id,
...
jsonb_extract_path_text
...
(folio_users.users__
...
.jsonb,
...
'personal',
...
'lastName')
...
AS requestor_last_name,
...
jsonb_extract_path_text
...
(folio_users.users__
...
.jsonb,
...
'personal',
...
'firstName')
...
AS requestor_first_name
FROM
...
folio_users.users__
The following expression will also get the second-level values for "lastName" and "firstName":
SELECT
...
folio_users.users__.id,
...
users__
...
.jsonb
...
#>>
...
'{personal,
...
lastName}'
...
AS
...
requestor_last_name,
...
users__
...
.jsonb
...
#>>
...
'{personal,
...
firstName}'
...
AS
...
requestor_first_name
FROM
...
folio_users.users__
Extracting "Third level" Data Fields
...
SELECT
al.id as audit_loan_id,
jsonb_extract_path_text (al.jsonb, 'loan', 'status', 'name') AS loan_status__name,
jsonb_extract_path_text (al.jsonb, 'loan', 'metadata', 'updatedByUserId') AS loan_updated_by_user_id
FROM
folio_circulation.audit_loan AS al
The following expression will also get the third-level values:
SELECT
al.id as audit_loan_id,
al.jsonb #>> '{loan, status, name}' AS loan_status__name,
al.jsonb #>> '{loan, metadata, updatedByUserId}' AS loan_updated_by_user_id
FROM
folio_circulation.audit_loan AS al
...