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  ('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
        )

--EnterSelect adistinct locationrows codeto betweenreduce theduplicates quotethat marks
 may  result  from   --If left blank, the query will return all locations except 'serv,remo'
    )

--Select distinct rows to reduce duplicates that may result from the the joins
--and bringing in data fields from holdings records (in this case)
SELECT DISTINCT
    instanceipd.id AS instance_id,                          --Instance UUID--Instance UUID from the physical description table
    instance.jsonb#>>'{hrid}' AS ipd.instance_hrid,                   --ExtractInstance instance HRID from JSONBthe physical description table
    instanceinstext.jsonb#>>'{title}', AS  title,                    --Extract titleTitle from JSONB
    location__t.name AS the instance extension table
    ll.location_name,                    --Human-readable location name
    holdings_record__the.call_number,                      --Call number from the holdings extension table
    phys.jsonb#>>'{}' AS field_300,ipd.physical_description,            --Raw physical description text
    CASE
        --FullCheck textwhether of eachthe physical description (MARC 300 equivalent)

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

        --Check whether the description looks appearslike toit referrefers to pages or leaves
        AND (
            phys.jsonb#>>'{}'ipd.physical_description LIKE '%page%' 
            OR phys.jsonb#>>'{}'ipd.physical_description LIKE '%p.%' 
            OR phys.jsonb#>>'{}'ipd.physical_description LIKE '%leaves%' 
            OR phys.jsonb#>>'{}'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(phys.jsonb#>>'{}'ipd.physical_description, '\d{1,4}')::INT

        --Otherwise return null whenIf 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
    phys.ordinality AS record_sequenceipd.physical_description_ordinality         

--Start fromwith the instance extension table, whichto storesget JSONB records for bibliographic instancestitle-level data
FROM folio_inventoryderived.instance_ext AS instext

--LEFT JOIN LATERAL allows you to extract the physical descriptions array 
--for each instance record whether or not it has values in that array    --Join to holdings to bring in call number and location links
    LEFT JOIN LATERAL  folio_derived.holdings_ext AS he 
        jsonb_array_elements(ON instext.instance_id = he.instance_id
    
          jsonb_extract_path(instance.jsonb, 'physicalDescriptions')
    --Join to locations/libraries to get location names and location codes
    )LEFT WITH ORDINALITYJOIN folio_derived.locations_libraries AS phys(jsonb)ll 
        ON true
he.permanent_location_id = ll.location_id
    
    --Join to holdingsinstance tophysical bringdescriptions into callget numberthe andphysical locationdescription referencetext
    LEFT JOIN folio_inventoryderived.holdingsinstance_record__t physical_descriptions AS ipd 
        ON instext.instance._id = holdings_record__tipd.instance_id id

    --JoinApply tothe location table to get location names and codesfilter
WHERE 
    LEFT JOIN folio_inventory.location__t CASE
        ON holdings_record__t.permanent_location_id = location__t.id 

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

        --Otherwise, return only rows matching the specifiedspecific location code entered in the parameters CTE
        ELSE ll.location__t.code = (SELECT location_code_filter FROM parameters) 
    END
;

code = (SELECT location_code_filter FROM parameters)
    END
;

    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

;