*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
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__
...
This is the code to extract them. Note that the fields listed within the parentheses must be in the same hierarchical order as they appear in the json data.
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
Extracting Arrays
-- 1. Embedded in text objects
...