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.
  • What is a leaf? A leaf is a piece of paper printed on one side. A "page" is a piece of paper printed on 2 sides (e.g. 100 pages is 50 pieces of paper printed on 2 sides)
  • 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 descriptions 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.


--define a parameters Common Table Expression (subquery or CTE) so the location filter can be easily changed in one place
--enter a location code between the quote marks below ('law,ref' is just an example; replace this text with your own location code)
--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 rows to reduce duplicates that may result from the joins
--and bringing in data fields from holdings records (in this case)
SELECT DISTINCT
    ipd.instance_id,                     --Instance UUID from the physical description table
    ipd.instance_hrid,                   --Instance HRID from the physical description table
    instext.title,                       --Title from the instance extension table
    ll.location_name,                    --Human-readable location name
    he.call_number,                      --Call number from the holdings extension table
    ipd.physical_description,            --Raw physical description text
    CASE
        --Check whether the physical description contains a 1- to 4-digit number
        WHEN substring(ipd.physical_description, '\d{1,4}') IS NOT NULL
        --Check whether the description looks like it refers to pages or leaves
        AND (
            ipd.physical_description LIKE '%page%' 
            OR ipd.physical_description LIKE '%p.%' 
            OR ipd.physical_description LIKE '%leaves%' 
            OR ipd.physical_description LIKE '% l.%'
        )
        --If both conditions are true, extract the first 1- to 4-digit number
        --and cast it to an integer as the page count estimate
        THEN substring(ipd.physical_description, '\d{1,4}')::INT
        --If no usable page or leaf count is found, return null
        ELSE NULL
    END AS page_count_estimate,
    --ordinality shows the order of the physical description entry from the record 
    --in cases where there is more than one
    ipd.physical_description_ordinality  

--Start with the instance extension table to get title-level data
FROM folio_derived.instance_ext AS instext

    --Join to holdings to bring in call number and location links
    LEFT JOIN folio_derived.holdings_ext AS he 
        ON instext.instance_id = he.instance_id
    
    --Join to locations/libraries to get location names and location codes
    LEFT JOIN folio_derived.locations_libraries AS ll 
        ON he.permanent_location_id = ll.location_id
    
    --Join to instance physical descriptions to get the physical description text
    LEFT JOIN folio_derived.instance_physical_descriptions AS ipd 
        ON instext.instance_id = ipd.instance_id

--Apply the location filter
WHERE 
    CASE
        --If the location filter parameter is blank, return all locations except 'serv,remo'
        WHEN (SELECT location_code_filter FROM parameters) = ''
            THEN ll.location_code != 'serv,remo'
        --Otherwise, return only the specific location code entered in the parameters CTE
        ELSE ll.location_code = (SELECT location_code_filter FROM parameters)
    END
;



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. 


--define a parameters Common Table Expression (subquery or CTE) so the location filter can be easily changed in one place
--enter a location code between the quote marks below ('law,ref' is just an example; replace this text with your own location code)
--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 rows to reduce duplicates that may result from the joins
--and bringing in data fields from holdings records (in this case)
SELECT DISTINCT
    ipd.instance_id,                     --Instance UUID from the physical description table
    ipd.instance_hrid,                   --Instance HRID from the physical description table
    instext.title,                       --Title from the instance extension table
    ll.location_name,                    --Human-readable location name
    he.call_number,                      --Call number from the holdings extension table
    ipd.physical_description,            --Raw physical description text
    CASE
        --Check whether the physical description contains a 1- to 4-digit number
        WHEN substring(ipd.physical_description, '\d{1,4}') IS NOT NULL
        --Check whether the description looks like it refers to pages or leaves
        AND (
            ipd.physical_description LIKE '%page%' 
            OR ipd.physical_description LIKE '%p.%' 
            OR ipd.physical_description LIKE '%leaves%' 
            OR ipd.physical_description LIKE '% l.%'
        )
        --If both conditions are true, extract the first 1- to 4-digit number
        --and cast it to an integer as the page count estimate
        THEN substring(ipd.physical_description, '\d{1,4}')::INT
        --If no usable page or leaf count is found, return null
        ELSE NULL
    END AS page_count_estimate,
    --ordinality shows the order of the physical description entry from the record 
    --in cases where there is more than one
    ipd.physical_description_ordinality  

