*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
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
'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,
...
...
...
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
...
=
...
...
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:
