Extracting "First Level" Data Fields
In the code below, 'item_status' is a "first level" jsonb field. In the blobJSON data array in this table, the main field 'status' has only one subfield (one possible value).
'Requester_last_name' and 'requester_first_name' are both second-level fields, because the main field 'personal' has many sub-fields, including first_name, middle_name, last_name, id, etc.SELECT
. 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). 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, '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" subfields in the JSON data array of the SELECT
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.id,
jsonb_extract_path_text (, 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__