--Start with the instance extension table to get title-level data
FROM folio_derived.instance_ext AS instext

    --Join to holdings to bring in call number and location links
    LEFT JOIN folio_derived.holdings_ext AS he 
        ON instext.instance_id = he.instance_id
    
    --Join to locations/libraries to get location names and location codes
    LEFT JOIN folio_derived.locations_libraries AS ll 
        ON he.permanent_location_id = ll.location_id
    
    --Join to instance physical descriptions to get the physical description text
    LEFT JOIN folio_derived.instance_physical_descriptions AS ipd 
        ON instext.instance_id = ipd.instance_id

--Apply the location filter
WHERE 
    CASE
        --If the location filter parameter is blank, return all locations except 'serv,remo'
        WHEN (SELECT location_code_filter FROM parameters) = ''
            THEN ll.location_code != 'serv,remo'
        --Otherwise, return only the specific location code entered in the parameters CTE
        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. 


--define a parameters Common Table Expression (subquery or CTE) so the location filter can be easily changed in one place
--enter a location code between the quote marks below ('law,ref' is just an example; replace this text with your location code)
--if left blank, the query will get all locations except for 'serv,remo'

WITH parameters AS 
    (
    SELECT 
        'law,ref' AS location_code_filter
        --Enter a location code between the quote marks
        --If left blank, the query will return all locations except 'serv,remo'
    )

--Select distinct rows to reduce duplicates that may result from the joins
--and bringing in data fields from holdings records (in this case)
SELECT DISTINCT
    instance.id AS instance_id,                          --Instance UUID
    instance.jsonb#>>'{hrid}' AS instance_hrid,           --Extract instance HRID from JSONB
    instance.jsonb#>>'{title}' AS title,                  --Extract title from JSONB
    location__t.name AS location_name,                   --Human-readable location name
    holdings_record__t.call_number,                      --Call number from holdings
    phys.jsonb#>>'{}' AS field_300,                      --Full text of each physical description (MARC 300 equivalent)

    CASE
        --Check whether the physical description contains a 1- to 4-digit number
        WHEN substring(phys.jsonb#>>'{}', '\d{1,4}') IS NOT NULL

        --Check whether the description appears to refer to pages or leaves
        AND (
            phys.jsonb#>>'{}' LIKE '%page%' 
            OR phys.jsonb#>>'{}' LIKE '%p.%' 
            OR phys.jsonb#>>'{}' LIKE '%leaves%' 
            OR phys.jsonb#>>'{}' LIKE '% l.%'
        )

        --If both conditions are true, extract the first number and cast to integer
        THEN substring(phys.jsonb#>>'{}', '\d{1,4}')::INT

        --Otherwise return null when no usable page count is found
        ELSE NULL
    END AS page_count_estimate,
   
  --ordinality shows the order of the physical description entry from the record 
  --in cases where there is more than one
    phys.ordinality AS record_sequence_ordinality         

--Start from the instance table, which stores JSONB records for bibliographic instances
FROM folio_inventory.instance

--LEFT JOIN LATERAL allows you to extract the physical descriptions array 
--for each instance record whether or not it has values in that array
    LEFT JOIN LATERAL 
        jsonb_array_elements(
            jsonb_extract_path(instance.jsonb, 'physicalDescriptions')
        ) WITH ORDINALITY AS phys(jsonb)
        ON true

    --Join to holdings to bring in call number and location reference
    LEFT JOIN folio_inventory.holdings_record__t 
        ON instance.id = holdings_record__t.instance_id 

    --Join to location table to get location names and codes
    LEFT JOIN folio_inventory.location__t 
        ON holdings_record__t.permanent_location_id = location__t.id 

--Apply the location filter based on the parameter value
WHERE 
    CASE 
        --If the parameter is blank, return all locations except 'serv,remo'
        WHEN (SELECT location_code_filter FROM parameters) = ''
            THEN location__t.code != 'serv,remo' 

        --Otherwise return only rows matching the specified location code
        ELSE location__t.code = (SELECT location_code_filter FROM parameters) 
    END
;


Result:


Physical Descriptions array example from the instance table:





  • No labels