Page History

...

WITH parameters AS 
    (SELECT 
    'law,ref' AS location_code_filter 
    )

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 
;

...