Page History

...

It is impossible to account for every variation of page number entry in your query

Basic code to extract physical description from the instance table

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.

SELECT 
    instance.id AS instance_id,
    instance.jsonb#>>'{hrid}' AS instance_hrid,
    instance.jsonb#>>'{title}' as title,
    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.%')
        THEN substring (phys.jsonb#>>'{}','\d{1,4}')::INT
        ELSE NULL
    END AS page_count_estimate

FROM folio_inventory.instance
LEFT JOIN LATERAL jsonb_array_elements (jsonb_extract_path (instance.jsonb,'physicalDescriptions')) AS phys (jsonb)
ON TRUE
;

Result:

Image Added

Basic Code Template for Page Numbers

...