Page History

...

• jsonb_extract_path   ---- this expression specifies the PATH necessary to get the last field entered in the path; the result is a json object and will display in double quotes
• jsonb_extract_path_text   ---- this expression does the same thing as jsonb_extract_path, but returns a TEXT object (displays without quotes)
• jsonb_array_elements   ---- this expression is used for extracting values within an array object
• the operator #>>. The format for using this operator is: table_name.jsonb #>> '{path_of_fields_in_the_hierarchy_separated_by_commas}' 
  • Example: audit_loan.jsonb #>> '{loan, metadata, updatedByUserId}'  -- this will get the value for "updatedByUserId" (the result is in Text format)

Extracting "First Level" Data Fields 

Example: In the data array of the folio_users.user table (screenshot below), the field 'active' (a boolean value showing patron status) is a "first level" hierarchical field. Other first-level fields include: id, barcode, metadata, personal, proxyFor, username, createdDate, departments, patronGroup, updatedDate and externalSystemId.

Image Modified

You will see that some of the first-level fields have subordinate data in second-levels fields ("metadata" and "personal" have subordinate fields). The following query will get the data out of first-level fields, when those fields don't have subordinate fields:

SELECT
     users.id AS user_id,
     jsonb_extract_path_text (users.jsonb, 'statusactive')::boolean AS patron_status  – "active" is a boolean value (true or false)
FROM folio_users.users

The following expression will also get the value for "statusactive":

SELECT
     users.id AS user_id,
     users.jsonb #>> '{statusactive}" AS patron_status
FROM folio_users.users

...

Extracting "Second Level" Data Fields

In the example below, 'lastName' and 'firstName' are both "second-level" fields found under "personal." 

SELECT
     users.id AS user_id,
     jsonb_extract_path_text (users.jsonb, 'personal', 'lastName') AS user_last_name,
     jsonb_extract_path_text (users.jsonb, 'personal', 'firstName') AS user_first_name
FROM
     folio_users.users

The following expression will also get "lastName" and "firstName":

SELECT
     users.id AS user_id,
     users.jsonb #>> '{personal, lastName}' AS user_last_name
     users.jsonb #>> '{personal, firstName}' AS user_first_name
FROM
     folio_users.users

Extracting "Third level" Data 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
     audit_loan.id AS audit_loan_id,
     jsonb_extract_path_text (audit_loan.jsonb, 'loan', 'status', 'name') AS loan_status_name,
     jsonb_extract_path_text (audit_loan.jsonb, 'loan', 'metadata', 'updatedByUserId') AS loan_updated_by_user_id
FROM
     folio_circulation.audit_loan

The following expression will also get the third-level values:

SELECT
     audit_loan.id AS audit_loan_id,
     audit_loan.jsonb #>> '{loan, status, name}' AS loan_status__name,
     audit_loan.jsonb #>> '{loan, metadata, updatedByUserId}' AS loan_updated_by_user_id
FROM
     folio_circulation.audit_loan

...

When extracting arrays embedded in text objects, it's usually best to use a "cross join lateral" statement in the FROM stanza. This cross join creates a "json object" that can then be further extracted in the Select stanza. Warning: the records retrieved through a cross join OR from using a nested json extract expression will only be those that have the array elements you're looking for; records without those elements will drop out.  See "Alternative to using cross joins" (that captures all the records) at end of this page.

It's also possible to NOT use a cross join at all, but to nest the json extract statements in one long expression in the Select stanza. However, you lose the ability to include the ordinality of the values extracted. See Summary at the end of this page ("Three ways to extract arrays").

...

Here is what the custom_fields jsonb data 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 created 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' array object at the end of the parentheses; we use this extract expression because "values" is an array
----- "jsonb_extract_path" looks for the path of elements to follow in order to get to the values array – the elements listed must be in the same hierarchical order as they appear in the table
----- the entire cross join statement produces a jsonb object which I named "values" (note: what you name this object is totally arbitrary - you could call it "stuff" if you felt like it – "stuff (jsonb)")

