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