*** This version of Confluence is for testing only and contains a copy of content from June 29th 2026. No changes will be preserved. ***
Why would you need to include number of pages in a report?
- physical collections inventory
- planning collection shifts, especially when books are not located together and direct measurement (linear feet) is not feasible
- estimating the number of trucks needed to move materials to the Annex or another location
- doing tracers; height of volume and number of pages give an approximation of the size of the item being searched for
- counting the number of exposures needed to scan or copy a book
Where are page number data fields stored?
- in the MARC record , field 300 is the physical description, 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 Marc21 format for bibliographic data for details.
- What is a leaf? A leaf is a piece of paper printed on one side. A "page" is a piece of paper printed on 2 sides (e.g. 100 pages is 50 pieces of paper printed on 2 sides)
- The "folio_derived.instance_physical_descriptions" derived table is created from the instance physical descriptions array, but shows the entire 300 field, 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 showing page numbers, if the original form of the work was print material
The example below shows an online resource with "236 pages." If your query is only pulling items from a physical location, you should not get any electronic resources. However, it is good to be aware that electronic resources may have physical descritptions inherited from the source material.
Note that physical descriptions may be entered in many different ways and have a high degree of complexity; parsing out the page count through regular expressions (the methods used here) will never be completely accurate.
Code to extract the physical description using derived tables
This method finds an estimated page count from the instance_physical_descriptions derived table. Because the physical description field of the instance record is a text-entry field that has no enforced data entry format, the parsing statement will not yield highly accurate results. It works in cases where a page count number is followed by "p." or "pages" or "leaves" or "l." and when the first number in the description field (reading from left to right) is the actual number of pages or leaves and not some other value.
...
--define
...
a
...
parameters
...
Common
...
Table
...
Expression
...
(subquery
...
or
...
CTE)
...
so
...
the
...
location
...
filter
...
can
...
be
...
easily
...
changed
...
in
...
one
...
place
--enter
...
a
...
location
...
code
...
between
...
the
...
quote
...
marks
...
below
--if
...
left
...
blank,
...
the
...
query
...
will
...
get
...
all
...
locations
...
except
...
for
...
'serv,remo'
...
WITH
...
parameters
...
AS
...
...
(
...
...
SELECT
...
...
...
'law,ref'
...
AS
...
location_code_filter
...
...
)
...
--Select
...
distinct
...
rows
...
to
...
reduce
...
duplicates
...
that
...
may
...
result
...
from
...
the
...
joins
--and
...
bringing
...
in
...
data
...
fields
...
from
...
holdings
...
records
...
(in
...
this
...
case)
SELECT
...
DISTINCT
...
...
ipd.instance_id,
...
...
...
...
...
...
...
...
...
...
...
--Instance
...
UUID
...
from
...
the
...
physical
...
description
...
table
...
...
ipd.instance_hrid,
...
...
...
...
...
...
...
...
...
...
--Instance
...
HRID
...
from
...
the
...
physical
...
description
...
table
...
...
instext.title,
...
...
...
...
...
...
...
...
...
...
...
...
--Title
...
from
...
the
...
instance
...
extension
...
table
...
...
ll.location_name,
...
...
...
...
...
...
...
...
...
...
--Human-readable
...
location
...
name
...
...
he.call_number,
...
...
...
...
...
...
...
...
...
...
...
--Call
...
number
...
from
...
the
...
holdings
...
extension
...
table
...
...
ipd.physical_description,
...
...
...
...
...
...
--Raw
...
physical
...
description
...
text
...
...
CASE
...
...
...
...
--Check
...
whether
...
the
...
physical
...
description
...
contains
...
a
...
1-
...
to
...
4-digit
...
number
...
...
...
...
WHEN
...
substring(ipd.physical_description,
...
'\d{1,4}')
...
IS
...
NOT
...
NULL
...
...
...
...
--Check
...
whether
...
the
...
description
...
looks
...
like
...
it
...
refers
...
to
...
pages
...
or
...
leaves
...
...
...
...
AND
...
(
...
...
...
...
...
...
ipd.physical_description
...
LIKE
...
'%page%'
...
...
...
...
...
...
OR
...
ipd.physical_description
...
LIKE
...
'%p.%'
...
...
...
...
...
...
OR
...
ipd.physical_description
...
LIKE
...
'%leaves%'
...
...
...
...
...
...
OR
...
ipd.physical_description
...
LIKE
...
'%
...
l.%'
...
...
...
...
)
...
...
...
...
--If
...
both
...
conditions
...
are
...
true,
...
extract
...
the
...
first
...
1-
...
to
...
4-digit
...
number
...
...
...
...
--and
...
cast
...
it
...
to
...
an
...
integer
...
as
...
the
...
page
...
count
...
estimate
...
...
...
...
THEN
...
substring(ipd.physical_description,
...
'\d{1,4}')::INT
...
...
...
...
--If
...
no
...
usable
...
page
...
or
...
leaf
...
count
...
is
...
found,
...
return
...
null
...
...
...
...
ELSE
...
NULL
...
...
END
...
AS
...
page_count_estimate,
...
...
--ordinality
...
shows
...
the
...
order
...
of
...
the
...
physical
...
description
...
entry
...
from
...
the
...
record
...
...
--in
...
cases
...
where
...
there
...
is
...
more
...
than
...
one
...
...
ipd.physical_description_ordinality
...
...
--Start
...
with
...
the
...
instance
...
extension
...
table
...
to
...
get
...
title-level
...
data
FROM
...
folio_derived.instance_ext
...
AS
...
instext
...
...
...
--Join
...
to
...
holdings
...
to
...
bring
...
in
...
call
...
number
...
and
...
location
...
links
...
...
LEFT
...
JOIN
...
folio_derived.holdings_ext
...
AS
...
he
...
...
...
...
ON
...
instext.instance_id
...
=
...
he.instance_id
...
...
...
--Join
...
to
...
locations/libraries
...
to
...
get
...
location
...
names
...
and
...
location
...
codes
...
...
LEFT
...
JOIN
...
folio_derived.locations_libraries
...
AS
...
ll
...
...
...
...
ON
...
he.permanent_location_id
...
=
...
ll.location_id
...
...
...
--Join
...
to
...
instance
...
physical
...
descriptions
...
to
...
get
...
the
...
physical
...
description
...
text
...
...
LEFT
...
JOIN
...
folio_derived.instance_physical_descriptions
...
AS
...
ipd
...
...
...
...
ON
...
instext.instance_id
...
=
...
ipd.instance_id
...
--Apply
...
the
...
location
...
filter
WHERE
...
...
CASE
...
...
...
...
--If
...
the
...
location
...
filter
...
parameter
...
is
...
blank,
...
return
...
all
...
locations
...
except
...
'serv,remo'
...
...
...
...
WHEN
...
(SELECT
...
location_code_filter
...
FROM
...
parameters)
...
=
...
''
...
...
...
...
...
...
THEN
...
ll.location_code
...
!=
...
'serv,remo'
...
...
...
...
--Otherwise,
...
return
...
only
...
the
...
specific
...
location
...
code
...
entered
...
in
...
the
...
parameters
...
CTE
...
...
...
...
ELSE
...
ll.location_code
...
=
...
(SELECT
...
location_code_filter
...
FROM
...
parameters)
...
...
END
;
...
Result:
...
...
...
Code to
...
extract
...
the
...
physical
...
description
...
from
...
the
...
instance
...
table
...
using
...
a
...
data
...
array
...
extraction
...
function
...
This
...
method
...
extracts
...
the
...
"physical
...
descriptions"
...
array
...
from
...
the
...
instance
...
record;
...
records
...
that
...
do
...
not
...
have
...
a
...
physical
...
descriptions
...
array
...
will
...
NOT
...
drop
...
out,
...
because
...
the
...
"left
...
join
...
lateral"
...
function
...
used
...
in
...
this
...
query
...
will
...
keep
...
all
...
instance
...
records
...
in
...
the
...
results
...
set,
...
including
...
those
...
that
...
do
...
not
...
have
...
array
...
values
...
for
...
physical
...
descriptions.
...
As
...
stated
...
above,
...
because
...
the
...
physical
...
description
...
field
...
of
...
the
...
instance
...
record
...
is
...
a
...
text-entry
...
field
...
that
...
has
...
no
...
enforced
...
data
...
entry
...
format,
...
the
...
parsing
...
statement
...
will
...
not
...
yield
...
highly
...
accurate
...
results.
...
It
...
works
...
in
...
cases
...
where
...
a
...
page
...
count
...
number
...
is
...
followed
...
by
...
"p."
...
or
...
"pages"
...
or
...
"leaves"
...
or
...
"l."
...
and
...
when
...
the
...
first
...
number
...
in
...
the
...
description
...
field
...
(reading
...
from
...
left
...
to
...
right)
...
is
...
the
...
actual
...
number
...
of
...
pages
...
or
...
leaves,
...
and
...
not
...
some
...
other
...
value.
...
--define
...
a
...
parameters
...
Common
...
Table
...
Expression
...
(subquery
...
or
...
CTE)
...
so
...
the
...
location
...
filter
...
can
...
be
...
easily
...
changed
...
in
...
one
...
place
--enter
...
a
...
location
...
code
...
between
...
the
...
quote
...
marks
...
below
--if
...
left
...
blank,
...
the
...
query
...
will
...
get
...
all
...
locations
...
except
...
for
...
'serv,remo'
...
WITH
...
parameters
...
AS
...
...
(
...
...
SELECT
...
...
...
...
'law,ref'
...
AS
...
location_code_filter
...
...
...
...
--Enter
...
a
...
location
...
code
...
between
...
the
...
quote
...
marks
...
...
...
...
--If
...
left
...
blank,
...
the
...
query
...
will
...
return
...
all
...
locations
...
except
...
'serv,remo'
...
)
--Select
...
distinct
...
rows
...
to
...
reduce
...
duplicates
...
that
...
may
...
result
...
from
...
the
...
joins
--and
...
bringing
...
in
...
data
...
fields
...
from
...
holdings
...
records
...
(in
...
this
...
case)
SELECT
...
DISTINCT
...
...
...
AS
...
instance_id,
...
...
...
...
...
...
...
...
...
...
...
...
...
--Instance
...
UUID
...
...
instance.jsonb#>>'{hrid}'
...
AS
...
instance_hrid,
...
...
...
...
...
...
--Extract
...
instance
...
HRID
...
from
...
JSONB
...
...
instance.jsonb#>>'{title}'
...
AS
...
title,
...
...
...
...
...
...
...
...
...
--Extract
...
title
...
from
...
JSONB
...
...
...
AS
...
location_name,
...
...
...
...
...
...
...
...
...
...
--Human-readable
...
location
...
name
...
...
holdings_record__t.call_number,
...
...
...
...
...
...
...
...
...
...
...
--Call
...
number
...
from
...
holdings
...
...
phys.jsonb#>>'{}'
...
AS
...
field_300,
...
...
...
...
...
...
...
...
...
...
...
--Full
...
text
...
of
...
each
...
physical
...
description
...
(MARC
...
300
...
equivalent)
...
...
...
CASE
...
...
...
...
--Check
...
whether
...
the
...
physical
...
description
...
contains
...
a
...
1-
...
to
...
4-digit
...
number
...
...
...
...
WHEN
...
substring(phys.jsonb#>>'{}',
...
'\d{1,4}')
...
IS
...
NOT
...
NULL
...
...
...
...
...
--Check
...
whether
...
the
...
description
...
appears
...
to
...
refer
...
to
...
pages
...
or
...
leaves
...
...
...
...
AND
...
(
...
...
...
...
...
...
phys.jsonb#>>'{}'
...
LIKE
...
'%page%'
...
...
...
...
...
...
OR
...
phys.jsonb#>>'{}'
...
LIKE
...
'%p.%'
...
...
...
...
...
...
OR
...
phys.jsonb#>>'{}'
...
LIKE
...
'%leaves%'
...
...
...
...
...
...
OR
...
phys.jsonb#>>'{}'
...
LIKE
...
'%
...
l.%'
...
...
...
...
)
...
...
...
...
...
--If
...
both
...
conditions
...
are
...
true,
...
extract
...
the
...
first
...
number
...
and
...
cast
...
to
...
integer
...
...
...
...
THEN
...
substring(phys.jsonb#>>'{}',
...
'\d{1,4}')::INT
...
...
...
...
...
--Otherwise
...
return
...
null
...
when
...
no
...
usable
...
page
...
count
...
is
...
found
...
...
...
...
ELSE
...
NULL
...
...
END
...
AS
...
page_count_estimate,
...
...
--ordinality
...
shows
...
the
...
order
...
of
...
the
...
physical
...
description
...
entry
...
from
...
the
...
record
...
--in
...
cases
...
where
...
there
...
is
...
more
...
than
...
one
...
...
phys.ordinality
...
AS
...
record_sequence_ordinality
...
...
...
...
...
--Start
...
from
...
the
...
instance
...
table,
...
which
...
stores
...
JSONB
...
records
...
for
...
bibliographic
...
instances
FROM
...
folio_inventory.instance
...
--LEFT
...
JOIN
...
LATERAL
...
allows
...
you
...
to
...
extract
...
the
...
physical
...
descriptions
...
array
--for
...
each
...
instance
...
record
...
whether
...
or
...
not
...
it
...
has
...
values
...
in
...
that
...
array
...
...
LEFT
...
JOIN
...
LATERAL
...
...
...
...
jsonb_array_elements(
...
...
...
...
...
...
jsonb_extract_path(instance.jsonb,
...
'physicalDescriptions')
...
...
...
...
)
...
WITH
...
ORDINALITY
...
AS
...
phys(jsonb)
...
...
...
...
ON
...
true
...
...
...
--Join
...
to
...
holdings
...
to
...
bring
...
in
...
call
...
number
...
and
...
location
...
reference
...
...
LEFT
...
JOIN
...
folio_inventory.holdings_record__t
...
...
...
...
ON
...
...
=
...
holdings_record__t.instance_id
...
...
...
--Join
...
to
...
location
...
table
...
to
...
get
...
location
...
names
...
and
...
codes
...
...
LEFT
...
JOIN
...
folio_inventory.location__t
...
...
...
...
ON
...
holdings_record__t.permanent_location_id
...
=
...
...
--Apply
...
the
...
location
...
filter
...
based
...
on
...
the
...
parameter
...
value
WHERE
...
...
CASE
...
...
...
...
--If
...
the
...
parameter
...
is
...
blank,
...
return
...
all
...
locations
...
except
...
'serv,remo'
...
...
...
...
WHEN
...
(SELECT
...
location_code_filter
...
FROM
...
parameters)
...
=
...
''
...
...
...
...
...
...
THEN
...
location__t.code
...
!=
...
'serv,remo'
...
...
...
...
...
--Otherwise
...
return
...
only
...
rows
...
matching
...
the
...
specified
...
location
...
code
...
...
...
...
ELSE
...
location__t.code
...
=
...
(SELECT
...
location_code_filter
...
FROM
...
parameters)
...
...
END
;
...
Result:
Physical Descriptions array example from the instance table:
...