LEFT JOIN folio_users.custom_fields__t AS cft  

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

         ------ the custom_fields__t table has the custom fields id, the ref_id and the custom fields name (joining to this table is not required – I just wanted to include those fields in the results)

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

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

RESULT:

...

Because "departments" is an array, you still have to extract it with a cross join statement, as in the previous example. In the code below, I am using the shorthand method to extract the various elements:

SELECT

users.idASuser_id,

(users.jsonb #>> '{active}')::BOOLEANASpatron_status,

CASE

WHEN (users.jsonb #>> '{active}')::BOOLEAN = true

THEN'Active'

ELSE'Expired'

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

users.jsonb #>>'{personal, lastName}'ASlast_name, ------- this extrcts the last name from the "personal" object (second-level extraction)

users.jsonb #>>'{personal, firstName}'ASfirst_name, ------- this extracts the first name from the "personal' object (second-level extraction)

users.jsonb #>>'{username}'ASnet_id, ------ this extracts "username" from the table (first-level extraction)

users.jsonb #>>'{barcode}'ASuser_barcode, ------ this extracts "barcode" from the users table (first-level extraction)

depts.jsonb #>>'{}'ASdept_id, ----- this extracts the array values from the "depts" json object created through 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.nameASdepartment_name,

depts.ordinalityASdept_ordinality------ this displays the ordinality of the departments (the sequence of occurrence in the record)

FROMfolio_users.users

CROSSJOINLATERALjsonb_array_elements (jsonb_extract_path (users.jsonb,'departments'))

WITHordinalityASdepts (jsonb)

LEFTJOINfolio_users.departments__tASud-------- 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:

Image Modified

3. Extracting Arrays - Summary: Three ways to extract arrays

This illustrates three methods for extracting an embedded array from the folio_users.custom_fields table (example)

-- 1. No cross join -- just use triple-nested json extract statements in the Select clause

SELECT

cft.id AS custom_fields_id,

cft.ref_id,

cft.name AS custom_fields_name,

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')),'id') AS value_id,

jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values')),'value') AS value_name

FROM folio_users.custom_fields AS cf

LEFT JOIN folio_users.custom_fields__t AS cft

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

ORDER BY name, value_id, value_name

;

-- 2. Use a cross join and put the result in a single-level json extract statement in the Select clause

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,

jsonb_extract_path_text (values.jsonb,'value') AS value_name

FROM folio_users.custom_fields AS cf

CROSS JOIN LATERAL

jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values'))

AS values (jsonb)

LEFT JOIN folio_users.custom_fields__t AS cft

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

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

;

-- 3. Use a cross join but use the shortcut method in the Select clause to get the values in the array

SELECT

cft.id AS custom_fields_id,

cft.ref_id,

cft.name AS custom_fields_name,

values.jsonb #>> '{id}' AS value_id,

values.jsonb #>> '{value}' AS value_name

FROM folio_users.custom_fields AS cf

CROSS JOIN LATERAL

jsonb_array_elements (jsonb_extract_path (cf.jsonb, 'selectField', 'options', 'values'))

AS values (jsonb)

LEFT JOIN folio_users.custom_fields__t AS cft

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

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

---- can also use:   ORDER BY name, values.jsonb #>> '{id}', values.jsonb #>> '{value}'

4. Extracting Arrays - Alternatives to using cross joins, and how to prevent records without the array elements from dropping out

Cross join lateral queries will return only those records that have all the array elements you are trying to find. It's possible to use multiple cross join statements in the FROM stanza to get multiple array values (for example, subjects, contributors and languages from the instance table) - but the results will show only those records that have ALL the array elements specified in the cross joins. 

If you want to find records that have any or all of the particular array fields, AND show all the records that don't have any of those array fields, the better plan is to do a 2-part query.

• • First, create a query that has a nested jsonb extract statement (in the Select clause) for EACH of the arrays you're trying to extract (example below) – do not use cross joins
    • if you want to use cross joins, create one cross join query for each array field that you want to extract; don't combine all the extracts in one cross-join query
    • You will have to left-join each of the query results to the all-records query (created in the second step)
  • Second, create a query that finds all records from the source table (for example, get all instance ids from the folio_inventory.instance table), and left-join the results of the first query (or queries) to it

In this example, we're finding three array elements from the folio_inventory.instance table: contributors, languages, and subjects, then joining the result to a complete set of instance records.

WITH recs AS    ----- Get the array elements for contributors, languages and subjects using nested jsonb extract statements in the Select clause
(SELECT
    ii.id,
    ii.jsonb #>> '{hrid}' AS instance_hrid, ---- first level text extract
    ii.jsonb #>> '{title}' AS title, ---- first level text extract
    jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (ii.jsonb, 'contributors')),'name') AS contributors, -- contributors array extract (second-level extract)
    (jsonb_array_elements (jsonb_extract_path (ii.jsonb,'languages'))) #>>'{}' as languages, ----- languages array extract (top-level extract) – note the empty curly brackets
    jsonb_extract_path_text (jsonb_array_elements (jsonb_extract_path (ii.jsonb, 'subjects')),'value') as subjects ----- subject array extract (second-level extract)

FROM folio_inventory.instance AS ii
)

