Page History

...

• in the MARC record, field 300 is the physical description, and subfield "a" contains the page numbers or leaves "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

...

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

...

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. 

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

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:

...