Extracting "First Level" Data Fields 

In the code below, 'item_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). 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 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__




  • No labels