Page History

...

This method does not use the problematic marc__t table. This code extracts the "physical descriptions" array from the instance record; records that do not have a physical descriptions array will not drop out, because a left join lateral function is used. Note that when you choose a specific physical location, the electronic resources will not be included. 

WITH parameters as (

SELECT 

/*Enter the location code for the library between the apostrophes, e.g. 'olin' , 'law,res' /*

...

(SELECT 
'olin'::VARCHAR as location_code_filter
),

 instance.id AS instance_id,
    instance.jsonb#>>'{hrid}' AS instance_hrid,
    instance.jsonb#>>'{title}' as title,
    location__t.code,
    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

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  

Result:

Image Removed

Basic Code Template for Page Numbers

Here is some code with explanatory comments to serve as a basic template for adding data showing the number of pages in the physical items you have selected in your query.

-- MARC 300 subfield a rows (can have multiple per instance)
number_of_pages_raw AS (
SELECT
m.instance_hrid,
m.content AS physical_description_300a,
-- numeric page count
CASE
WHEN substring(m.content from '\d{1,4}') IS NOT NULL
AND substring(m.content from '\d{1,4}')::int <= 3000
THEN substring(m.content from '\d{1,4}')::int
ELSE NULL
END AS page_count_estimate
FROM folio_source_record.marc__t AS m

LEFT JOIN folio_source_record.records_lb AS rec
ON m.instanceinventory.location__t  on holdings_record__t.permanent_location_id = rec.external_idlocation__t.id  

WHERE m.field = '300'
AND m.sf = 'a'
AND rec.state = 'ACTUAL'
),
-- group to one row per instance_hrid
number_of_pages AS (
SELECT
instance_hrid,
MIN(physical_description_300a) AS physical_description_300a,
MIN(page_count_estimate) AS page_count_estimate
FROM number_of_pages_raw
GROUP BY instance_hrid
),
CASE WHEN 

   (SELECT location_code_filter FROM parameters IS NULL) 
THEN location__t.code !=‘serv,remo’ 
ELSE location__t.code = (SELECT location_code_filter from parameters)
END

;

Result:

Image Added