Page History

...

In the example below, 'lastName' and 'firstName' are both "second-level" fields found under "personal." 

SELECT
     folio_ users.users_id AS user_.id,
     jsonb_extract_path_text (users.jsonb, 'personal', 'lastName') AS requestor_last_name,
     jsonb_extract_path_text (users.jsonb, 'personal', 'firstName') AS requestor_first_name
FROM
     folio_users.users

The following expression will also get "lastName" and "firstName":

SELECT
     folio_ users.users_id AS user_.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
     alaudit_loan.id as AS audit_loan_id,
     jsonb_extract_path_text (alaudit_loan.jsonb, 'loan', 'status', 'name') AS loan_status__name,
     jsonb_extract_path_text (alaudit_loan.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
     alaudit_loan.id as AS audit_loan_id,
     alaudit_loan.jsonb #>> '{loan, status, name}' AS loan_status__name,
     alaudit_loan.jsonb #>> '{loan, metadata, updatedByUserId}' AS loan_updated_by_user_id
FROM
     folio_circulation.audit_loan AS al

Extracting Arrays 

-- 1. Arrays embedded in text objects

...