Page History

...

• jsonb_extract_path   ---- this expression specifies the PATH necessary to get the last field entered in the path; the result is a json object and will be enclosed in double-quotes
• jsonb_extract_path_text   ---- this expression does the same thing as jsonb_extract_path, but returns a TEXT object (no quotes)
• jsonb_array_elements   ---- this expression is used for extracting values within an array object
• table_name_or_alias.jsonb #>> '{path_of_fields_in_the_hierarchy_separated_by_commas}' 
  • Example: audit_loan.jsonb #>> '{loan, metadata, updatedByUserId}'  -- this will get the value for "updatedByUserId"; you can use this shorthand statement to extract values in text objects or arrays

Extracting "First 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

...