Page History

...

Extracting "First Level" Data Fields 

Example: In the code below, 'patron_status' the data array of the folio_users.user table (screenshot below), the field 'active' (a boolean value showing patron status) is a "first level" jsonb field. In the JSON data array in this table, the main field 'status' has only one subfield (one possible value). The data field is extracted from the folio_users.users table because this table contains JSON data array where that field exists (this field is not available in the users__t "transformed" table). The "users" table is in the Partition subfolder under "users__t". The partition table only contains "current" versions of the records, while the "users__" table contains current and archive versions of the records. (You generally want just current records for queries.)hierarchical field. Other first-level fields include: id, barcode, metadata, personal, proxyFor, username, createdDate, departments, patronGroup, updatedDate and externalSystemId.

Image Added

You will see that some of the first-level fields have subordinate data in second-levels fields ("metadata" and "personal" fields have this). The following query will get the data out of first-level fields, when those fields don't have subordinate fields:

SELECT
     users.id AS user_id,
     jsonb_extract_path_text (users.jsonb, 'status') AS patron_status,
FROM folio_users.users

...

Extracting "Second Level" Data Fields

In the code example below, 'requester_last_name' and 'requester_first_name' are both "second-level" subfields in the JSON data array of (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.). Note that the folio_users.users__ table has 2 underscores at the end of its name. 

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__

...