Page Numbers Without Tears

Why would you need to include number of pages in a report?

• physical collections inventory 
• planning collection shifts, especially when books are not located together and direct measurement (linear feet) is not feasible
• estimating the number of trucks needed to move materials to the Annex or another location
• doing tracers; height of volume and number of pages give an approximation of the size of the item being searched for
• counting the number of exposures needed to scan or copy a book

Where are page number data fields stored?

• in the MARC record, field 300 is the physical description, and subfield "a" contains the page numbers or "leaves" (note that the "300" field and the "a" subfield are both repeatable in a record). See Marc21 format for bibliographic data for details.
• The "folio_derived.instance_physical_descriptions" derived table is created from the instance physical descriptions array, but shows the entire 300 field, without subfield indicators. See derivation code for this table. (Note that instance records that do not have a physical descriptions array will not be in the derived table.)
• NOTE: Some electronic resources have a 300 field showing page numbers, if the original form of the work was print material

The example below shows an online resource with "236 pages." If your query is only pulling items from a physical location, you should not get any electronic resources. However, it is good to be aware that electronic resources may have physical descritptions inherited from the source material. 

Note that physical descriptions may be entered in many different ways and have a high degree of complexity; parsing out the page count through regular expressions (the methods used here) will never be completely accurate.

Code to extract the physical description using derived tables

This method finds an estimated page count from the instance_physical_descriptions derived table. Because the physical description field of the instance record is a text-entry field that has no enforced data entry format, the parsing statement will not yield highly accurate results. It works in cases where a page count number is followed by "p." or "pages" or "leaves" or "l." and when the first number in the description field (reading from left to right) is the actual number of pages or leaves and not some other value. 

WITH parameters AS 
    (SELECT 
    'law,ref' AS location_code_filter -- enter a location code; if left blank, the query will get all locations except for 'serv,remo'
    )

SELECT distinct
    ipd.instance_id,
    ipd.instance_hrid,
    instext.title,
    ll.location_name,
    he.call_number,
    ipd.physical_description,
    CASE
        WHEN substring (ipd.physical_description,'\d{1,4}') IS NOT NULL
        AND (ipd.physical_description LIKE '%page%' 
          OR ipd.physical_description LIKE '%p.%' 
          OR ipd.physical_description LIKE '%leaves%' 
          OR ipd.physical_description LIKE '% l.%')
        THEN substring (ipd.physical_description,'\d{1,4}')::INT
        ELSE NULL
    END AS page_count_estimate,
    ipd.physical_description_ordinality

FROM folio_derived.instance_ext AS instext
    LEFT JOIN folio_derived.holdings_ext AS he 
    ON instext.instance_id = he.instance_id
    
    LEFT JOIN folio_derived.locations_libraries AS ll 
    ON he.permanent_location_id = ll.location_id
    
    LEFT JOIN folio_derived.instance_physical_descriptions AS ipd 
    ON instext.instance_id = ipd.instance_id

WHERE 
    CASE WHEN (SELECT location_code_filter FROM parameters) =''
    THEN ll.location_code != 'serv,remo' 
    ELSE ll.location_code = (SELECT location_code_filter FROM parameters) 
    END
;

Result:

Code to extract the physical description from the instance table using a data array extraction function

This method extracts the "physical descriptions" array from the instance record; records that do not have a physical descriptions array will NOT drop out, because the "left join lateral" function used in this query will keep all instance records in the results set, including those that do not have array values for physical descriptions. 

As stated above, because the physical description field of the instance record is a text-entry field that has no enforced data entry format, the parsing statement will not yield highly accurate results. It works in cases where a page count number is followed by "p." or "pages" or "leaves" or "l." and when the first number in the description field (reading from left to right) is the actual number of pages or leaves, and not some other value. 

-- enter a location code between the quote marks below
--if left blank, the query will get all locations except for 'serv,remo'

WITH parameters AS 
    (SELECT 
    'law,ref' AS location_code_filter 
    )

SELECT distinct
    instance.id AS instance_id,
    instance.jsonb#>>'{hrid}' AS instance_hrid,
    instance.jsonb#>>'{title}' AS title,
    location__t.name AS location_name,
    holdings_record__t.call_number,
    phys.jsonb#>>'{}' AS field_300,
    CASE
        WHEN substring (phys.jsonb#>>'{}','\d{1,4}') IS NOT NULL
        AND (phys.jsonb#>>'{}' LIKE '%page%' OR phys.jsonb#>>'{}' LIKE '%p.%' OR phys.jsonb#>>'{}' LIKE '%leaves%' OR phys.jsonb#>>'{}' LIKE '% l.%')
        THEN substring (phys.jsonb#>>'{}','\d{1,4}')::INT
        ELSE NULL
    END AS page_count_estimate,
    phys.ordinality AS record_sequence_ordinality

FROM folio_inventory.instance
    LEFT JOIN LATERAL jsonb_array_elements (jsonb_extract_path (instance.jsonb,'physicalDescriptions')) WITH ordinality AS phys (jsonb)
    ON true
    
    LEFT JOIN folio_inventory.holdings_record__t 
    ON instance.id = holdings_record__t.instance_id 
    
    LEFT JOIN folio_inventory.location__t 
    ON holdings_record__t.permanent_location_id = location__t.id 

WHERE 
    CASE WHEN (SELECT location_code_filter FROM parameters) =''
    THEN location__t.code != 'serv,remo' 
    ELSE location__t.code = (SELECT location_code_filter FROM parameters) 
    END 
;

Result:

Physical Descriptions array example from the instance table: