You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 19 Next »


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__


Extracting "Third level" Data Fields

Some tables, such as the folio_circulation.audit_loan table, have third-level data fields that are comprised of text objects (they can also have array objects, but that is covered in the next section – note: array objects can appear at any level within the json data). The json statement for extracting thrid-level text fields is exactly the same as for first- and second-level data fields, except there are three fields listed in the parentheses, instead of just one or two. 

In this example, "name" and "updatedByUserId" are third-level fields.


This is the code to extract them. Note that the fields listed within the parentheses must be in the same hierarchical order as they appear in the json data.

SELECT
     al.id as audit_loan_id,
     jsonb_extract_path_text (al.jsonb, 'loan', 'status', 'name') AS loan_status__name,
     jsonb_extract_path_text (al.jsonb, 'loan', 'metadata', 'updatedByUserId') AS loan_updated_by_user_id
FROM
     folio_circulation.audit_loan AS al

Extracting Arrays 

-- 1. Embedded in text objects

In the previous examples, the elements being extracted were "text objects" within the table's JSON array, and were denoted by curly brackets { }. In this next example, we need to extract multiple values from an "array" object (denoted by square brackets [ ] ) that is embedded in a hierarchy of text elements. This calls for nested json extract statements, a strucure very similar to what we used in Access with nested "GetField" and "GetXXXBLOB" functions.

  • Nested extract statements are applied from the outside in. So the left-most json statement applies to the right-most element in the parentheses
  • The entire nested extract statement is cast as a "jsonb" object
  • That jsonb object is then used in the select stanza to get the needed fields within it (by using another json extract statement on it)

The example below uses the folio_users.custom_fields table, which has embedded array elements ("id","value" and "default") nested within a hierarchy of objects: 

  • "selectField" (text object) – topmost level
  • "options" (text object) – middle level
  • "values" (array object) – bottom level. This array object contains the fields we need: "id" and "value"

Here is what the custom_fields json array looks like:



The code to do that is:

SELECT

   cft.id AS custom_fields_id,

   cft.ref_id,

   cft.name AS custom_fields_name,

   jsonb_extract_path_text (values.jsonb,'id') AS value_id,  ----- this extracts the "id" element from the values object that we created in the cross join below

   jsonb_extract_path_text (values.jsonb,'value') AS value_name  ----- this extracts the "value" element from the values object that we careated in the cross join below
          
           ------ NOTE: the two json extract statements above can be replaced by a shorthand formula, using the "#>>" operator: 
                      values.jsonb #>> '{id}'
                      values.jsonb #>> '{value}'


FROM folio_users.custom_fields AS cf

CROSS JOIN LATERAL jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')) AS values (jsonb)
----- "jsonb_array_elements" works on the 'values' element in the parentheses; we use this extract function because "values" is an array
----- "jsonb_extract_path" works on the "selectField" and "options" objects – the order of objects listed must be in the same hierarchical order as they appear in the table
----- the entire cross join statement produces a jsonb object that I named named "values" (note: what you name this object is totally arbitrary - you could call it "stuff" if you felt like it)


LEFT JOIN folio_users.custom_fields__t AS cft  

ON jsonb_extract_path_text (cf.jsonb,'id')::UUID = cft.id

         ------ I'm joining to the custom_fields__t table to show the custom fields id, the ref_id and the custom_fields name (this is not necessary, it's just a convenience)


ORDER BY name, jsonb_extract_path_text (values.jsonb,'id'), jsonb_extract_path_text (values.jsonb,'value')


  • Final note: if you want to include "ordinality" as part of your results – this is the order in which the extracted values appear in the source record – you would insert "WITH ordinality" before "AS values (jsonb)":
           CROSS JOIN LATERAL jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')) WITH ordinality AS values (jsonb)

Then you would add a line in the Select stanza to show the ordinality: 
       values.ordinality AS custom_fields_ordinality


RESULT:


Extracting Arrays 

-- 2. Array is at the top-most hierarchical level in a table

Sometimes you want to extract an array that is at the top-most hierarchical level of a table – that is, it is not embedded within any other object. An example of this is "departments" element in the folio_users.users table:


Because "departments" is an array, you still have to extract it with a cross join statement, as in the previous example. Then you need to display the array elements using the "shortcut" method in the Select stanza.


SELECT

      users.id as user_id,

      jsonb_extract_path_text (users.jsonb,'active')::BOOLEAN as patron_status,  ----- this gets the patron status, which is a boolean value; note that I have to cast the extract as "boolean"

      CASE

            WHEN jsonb_extract_path_text (users.jsonb,'active')::BOOLEAN = true

            THEN 'Active'

            ELSE 'Expired'

            END AS patron_status_name,  -------- this Case When statement is just for displaying the boolean value as "Active" or "Expired"

      jsonb_extract_path_text (users.jsonb,'personal','lastName') as last_name,  ------- this extrcts the last name from the "personal" object (second-level extraction)

      jsonb_extract_path_text (users.jsonb,'personal','firstName') as first_name,  ------- this extracts the first name from the "personal' object (second-level extraction)

      jsonb_extract_path_text (users.jsonb,'username') as net_id,  ------ this extracts "username" from the table (first-level extraction)

      jsonb_extract_path_text (users.jsonb,'barcode') as user_barcode,  ------ this extracts "barcode" from the users table (first-level extraction)

      depts.jsonb #>> '{}' as dept_id,   ----- this is the shortcut method of displaying the values from the "depts" json object created by the cross join; note that the curly brackets are empty; this is because there is no tag for the values, it's just the values themselves. If there were a tag for the values, the tag name would go in the curly brackets

      ud.name as department_name,

      depts.ordinality as dept_ordinality   ------ this displays the ordinality of the departments 


FROM folio_users.users

      CROSS JOIN LATERAL  jsonb_array_elements (jsonb_extract_path (users.jsonb,'departments'))

      WITH ordinality AS depts (jsonb)


LEFT JOIN folio_users.departments__t AS ud   -------- I am joining to the the departments__t table to get the department name

ON (depts.jsonb #>> '{}')::UUID = ud.id  -------- note that I'm using the shortcut expression to join to the id in the departments__t table


RESULT:

  • No labels