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"

...

In the example below, 'requester_last_name' and 'requester_first_name' are both "second-level" (subordinate) fields in the folio_users.users table. They are found under the "first level " field called "personal." The "personal" field contains many additional "second-level" subfields in the JSON data array (e.g., first_name, middle_name, last_name, id, etc.). 

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__

...

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__

...