Page History

--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
;

--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
;