Page History

The following jsonb extract expressions will be used to get values from the json data array of a table. They can be used singly or can be nested:

• jsonb_extract_path   ---- this expression specifies the PATH necessary to get the last field entered in the path; the data type for this result is "JSONB" and will show the result enclosed in double-quotes
• jsonb_extract_path_text   ---- this expression does the same thing as jsonb_extract_path, but returns a TEXT object (the result shows 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"

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

...