*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
...
- in the MARC record, field 300 is the physical description, subfield a (the 300 and subfield "a" contains the page numbers or leaves (note that the "300" field and the "a" subfield are both repeatable in a record). See See Marc21 format for bibliographic data for details.
- page numbers are also extracted from the The "folio_inventoryderived.instance table jsonb array and written to the instance_physical_descriptions" derived table is created from the instance physical descriptions array, but shows the entire 300 field, but without subfield indicators. See derivation code for this table. (Note that instance records that do not have a physical descriptions array will not be in the derived table.)
- NOTE: Some electronic resources have a 300 field subfield a showing page numbers, if the original form of the work was print material
...
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 NOT drop out, because a "left join lateral" function is used to extract the array values, or show NULL if there are no array values.
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
...
...
...
AS
...
instance_id,
...
...
instance.jsonb#>>'{hrid}'
...
AS
...
instance_hrid,
...
...
instance.jsonb#>>'{title}'
...
AS
...
title,
...
...
...
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
...
...
=
...
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:
Physical Descriptions array example from the instance table:
...