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. 

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:

Image Modified

Physical Descriptions array example from the instance table:

Image Added