SELECT     ------- Get all the records from the instance__t table; left join the results of the first query that got the array elements
    instance__t.id,
    instance__t.hrid,
    instance__t.title,
    string_agg (distinct recs.contributors,' | ') as contributors_aggregated,
    string_agg (distinct recs.languages,' | ') as languages_aggregated,
    string_agg (distinct recs.subjects,' | ') as subjects_aggregated 

FROM folio_inventory.instance__t 
    LEFT JOIN recs
    ON instance__t.id = recs.id
    
GROUP BY 
    instance__t.id,
    instance__t.hrid,
    instance__t.title
;

;

RESULT:

Image Added

Use "left join lateral" to get all selected records from the instance table, whether they have data is the sepecified arrays or not:

The following example gets array values for six arrays in the Instance table, but shows the records that don't have values as well (accomplishes the same results as a cross join lateral with a link to the orginal instance table, more quickly and easily!)

select 
    instance.id,
    instance.jsonb#>>'{hrid}' as instance_hrid,
    holdings_record__t.hrid as holdings_hrid,
    instance.jsonb#>>'{title}' as title,
    string_agg (distinct editns.jsonb#>>'{}',' | ') as editions,
    string_agg (distinct pub.jsonb#>>'{place}',' | ') as publication_place,
    string_agg (distinct pub.jsonb#>>'{publisher}',' | ') as publisher,
    string_agg (distinct pub.jsonb#>>'{dateOfPublication}',' | ') as date_of_publication,
    string_agg (distinct subj.jsonb#>>'{value}',' | ') as subjects,
    string_agg (distinct notesext.jsonb#>>'{note}',' | ') as instance_notes

from folio_inventory.instance 
    left join lateral jsonb_array_elements (jsonb_extract_path (instance.jsonb,'editions')) as editns (jsonb)
    on true
    
    left join lateral jsonb_array_elements (jsonb_extract_path (instance.jsonb,'subjects')) as subj (jsonb)
    on true
    
    left join lateral jsonb_array_elements (jsonb_extract_path (instance.jsonb,'notes')) as notesext (jsonb)
    on true
    
    left join lateral jsonb_array_elements (jsonb_extract_path (instance.jsonb,'publication')) as pub (jsonb)
    on true

    left join folio_inventory.holdings_record__t  
    on instance.id = holdings_record__t.instance_id

group by 
    instance.id,
    instance.jsonb#>>'{hrid}',
    holdings_record__t.hrid,
    instance.jsonb#>>'{title}'
    ;

RESULT:

Image AddedResults using "left join lateral" in the From clause