Page History

...

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.

Image Added

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 embedded arrays

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" element object (denoted by square brackets [ ] ) that is embedded in a hierarchy of object 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) . – "Values" is the array that – bottom level. This is array object contains fields we need: "id", "value" (which is the value name) and "default"

Here is what the custom_fields data json array looks like:We want to get the elements under the "values" array (denoted by square brackets):

The code to do that is:

SELECT

...

   jsonb_extract_path_text (values.jsonb,'id') AS value_id,  ----- note that in this case, extracts the "id" we're getting is a text element, not a UUIDelement from the values object that we created in the cross join below

   jsonb_extract_path_text (values.jsonb,'value') AS value_name
           name  ----- this extracts the "value" element from the values object that we careated in the cross join below
           - because "values" is a jsonb object (produced by the cross join below), we need to use json extract statements in "Select" to get the value id and the value name from "values"
           ------ NOTE: the shorthand way to get the same "id" and "value" extracts is by 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)

...

• 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  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 AScustom_fields_ordinality

RESULT:

Extracting an array that is at the top-most level in a 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.

...