Extracting "First Level" Data Fields
In the code below, 'item_status' is a first level jsonb field. In the blob, 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.
SELECT
folio_users.users__.id,
jsonb_extract_path_text (folio_users.users__ .jsonb, 'status') AS item_status,
FROM folio_users.users__
Extracting "Second Level" Data Fields
In the code below, 'requester_last_name' and 'requester_first_name' are both second-level fields, because the main field 'personal' has many sub-fields in the JSON data array, including first_name, middle_name, last_name, id, etc.
SELECT
folio_users.users__.id,
jsonb_extract_path_text (folio_users.users__ .jsonb, 'status') AS item_status,
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__