*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
You will see that some of the first-level fields have subordinate data in second-levels fields ("metadata" and "personal" have subordinate fields). The following query will get the data out of first-level fields, when those fields don't have subordinate fields:
SELECT
...
users.id
...
AS
...
user_id,
...
jsonb_extract_path_text
...
(users.jsonb,
...
'status')
...
AS
...
patron_status
...
FROM
...
folio_users.users
The
...
following
...
expression
...
will
...
also
...
get
...
the
...
value
...
for
...
"status":
SELECT
...
users.id
...
AS
...
user_id,
...
users.jsonb
...
#>>
...
'{status}"
...
AS
...
patron_status
...
FROM
...
folio_users.users
Extracting "Second Level" Data Fields
...
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__
...