Page History

...

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

...