Cookbook: Inventory

Inventory

This page is still under construction!

This cookbook explains data storage and data querying for the inventory. First the basic principle is explained and then individual queries for different levels.

Please note: Data storage can vary. MARC data is used in many libraries and can be accessed more efficiently in other ways. Please see the MARC cookbook. On the other hand, there are also many libraries that need to import data directly and perform data queries directly in Iventory's tables.

Table of Contents

• 1. Basics about the data storage
  • 1.1. What you can find in the inventory storage?
  • 1.2. How to querying arrays in the records?
  • 1.3. Relationships
    • 1.3.1. Relationship between instances
    • 1.3.2. Relationship between holdings
    • 1.3.3. Relationship between items
  • 1.4. Connection between instance, holdings and items
• 2. Querying the data of inventory
  • 2.1. Instance
    • 2.1.1. Metadata to the instance record
    • 2.1.2. Descriptive cataloging
      • 2.1.2.1. Main title
      • 2.1.2.2. Alternative titles
        • 2.1.2.2.1. Parallel title
        • 2.1.2.2.2. Uniform title
      • 2.1.2.3. Subtitle
      • 2.1.2.4. Persons, families and corporations
      • 2.1.2.5. Edition
      • 2.1.2.6. Publication statement
      • 2.1.2.7. Identifier
      • 2.1.2.8. Physical descriptions
      • 2.1.2.9. Languages
      • 2.1.2.10. Series Statement
        • 2.1.2.10.1. Sub series
      • 2.1.2.11. Multipart monograph
        • 2.1.2.11.1. Multipart monographs that are part of a series
      • 2.1.2.12. Mode of issuance
      • 2.1.2.13. Instance type
      • 2.1.2.14. Instance format
      • 2.1.2.15. Nature of content
      • 2.1.2.16. Dissertation or thesis information
    • 2.1.3. Subject cataloging
      • 2.1.3.1. Classifications
      • 2.1.3.2. Subjects
    • 2.1.4. Statistic codes
  • 2.2. Holdings
    • 2.2.1. Metadata to the holdings record
    • 2.2.2. Holdings record
      • 2.2.2.1. HRID - Human readable ID
      • 2.2.2.2. Notes
        • 2.2.2.2.1. Notes
        • 2.2.2.2.2. Administrative notes
      • 2.2.2.3. Locations
      • 2.2.2.4. Holdings type
      • 2.2.2.5. Electronic access informations
    • 2.2.3. Statistic codes
  • 2.3. Items
    • 2.3.1. Metadata to the item record
    • 2.3.2. Item record
      • 2.3.2.1. Item information
      • 2.3.2.2. Notes
        • 2.3.2.2.1. Notes
        • 2.3.2.2.2. Administrative notes
      • 2.3.2.3. Electronic access informations
    • 2.3.3. Statistic codes

1. Basics about the data storage

The FOLIO app Inventory stores data in 2 schemas. The business logic in inventory and the records in inventory_storage. If you want to query the records, please use inventory_storage.

Metadb only takes the inventory_storage schema from the FOLIO database and names the schema folio_inventory. The following chapters are described for data queries in Metadb, but can be used for the FOLIO database too. If you use the FOLIO database, you only need to modify the tables accordingly.

Please note: There can be very much records in the inventory storage. Please think carefully beforehand about how you can filter the data.

[top]

1.1. What you can find in the inventory storage?

The logic of data storage within the FOLIO App Inventory is divided into different levels and parts.

Parts

Broadly speaking, there are two parts to consider:

• Locations
• Inventory

The two parts each have several tables that can be connected to each other. The two parts can also be connected together.

In this cookbook we will mainly look at the inventory. The part for the locations is already documented in the cookbook for Resource Access (RA).

Levels

If there is such a kind of level for the inventory, then it could be described as follows.

Instance > Holdings > Items

Level	Description
Instance	A bibliographic record. Contains bibliographic and administrative information about a particular title
Holdings	A data record that contains information such as location, call number, and volumes owned, to enable staff to locate and manage library holdings. Holdings records may describe library holdings that are physical, electronic, or other formats.
Items	A data record representing a single item. Contains information needed to identify and track a single item or piece, such as barcode, availability, and material type.

Please note: The description for locations can be found in the cookbook Resource Access (RA).

[top]

1.2. How to querying arrays in the records?

Each record is stored as a JSON object. Metadb handles the transformation of the data. However, there are some special things to consider when querying the data.

Some parts of the records in the JSON objects are stored in arrays. You should be aware that an array can contain many data elements. Such a data element can contain a group of information.

Example:

{
...
"publication": [
    {
      "role": "Publisher",
      "place": "Halle",
      "publisher": "Postreiter-Verl.",
      "dateOfPublication": "1987 (1987)"
    }
  ]
...
}

A JSON object contains zero, one, or more key-value pairs, also called properties. The object is surrounded by curly braces {}. As you can see, the property publication is displayed, which in turn contains an object. There could just as easily be several objects in the array.

When querying data, it is therefore recommended to querying this data within a CTE. The CTE can then be combined with the rest of the record. The connection is made via the UUID of the record. Examples of queries can be found in this cookbook. With other words, you create temporary tables (CTEs) for each property that contains arrays, which you then associate with the entire record. These CTEs also contain the UUID of the entire record. This means you can simply connect the individual CTEs to the entire record via the UUID.

Example:

WITH instance_publication AS (
    SELECT
        instance.id AS instance_id,
        ...
)
SELECT
    *
FROM
    folio_inventory.instance
    LEFT JOIN instance_publication ON instance_publication.instance_id = instance.id

Also note that if there are more objects in the array, you will get more rows in the result.

[top]

1.3. Relationships

1.3.1. Relationship between instances

In the Instance relationship, you can have any relationships between an instance and another instance. The kind of relationship can be stored in the system. There is a table in the database with the name instance_relationship where the relationships are stored. Within this table you have three columns that describe the relationship.

Column	Description
superinstanceid	UUID for the parent instances. The parent instance is the record chosen as the primary record and to which all other child records are linked.
subinstanceid	UUID for the child instances. A child instance record is any record that has been associated with a parent record.
instancerelationshiptypeid	UUID for the type of relationship. The values for these types are stored in the table instance_relationship_type

To combine the informations, you have to connect all these tables together.

Example:

SELECT 
    instance_super.id AS parent_instance_id,
    jsonb_extract_path_text(instance_super.jsonb, 'title') AS parent_instance_title,
    instance_sub.id AS child_instance_id,
    jsonb_extract_path_text(instance_sub.jsonb, 'title') AS child_instance_title,
    jsonb_extract_path_text(instance_relationship_type.jsonb, 'name') AS relationship
FROM 
    folio_inventory.instance_relationship
    INNER JOIN folio_inventory.instance AS instance_super ON instance_super.id = instance_relationship.superinstanceid 
    INNER JOIN folio_inventory.instance AS instance_sub ON instance_sub.id = instance_relationship.subinstanceid 
    LEFT JOIN folio_inventory.instance_relationship_type ON instance_relationship_type.id = instance_relationship.instancerelationshiptypeid

[top]

1.3.2. Relationship between holdings

[top]

1.3.3. Relationship between items

[top]

1.4. Connection between instance, holdings and items

You can create a connection between instances, holdings and items. The following example shows how to do this.

Example:

SELECT 
    instance.id AS instance_id,
    holdings_record.id AS holding_id,
    jsonb_extract_path_text(holdings_record.jsonb, 'hrid') AS holding_hrid,
    item.id AS item_id
FROM 
    folio_inventory.instance
    LEFT JOIN folio_inventory.holdings_record ON jsonb_extract_path_text(holdings_record.jsonb, 'instanceId') :: UUID = instance.id
    LEFT JOIN folio_inventory.item ON jsonb_extract_path_text(item.jsonb, 'holdingsRecordId') :: UUID = holdings_record.id
ORDER BY 
    instance.id,
    jsonb_extract_path_text(holdings_record.jsonb, 'hrid')

Sample output:

instance_id	holding_id	holding_hrid	item_id
32ffe839-6303-43e4-8394-54b429e131ba	bcf1980e-fae4-47fd-b0f9-cd95fc91e9ee	ho00000000001	62f5aa31-232b-4291-8094-9e51c4966a97
32ffe839-6303-43e4-8394-54b429e131ba	bcf1980e-fae4-47fd-b0f9-cd95fc91e9ee	ho00000000001	a5f0bee1-6744-4eac-b891-0d9d407686e3
32ffe839-6303-43e4-8394-54b429e131ba	bcf1980e-fae4-47fd-b0f9-cd95fc91e9ee	ho00000000001	c74eac7b-3883-4998-b1f1-9f19c35f7d7d
1c1f2b01-d1db-41a6-a51e-3a92860a6b14	1be016fd-9374-471d-8d4c-dd18db29ef74	ho00000000005	333e1adf-179e-4efc-9704-4278d2ee1a83

[top]

2. Querying the data of inventory

2.1. Instance

An instance is a bibliographic record. It contains bibliographic and administrative information about a particular title.

[top]

2.1.1. Metadata to the instance record

Every record in FOLIO has metadata. An instance's metadata can be queried as follows.

Example:

SELECT 
    instance.id AS instance_id,
    jsonb_extract_path_text(instance.jsonb, 'source') AS record_source,
    jsonb_extract_path_text(instance.jsonb, '_version') :: INTEGER AS version,
    jsonb_extract_path_text(instance.jsonb, 'metadata', 'createdDate') :: TIMESTAMPTZ AS created_date,
    jsonb_extract_path_text(instance.jsonb, 'metadata', 'updatedDate') :: TIMESTAMPTZ AS updated_date,
    jsonb_extract_path_text(instance.jsonb, 'metadata', 'createdByUserId') :: UUID AS created_by_user_id,
    jsonb_extract_path_text(instance.jsonb, 'metadata', 'updatedByUserId') :: UUID AS updated_by_user_id,
    COALESCE(jsonb_extract_path_text(instance.jsonb, 'discoverySuppress') :: BOOLEAN, FALSE) AS discovery_suppress
FROM 
    folio_inventory.instance

[top]

2.1.2. Descriptive cataloging

2.1.2.1. Main title

See also RDA 2.3.2 - The title, which is the main name for the resource.

The main title is part of the instance record. The example shows how to query the main title.

Example:

SELECT 
    id AS instance_id,
    jsonb_extract_path_text(INSTANCE.jsonb, 'title') AS instance_title
FROM 
    folio_inventory.instance

[top]

2.1.2.2. Alternative titles

The alternative titles contains several titles like parallel title, uniform title and more. They are part of the instance record and stored in an array. Since there can be multiple alternative titles or title types and therefore several in the array, note that the query may cause you to get multiple rows in the result.

Example:

SELECT 
    instance.id AS instance_id,
    jsonb_extract_path_text(alternative_titles.jsonb, 'alternativeTitle') AS alternative_title,
    jsonb_extract_path_text(alternative_titles.jsonb, 'alternativeTitleTypeId') :: UUID AS alternative_title_type_id,
    jsonb_extract_path_text(alternative_title_type.jsonb, 'name') AS alternative_title_type_name
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(instance.jsonb, 'alternativeTitles')) WITH ORDINALITY AS alternative_titles (jsonb)
    LEFT JOIN folio_inventory.alternative_title_type ON alternative_title_type.id = jsonb_extract_path_text(alternative_titles.jsonb, 'alternativeTitleTypeId') :: UUID

[top]

2.1.2.2.1. Parallel title

See also RDA 2.3.3 - The parallel title is the main title in one other language and/or writing

Parallel titles are part of the alternative titles in FOLIO. You can filter out these types of titles by filtering the alternative title type name.

Example:

SELECT 
    instance.id AS instance_id,
    jsonb_extract_path_text(alternative_titles.jsonb, 'alternativeTitle') AS alternative_title,
    jsonb_extract_path_text(alternative_titles.jsonb, 'alternativeTitleTypeId') :: UUID AS alternative_title_type_id,
    jsonb_extract_path_text(alternative_title_type.jsonb, 'name') AS alternative_title_type_name
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(instance.jsonb, 'alternativeTitles')) WITH ORDINALITY AS alternative_titles (jsonb)
    LEFT JOIN folio_inventory.alternative_title_type ON alternative_title_type.id = jsonb_extract_path_text(alternative_titles.jsonb, 'alternativeTitleTypeId') :: UUID
WHERE 
    jsonb_extract_path_text(alternative_title_type.jsonb, 'name') = 'Parallel title'

[top]

2.1.2.2.2. Uniform title

See also RDA 6.2.2 - The preferred title of the work is the title or title form chosen to identify the work. The preferred title is also the basis for the standardized search entry that represents this work.

Uniform titles are part of the alternative titles in FOLIO. You can filter out these types of titles by filtering the alternative title type name.

Example:

SELECT 
    instance.id AS instance_id,
    jsonb_extract_path_text(alternative_titles.jsonb, 'alternativeTitle') AS alternative_title,
    jsonb_extract_path_text(alternative_titles.jsonb, 'alternativeTitleTypeId') :: UUID AS alternative_title_type_id,
    jsonb_extract_path_text(alternative_title_type.jsonb, 'name') AS alternative_title_type_name
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(instance.jsonb, 'alternativeTitles')) WITH ORDINALITY AS alternative_titles (jsonb)
    LEFT JOIN folio_inventory.alternative_title_type ON alternative_title_type.id = jsonb_extract_path_text(alternative_titles.jsonb, 'alternativeTitleTypeId') :: UUID
WHERE 
    jsonb_extract_path_text(alternative_title_type.jsonb, 'name') = 'Uniform title'

[top]

2.1.2.3. Subtitle

[top]

2.1.2.4. Persons, families and corporations

• In FOLIO, FRBR group 2 is stored within the instance record.
• The informations are stored grouped as an object within an array. An object is created in the array for each entity.
• The property name for this array is contributors but it can contains all relationships to the resource, e.g. also the creators and so on.

For the German Community: Please take a look to the list AH-017 from the standardization council.

The following example shows how you can query the data.

Example:

SELECT 
    instance.id AS instance_id,
    jsonb_extract_path_text(contributors.jsonb, 'name') AS entity_name,
    COALESCE(jsonb_extract_path_text(contributors.jsonb, 'primary') :: BOOLEAN, FALSE) AS primary,
    jsonb_extract_path_text(contributors.jsonb, 'contributorNameTypeId') :: UUID AS entity_name_type_id,
    jsonb_extract_path_text(contributor_name_type.jsonb, 'name') AS entity_name_type,
    jsonb_extract_path_text(contributors.jsonb, 'contributorTypeId') :: UUID entity_relation_type_id,
    jsonb_extract_path_text(contributor_type.jsonb, 'code') AS entity_relation_type_code,
    jsonb_extract_path_text(contributor_type.jsonb, 'name') AS entity_relation_type_name,
    jsonb_extract_path_text(contributor_type.jsonb, 'source') AS entity_relation_type_source
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(instance.jsonb, 'contributors')) WITH ORDINALITY AS contributors (jsonb)
    LEFT JOIN folio_inventory.contributor_name_type ON contributor_name_type.id = jsonb_extract_path_text(contributors.jsonb, 'contributorNameTypeId') :: UUID
    LEFT JOIN folio_inventory.contributor_type ON contributor_type.id = jsonb_extract_path_text(contributors.jsonb, 'contributorTypeId') :: UUID

Sample Output:

Attribute	Output
instance_id	1c1f2b01-d1db-41a6-a51e-3a92860a6b14
entity_name	Krieger, Martin
primary	True False
entity_name_type_id	2b94c631-fca9-4892-a730-03ee529ffe2a
entity_name_type	Personal name
entity_relation_type_id	6e09d47d-95e2-4d8a-831b-f777b8ef6d81
entity_relation_type_code	aut
entity_relation_type_name	Author
entity_relation_type_source	marcrelator

[top]

2.1.2.5. Edition

The information about the edition statement, imprint and other publication source information. This means that multiple editions can be stored. The editions are therefore stored in an array and must be extracted.

Example:

SELECT
    instance.id AS instance_id,
    jsonb_extract_path_text(instance.jsonb, 'hrid') AS instance_hrid,
    editions.jsonb AS edition,
    editions.ordinality AS edition_ordinality
FROM
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(instance.jsonb, 'editions')) WITH ORDINALITY AS editions (jsonb)

[top]

2.1.2.6. Publication statement

See also RDA 2.8 - A statement identifying the place or places of publication, publisher or publishers, and date or dates of publication of a resource. Publication statements include statements relating to the publication, release, or issuing of a manifestation. Consider all online resources to be published. For early printed resources, distribution and manufacture statements relating to booksellers and printers may be treated as publication statements

In FOLIO, this information is stored together as a group and stored in an array in the instance's record. The following example shows how you can query the data.

Example:

SELECT
    instance.id AS instance_id,
    jsonb_extract_path_text(publication_statement.jsonb, 'place') AS place,
    jsonb_extract_path_text(publication_statement.jsonb, 'publisher') AS publisher,
    jsonb_extract_path_text(publication_statement.jsonb, 'dateOfPublication') AS date_of_publication,
    jsonb_extract_path_text(publication_statement.jsonb, 'role') AS role
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(instance.jsonb, 'publication')) WITH ORDINALITY AS publication_statement (jsonb)

[top]

2.1.2.7. Identifier

There are many different identifiers that can be saved to an instance in FOLIO. For this reason the identifiers are stored in an array in the instance's record. The following example shows how you can query the data.

Tip: Filter the type of identifier you need in the Where clause.

Example:

SELECT 
    instance.id AS instance_id,
    jsonb_extract_path_text(identifiers.jsonb, 'value') AS identifier_value,
    jsonb_extract_path_text(identifiers.jsonb, 'identifierTypeId') :: UUID AS identifier_type_id,
    jsonb_extract_path_text(identifier_type.jsonb, 'name') AS identifier_type_name
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(instance.jsonb, 'identifiers')) WITH ORDINALITY AS identifiers (jsonb)
    LEFT JOIN folio_inventory.identifier_type ON identifier_type.id = jsonb_extract_path_text(identifiers.jsonb, 'identifierTypeId') :: UUID
WHERE 
    jsonb_extract_path_text(identifier_type.jsonb, 'name') = 'ISBN'

[top]

2.1.2.8. Physical descriptions

The physical description of a manifestation describes the extent, dimensions, and other physical details in such a way that it can be distinguished from other manifestations with similar characteristics.

With FOLIO, several RDA elements are combined and saved as a string. This could be e.g. Page count, illustrative content, dimensions and so on. These strings are stored in an array in the instance's record. The following example shows how you can query the data.

Example:

SELECT 
    instance.id AS instance_id,
    physical_descriptions.jsonb AS physical_descriptions,
    physical_descriptions.ordinality AS physical_description_ordinality
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(instance.jsonb, 'physicalDescriptions')) WITH ORDINALITY AS physical_descriptions (jsonb)

Sample Output:

instance_id	physical_descriptions	physical_description_ordinality
1c1f2b01-d1db-41a6-a51e-3a92860a6b14	307, [16] S.; Ill., Kt.	1
84e22c03-114e-40f0-8561-07950a6f5460	216 pages : illustrations ; 24 cm	1

[top]

2.1.2.9. Languages

See also RDA 6.11 - The language in which the work is expressed.

In FOLIO, languages ​​are stored in an array in the instance's record. The following example shows how you can query the data. Please note: The instances can always have multiple languages.

Example:

SELECT
    instance.id AS instance_id,
    jsonb_extract_path_text(instance.jsonb, 'hrid') AS instance_hrid,
    languages.jsonb AS instance_language,
    languages.ordinality AS language_ordinality
FROM
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(jsonb, 'languages')) WITH ORDINALITY AS languages (jsonb)

Sample Output:

instance_id	instance_hrid	instance_language	language_ordinality
5eb4bdf0-04d3-408d-a418-300276845c90	in00000000004	eng	1
1c1f2b01-d1db-41a6-a51e-3a92860a6b14	in00000000008	ger	1

[top]

2.1.2.10. Series Statement

See also RDA 2.12 - A statement identifying a series to which a resource belongs and the numbering of the resource within the series.

Please note: The instances can always have multiple series.

In FOLIO, series are stored in an array in the instance's record. The following example shows how you can query the data.

Example:

SELECT 
    instance.id,
    series.jsonb AS series
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(instance.jsonb, 'series')) WITH ORDINALITY AS series (jsonb)

If you use a hierarchical description, you can also connect to the series through the relationship between instances.

Example:

SELECT 
    instance_super.id AS series_instance_id,
    jsonb_extract_path_text(instance_super.jsonb, 'title') AS series_instance_title,
    instance_sub.id AS volume_instance_id,
    jsonb_extract_path_text(instance_sub.jsonb, 'title') AS volume_instance_title
FROM 
    folio_inventory.instance_relationship
    INNER JOIN folio_inventory.instance AS instance_super ON instance_super.id = instance_relationship.superinstanceid 
    INNER JOIN folio_inventory.instance AS instance_sub ON instance_sub.id = instance_relationship.subinstanceid 
    LEFT JOIN folio_inventory.instance_relationship_type ON instance_relationship_type.id = instance_relationship.instancerelationshiptypeid 
WHERE 
    jsonb_extract_path_text(instance_relationship_type.jsonb, 'name') = 'monographic series'
ORDER BY 
    jsonb_extract_path_text(instance_super.jsonb, 'title'),
    jsonb_extract_path_text(instance_sub.jsonb, 'title')

[top]

2.1.2.10.1. Sub series

Sometimes a series can have a sub-series. This can also be queried in the relationship between the instances.

Example:

WITH sub_series AS (
    SELECT 
        instance_super.id AS sub_series_instance_id,
        jsonb_extract_path_text(instance_super.jsonb, 'title') AS sub_series_instance_title,
        instance_sub.id AS volume_instance_id,
        jsonb_extract_path_text(instance_sub.jsonb, 'title') AS volume_instance_title
    FROM 
        folio_inventory.instance_relationship
        INNER JOIN folio_inventory.instance AS instance_super ON instance_super.id = instance_relationship.superinstanceid 
        INNER JOIN folio_inventory.instance AS instance_sub ON instance_sub.id = instance_relationship.subinstanceid 
        LEFT JOIN folio_inventory.instance_relationship_type ON instance_relationship_type.id = instance_relationship.instancerelationshiptypeid 
    WHERE 
        jsonb_extract_path_text(instance_relationship_type.jsonb, 'name') = 'monographic series'
    ORDER BY 
        jsonb_extract_path_text(instance_super.jsonb, 'title'),
        jsonb_extract_path_text(instance_sub.jsonb, 'title')
)
SELECT 
    series.id AS series_instance_id,
    jsonb_extract_path_text(series.jsonb, 'title') AS series_instance_title,
    sub_series.sub_series_instance_id,
    sub_series.sub_series_instance_title,
    sub_series.volume_instance_id,
    sub_series.volume_instance_title
FROM 
    sub_series
    INNER JOIN folio_inventory.instance_relationship ON instance_relationship.subinstanceid = sub_series.sub_series_instance_id
    INNER JOIN folio_inventory.instance AS series ON series.id = instance_relationship.superinstanceid
ORDER BY 
    jsonb_extract_path_text(series.jsonb, 'title'),
    sub_series.sub_series_instance_title,
    sub_series.volume_instance_title

Sample output:

Attribut	Sample output
series_instance_id	f975a0f3-e433-525d-afce-057dc33fa4c1
series_instance_title	Abhandlungen der Mathematisch-Physikalischen Klasse der Königlich Bayerischen Akademie der Wissenschaften. Supplement-Band
sub_series_instance_id	181c8364-f2b5-50e8-b82c-6822683eebad
sub_series_instance_title	Beiträge zur Naturgeschichte Ostasiens
volume_instance_id	a9e2beca-2cb8-5554-a9cb-9dad8f1f014c
volume_instance_title	Japanische Pennatuliden / Heinrich Balss

[top]

2.1.2.11. Multipart monograph

A resource that is available (either simultaneously or sequentially) in multiple parts appears that is complete or within a limited number of parts should be completed (e.g. a lexicon in two volumes or three audio cassettes, as Set appear).

See also RDA 1.5 - How you can querying the data depends on how you create the bibliographic description according to RDA.

• English-American region: It will either be a comprehensive description or an analytical description used. LC practice for general cataloging/PCC practice: Do not create hierarchical descriptions.
• D-A-CH (German region): You can use all variants of descriptions. In the most cases there is a hierarchical description.

If you use the hierarchical description the following example can help you to querying the data:

SELECT 
    instance_super.id AS parent_title_instance_id,
    jsonb_extract_path_text(instance_super.jsonb, 'title') AS parent_title_instance_title,
    instance_sub.id AS volume_instance_id,
    jsonb_extract_path_text(instance_sub.jsonb, 'title') AS volume_instance_title
FROM 
    folio_inventory.instance_relationship
    INNER JOIN folio_inventory.instance AS instance_super ON instance_super.id = instance_relationship.superinstanceid 
    INNER JOIN folio_inventory.instance AS instance_sub ON instance_sub.id = instance_relationship.subinstanceid 
    LEFT JOIN folio_inventory.instance_relationship_type ON instance_relationship_type.id = instance_relationship.instancerelationshiptypeid 
WHERE 
    jsonb_extract_path_text(instance_relationship_type.jsonb, 'name') = 'multipart monograph'
ORDER BY 
    jsonb_extract_path_text(instance_super.jsonb, 'title'),
    jsonb_extract_path_text(instance_sub.jsonb, 'title')

[top]

2.1.2.11.1. Multipart monographs that are part of a series

As described in the previous chapter for the series statement, series can also contain multi-part monographs. The following example shows how you can query the titles of series and multi-part monographs together.

Example:

WITH multipart_monograph AS (
    SELECT 
        instance_super.id AS parent_title_instance_id,
        jsonb_extract_path_text(instance_super.jsonb, 'title') AS parent_title_instance_title,
        instance_sub.id AS volume_instance_id,
        jsonb_extract_path_text(instance_sub.jsonb, 'title') AS volume_instance_title
    FROM 
        folio_inventory.instance_relationship
        INNER JOIN folio_inventory.instance AS instance_super ON instance_super.id = instance_relationship.superinstanceid 
        INNER JOIN folio_inventory.instance AS instance_sub ON instance_sub.id = instance_relationship.subinstanceid 
        LEFT JOIN folio_inventory.instance_relationship_type ON instance_relationship_type.id = instance_relationship.instancerelationshiptypeid 
    WHERE 
        jsonb_extract_path_text(instance_relationship_type.jsonb, 'name') = 'multipart monograph'
    ORDER BY 
        jsonb_extract_path_text(instance_super.jsonb, 'title'),
        jsonb_extract_path_text(instance_sub.jsonb, 'title')
),
series AS (
    SELECT 
        instance_super.id AS series_instance_id,
        jsonb_extract_path_text(instance_super.jsonb, 'title') AS series_instance_title,
        instance_sub.id AS volume_instance_id,
        jsonb_extract_path_text(instance_sub.jsonb, 'title') AS volume_instance_title
    FROM 
        folio_inventory.instance_relationship
        INNER JOIN folio_inventory.instance AS instance_super ON instance_super.id = instance_relationship.superinstanceid 
        INNER JOIN folio_inventory.instance AS instance_sub ON instance_sub.id = instance_relationship.subinstanceid 
        LEFT JOIN folio_inventory.instance_relationship_type ON instance_relationship_type.id = instance_relationship.instancerelationshiptypeid 
    WHERE 
        jsonb_extract_path_text(instance_relationship_type.jsonb, 'name') = 'monographic series'
    ORDER BY 
        jsonb_extract_path_text(instance_super.jsonb, 'title'),
        jsonb_extract_path_text(instance_sub.jsonb, 'title')
)
SELECT 
    instance.id AS instance_id,
    jsonb_extract_path_text(instance.jsonb, 'title') AS volume_title,
    multipart_monograph.parent_title_instance_title AS multipart_monograph_title,
    series.series_instance_title AS series_title
FROM 
    folio_inventory.instance
    LEFT JOIN multipart_monograph ON multipart_monograph.volume_instance_id = instance.id
    LEFT JOIN series ON series.volume_instance_id = instance.id
WHERE 
    (
            multipart_monograph.parent_title_instance_title IS NOT NULL 
        AND series.series_instance_title IS NOT NULL 
    )

[top]

2.1.2.12. Mode of issuance

See also RDA 2.13 - A categorization that reflects whether a manifestation appears in one part or multiple parts, the manner in which it is updated, and whether its completion is predetermined or not. There are 4 different types according to RDA: Single unit, Multi-part monograph, Ongoing resource, Integrating resource. In FOLIO, they have sometimes other words for.

RDA	FOLIO
Single unit	single unit
Multi-part monograph	multipart monograph
Ongoing resource	serial
Integrating resource	integrating resource
/	unspecified

The following example shows how to get the data for the mode of issuance.

Example:

SELECT 
    instance.id AS instance_id,
    jsonb_extract_path_text(mode_of_issuance.jsonb, 'name') AS mode_of_issuance
FROM 
    folio_inventory.instance
    LEFT JOIN folio_inventory.mode_of_issuance ON mode_of_issuance.id = jsonb_extract_path_text(instance.jsonb, 'modeOfIssuanceId') :: UUID

[top]

2.1.2.13. Instance type

See also RDA 6.9 - The instance type descripe the kind of content from the resource. The instance type reflects the form of communication in which the content of the resource is used expressed and with what human sense (seeing, hearing, feeling, ...) the content is perceived.

RDA provides a list of values. Examples: text, sounds or two-dimensional moving image . These values are sometimes also offered in translation. Please check this in your system.

In FOLIO the content types are stored in the table instance_type. You can associate the content types with the record for the instance.

Example:

SELECT 
    instance.id AS instance_id,
    instance_type.id AS instance_type_id,
    jsonb_extract_path_text(instance_type.jsonb, 'code') AS instance_type_code,
    jsonb_extract_path_text(instance_type.jsonb, 'name') AS instance_type_name,
    jsonb_extract_path_text(instance_type.jsonb, 'source') AS instance_type_source
FROM 
    folio_inventory.instance 
    INNER JOIN folio_inventory.instance_type ON instance_type.id = jsonb_extract_path_text(instance.jsonb, 'instanceTypeId') :: UUID

Sample output:

instance_id	instance_type_id	instance_type_code	instance_type_name	instance_type_source
5eb4bdf0-04d3-408d-a418-300276845c90	6312d172-f0cf-40f6-b27d-9fa8feaf332f	txt	text	rdacontent

[top]

2.1.2.14. Instance format

In RDA you have two different types. You have the media type and the data carrier type.

• See also RDA 3.2 for the media type - The media type expresses the category of device required to support the resource to watch, play or run. Examples for media types: unmediated, computer ...
• See also RDA 3.3 for the data carrier type - The disk type categorizes the format of the storage medium and the housing of a data carrier - in combination with the type of device required. Examples for data carrier types: volume, online resource ...

RDA provides lists of the values. These values are sometimes also offered in translation. Please check this in your system.

In FOLIO the different combinations of both types are merged together in records in the table instance_format. What is important is that this only concerns the instance's affiliations with the respective types.

Example:

WITH instance_format_ids AS (
    SELECT 
        instance.id AS instance_id,
        instanceFormatIds.jsonb :: uuid AS instance_format_id
    FROM 
        folio_inventory.instance
        CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(instance.jsonb, 'instanceFormatIds')) WITH ORDINALITY AS instanceFormatIds (jsonb)
)
SELECT 
    instance_format_ids.instance_id,
    instance_format_ids.instance_format_id,
    jsonb_extract_path_text(instance_format.jsonb, 'code') AS instance_format_code,
    jsonb_extract_path_text(instance_format.jsonb, 'name') AS instance_format_name,
    jsonb_extract_path_text(instance_format.jsonb, 'source') AS instance_format_source
FROM 
    instance_format_ids
    LEFT JOIN folio_inventory.instance_format ON instance_format.id = instance_format_ids.instance_format_id

Sample output:

instance_id	instance_format_id	instance_format_code	instance_format_name	instance_format_source
84e22c03-114e-40f0-8561-07950a6f5460	8d511d33-5e85-4c5d-9bce-6e3c9cd0c324	nc	unmediated -- volume	rdacarrier
5eb4bdf0-04d3-408d-a418-300276845c90	f5e8210f-7640-459b-a71f-552567f92369	cr	computer -- online resource	rdacarrier

[top]

2.1.2.15. Nature of content

See also RDA 7.2. - The specific property of a resource's primary content. Examples Textbook, Thesis, Journal. RDA provides lists of the values. These values are sometimes also offered in translation. Please check this in your system.

In FOLIO you can find the records in the table nature_of_content_term.

Example:

WITH nature_of_content_ids AS (
    SELECT 
        instance.id AS instance_id,
        nature_of_content_ids.jsonb :: uuid AS nature_of_content_term_id
    FROM 
        folio_inventory.instance
        CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(instance.jsonb, 'natureOfContentTermIds')) WITH ORDINALITY AS nature_of_content_ids (jsonb)
)
SELECT 
    nature_of_content_ids.instance_id,
    nature_of_content_ids.nature_of_content_term_id,
    jsonb_extract_path_text(nature_of_content_term.jsonb, 'name') AS nature_of_content_term_name,
    jsonb_extract_path_text(nature_of_content_term.jsonb, 'source') AS nature_of_content_term_source
FROM 
    nature_of_content_ids
    LEFT JOIN folio_inventory.nature_of_content_term ON nature_of_content_term.id = nature_of_content_ids.nature_of_content_term_id

Sample output:

instance_id	nature_of_content_term_id	nature_of_content_term_name	nature_of_content_term_source
89457437-a919-4b86-bcf4-c6f48cfc19f7	0abeee3d-8ad2-4b04-92ff-221b4fce1075	journal	folio

[top]

2.1.2.16. Dissertation or thesis information

See also RDA 7.9 - A university thesis is a work that is presented to obtain an academic degree.

See also RDA 7.2 (Nature of content): The information thesis should be recorded as the type of content.

The best way to find your thesis in the inventory records is to filter out all instances where the nature of content is thesis.

Example:

SELECT 
    instance.id,
    nature_of_content.jsonb :: uuid AS nature_of_content_id,
    jsonb_extract_path_text(nature_of_content_term.jsonb, 'name') AS nature_of_content_name,
    jsonb_extract_path_text(nature_of_content_term.jsonb, 'source') AS nature_of_content_source
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(instance.jsonb, 'natureOfContentTermIds')) WITH ORDINALITY AS nature_of_content (jsonb)
    LEFT JOIN folio_inventory.nature_of_content_term ON nature_of_content_term.id = nature_of_content.jsonb :: uuid
WHERE 
    jsonb_extract_path_text(nature_of_content_term.jsonb, 'name') = 'thesis'

[top]

2.1.3. Subject cataloging

2.1.3.1. Classifications

There are different types of classifications in the world. In FOLIO you can therefore store several classifications with their classification type for an instance record. If you only need a specific type, filter it using the WHERE clause.

Example:

SELECT 
    instance.id AS instance_id,
    jsonb_extract_path_text(classifications.jsonb, 'classificationNumber') AS classification_number,
    jsonb_extract_path_text(classifications.jsonb, 'classificationTypeId') :: UUID AS classification_type_id,
    jsonb_extract_path_text(classification_type.jsonb, 'name') AS classification_type_name,
    jsonb_extract_path_text(classification_type.jsonb, 'source') AS classification_source
FROM 
    folio_inventory.instance
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(instance.jsonb, 'classifications')) WITH ORDINALITY AS classifications (jsonb)
    LEFT JOIN folio_inventory.classification_type ON classification_type.id = jsonb_extract_path_text(classifications.jsonb, 'classificationTypeId') :: UUID

Sample Output:

instance_id	classification_number	classification_type_id	classification_type_name	classification_source
5eb4bdf0-04d3-408d-a418-300276845c90	JZ1318 .M3877 2021eb	ce176ace-a53e-4b4d-aa89-725ed7b2edac	LC	folio
5eb4bdf0-04d3-408d-a418-300276845c90	303.482	42471af9-7d25-4f3a-bf78-60d29dcf463b	Dewey	folio

[top]

2.1.3.2. Subjects

Subjects are stored in the instance record as array elements. You can simply query this array to get the subjects.

Example:

SELECT 
    instance.id AS instance_id,
    jsonb_array_elements_text(jsonb_extract_path(instance.jsonb, 'subjects')) AS subject
FROM 
    folio_inventory.instance

If you need a list of the subjects for an instance record in one line, please use the aggregation function string_agg().

Example:

WITH subjects AS (
    SELECT 
        instance.id AS instance_id,
        jsonb_array_elements_text(jsonb_extract_path(instance.jsonb, 'subjects')) AS subject
    FROM 
        folio_inventory.INSTANCE
)
SELECT 
    instance_id,
    string_agg(
        subject,
        ', '
        ORDER BY subject
    ) AS subjects
FROM 
    subjects
GROUP BY 
    instance_id

[top]

2.1.4. Statistic codes

You can enter statistic codes to an instance record.

Example:

WITH statistical_code_ids AS (
    SELECT 
        instance.id AS instance_id,
        jsonb_array_elements_text(jsonb_extract_path(instance.jsonb, 'statisticalCodeIds')) :: UUID AS statistical_code_id
    FROM 
        folio_inventory.instance
)
SELECT 
    instance_id,
    statistical_code_id,
    jsonb_extract_path_text(statistical_code.jsonb, 'code') AS statistical_code,
    jsonb_extract_path_text(statistical_code.jsonb, 'name') AS statistical_code_name,
    jsonb_extract_path_text(statistical_code.jsonb, 'source') AS statistical_code_source,
    jsonb_extract_path_text(statistical_code.jsonb, 'statisticalCodeTypeId') :: UUID AS statistical_code_type_id,
    jsonb_extract_path_text(statistical_code_type.jsonb, 'name') AS statistical_code_type_name,
    jsonb_extract_path_text(statistical_code_type.jsonb, 'source') AS statistical_code_type_source
FROM 
    statistical_code_ids
    LEFT JOIN folio_inventory.statistical_code ON statistical_code.id = statistical_code_ids.statistical_code_id
    LEFT JOIN folio_inventory.statistical_code_type ON statistical_code_type.id = jsonb_extract_path_text(statistical_code.jsonb, 'statisticalCodeTypeId') :: UUID

[top]

2.2. Holdings

A holding is a data record that contains information such as location, call number, and volumes owned, to enable staff to locate and manage library holdings. Holdings records may describe library holdings that are physical, electronic, or other formats.

[top]

2.2.1. Metadata to the holdings record

Every record in FOLIO has metadata. The metadata for a holding record can be queried as follows.

Example:

SELECT 
    holdings_record.id AS holdings_record_id,
    jsonb_extract_path_text(holdings_record.jsonb, 'sourceId') :: UUID AS record_source_id,
    jsonb_extract_path_text(holdings_records_source.jsonb, 'name') AS record_source_name,
    jsonb_extract_path_text(holdings_records_source.jsonb, 'source') AS record_source_source,
    jsonb_extract_path_text(holdings_record.jsonb, '_version') :: INTEGER AS version,
    COALESCE(jsonb_extract_path_text(holdings_record.jsonb, 'discoverySuppress') :: BOOLEAN, FALSE) AS discovery_suppress,
    jsonb_extract_path_text(holdings_record.jsonb, 'metadata', 'createdDate') :: TIMESTAMPTZ AS created_date,
    jsonb_extract_path_text(holdings_record.jsonb, 'metadata', 'updatedDate') :: TIMESTAMPTZ AS updated_date,
    jsonb_extract_path_text(holdings_record.jsonb, 'metadata', 'createdByUserId') :: UUID AS created_by_user_id,
    jsonb_extract_path_text(holdings_record.jsonb, 'metadata', 'updatedByUserId') :: UUID AS updated_by_user_id
FROM 
    folio_inventory.holdings_record
    LEFT JOIN folio_inventory.holdings_records_source ON holdings_records_source.id = jsonb_extract_path_text(holdings_record.jsonb, 'sourceId') :: UUID

[top]

2.2.2. Holdings record

2.2.2.1. HRID - Human readable ID

HRID is the "Human readable identifier", also called "eye readable ID". It is an ID that can better read from humans than the UUIDs. It is an additional ID that is stored in the holdings record and displayed in FOLIO. Regardless, there is a UUID for each record.

Please note: An instance can have multiple holding records. For this reason it make sense to include the instance record informations in the query.

Example:

SELECT 
    instance.id AS instance_id,
    holdings_record.id AS holding_id,
    jsonb_extract_path_text(holdings_record.jsonb, 'hrid') AS holding_hrid
FROM 
    folio_inventory.instance
    INNER JOIN folio_inventory.holdings_record ON jsonb_extract_path_text(holdings_record.jsonb, 'instanceId') :: UUID = instance.id
ORDER BY 
    instance.id,
    jsonb_extract_path_text(holdings_record.jsonb, 'hrid')

Sample output:

instance_id	holding_id	holding_hrid
89457437-a919-4b86-bcf4-c6f48cfc19f7	94d7bb58-0f76-4eb0-8d91-1308f3b7491b	ho00000000002
89457437-a919-4b86-bcf4-c6f48cfc19f7	721c86dd-5a21-4ee4-9580-5593632a045d	ho00000000003

[top]

2.2.2.2. Notes

You can give different notes to a holding record.

• Notes
• Administrative notes

2.2.2.2.1. Notes

The notes are stored in an array. The following example show how you can extract the data.

Example:

SELECT 
    holdings_record.id AS holdings_record_id,
    jsonb_extract_path_text(notes.jsonb, 'note') AS holdings_record_note,
    jsonb_extract_path_text(notes.jsonb, 'holdingsNoteTypeId') :: UUID AS note_type_id,
    jsonb_extract_path_text(holdings_note_type.jsonb, 'name') AS note_type_name,
    jsonb_extract_path_text(holdings_note_type.jsonb, 'source') AS note_type_source
FROM 
    folio_inventory.holdings_record
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(holdings_record.jsonb, 'notes')) WITH ORDINALITY AS notes (jsonb)
    LEFT JOIN folio_inventory.holdings_note_type ON holdings_note_type.id = jsonb_extract_path_text(notes.jsonb, 'holdingsNoteTypeId') :: UUID

Sample output:

holdings_record_id	holdings_record_note	note_type_id	note_type_name	note_type_source
f955dd70-d615-4f15-8ebe-9d1e5140f4e7	Test 1	d6510242-5ec3-42ed-b593-3585d2e48fd6	Action note	folio

[top]

2.2.2.2.2. Administrative notes

The administrative notes are stored in an array. The following example show how you can extract the data.

Example:

SELECT 
    holdings_record.id AS holdings_record_id,
    administrative_notes.jsonb AS administrative_notes
FROM 
    folio_inventory.holdings_record
    CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(holdings_record.jsonb, 'administrativeNotes')) WITH ORDINALITY AS administrative_notes (jsonb)

Sample output:

holdings_record_id	administrative_notes
94d7bb58-0f76-4eb0-8d91-1308f3b7491b	delete holdings after 2022
94d7bb58-0f76-4eb0-8d91-1308f3b7491b	cataloged on 10/01/2022
94d7bb58-0f76-4eb0-8d91-1308f3b7491b	revised on 10/10/2022

[top]

2.2.2.3. Locations

A holding record can contains different location types. Since only the UUIDs are in the holding record, they still have to be linked to the locations via the UUIDs. The following example shows how to do this. Please note: In the example only some data about the locations in the main query was taken. You can also add additional data from the CTE.

Example:

WITH locations AS (
    SELECT
        loccampus__t.id                    AS campus_id,
        loccampus__t.name                  AS campus_name,
        loccampus__t.code                  AS campus_code,
        location__t.id                     AS location_id,
        location__t.name                   AS location_name,
        location__t.code                   AS location_code,
        location__t.discovery_display_name AS discovery_display_name,
        loclibrary__t.id                   AS library_id,
        loclibrary__t.name                 AS library_name,
        loclibrary__t.code                 AS library_code,
        locinstitution__t.id               AS institution_id,
        locinstitution__t.name             AS institution_name,
        locinstitution__t.code             AS institution_code
    FROM
        folio_inventory.loccampus__t
        LEFT JOIN folio_inventory.location__t       ON location__t.campus_id = loccampus__t.id
        LEFT JOIN folio_inventory.locinstitution__t ON locinstitution__t.id  = location__t.institution_id 
        LEFT JOIN folio_inventory.loclibrary__t     ON loclibrary__t.id      = location__t.library_id
)
SELECT 
    holdings_record.id AS holdings_record_id,
    jsonb_extract_path_text(holdings_record.jsonb, 'permanentLocationId') :: UUID AS permanent_location_id,
    permanent_location.location_name AS permanent_location_name,
    permanent_location.location_code AS permanent_location_code,
    jsonb_extract_path_text(holdings_record.jsonb, 'temporaryLocationId') :: UUID AS temporary_location_id,
    temporary_location.location_name AS temporary_location_name,
    temporary_location.location_code AS temporary_location_code,
    jsonb_extract_path_text(holdings_record.jsonb, 'effectiveLocationId') :: UUID AS effective_location_id,
    effective_location.location_name AS effective_location_name,
    effective_location.location_code AS effective_location_code
FROM 
    folio_inventory.holdings_record
    LEFT JOIN locations AS permanent_location ON permanent_location.location_id = jsonb_extract_path_text(holdings_record.jsonb, 'permanentLocationId') :: UUID
    LEFT JOIN locations AS temporary_location ON temporary_location.location_id = jsonb_extract_path_text(holdings_record.jsonb, 'temporaryLocationId') :: UUID
    LEFT JOIN locations AS effective_location ON effective_location.location_id = jsonb_extract_path_text(holdings_record.jsonb, 'effectiveLocationId') :: UUID

[top]

2.2.2.4. Holdings type

The holdings record can have a type. Examples can be: Monograph, Electronic, Serial, Multi-part monograph, Physical. Since only the UUID of the type is in the holding record, it still has to be linked to the holding type record in a separate table via the UUID. The following example shows how to do this.

Example:

SELECT 
    holdings_record.id AS holdings_record_id,
    jsonb_extract_path_text(holdings_record.jsonb, 'holdingsTypeId') :: UUID AS holding_type_id,
    jsonb_extract_path_text(holdings_type.jsonb, 'name') AS holding_type_name,
    jsonb_extract_path_text(holdings_type.jsonb, 'source') AS holding_type_source
FROM 
    folio_inventory.holdings_record
    LEFT JOIN folio_inventory.holdings_type ON holdings_type.id = jsonb_extract_path_text(holdings_record.jsonb, 'holdingsTypeId') :: UUID

Sample output:

holdings_record_id	holding_type_id	holding_type_name	holding_type_source
1be016fd-9374-471d-8d4c-dd18db29ef74	03c9c400-b9e3-4a07-ac0e-05ab470233ed	Monograph	folio

[top]

2.2.2.5. Electronic access informations

If you have an electronic resource, you can have additional informations about the electronic access in the holdings record. You can querying this informations with the following sql statement.

Example:

SELECT
    holdings_record.instanceid AS instance_id,
    holdings_record.id AS holdings_id,
    jsonb_extract_path_text(holdings_record.jsonb, 'hrid') AS holdings_hrid,
    jsonb_extract_path_text(electronic_access.jsonb, 'relationshipId') :: UUID AS relationship_id,
    jsonb_extract_path_text(electronic_access_relationship.jsonb, 'name') AS relationship_name,
    jsonb_extract_path_text(electronic_access.jsonb, 'uri') AS uri,
    jsonb_extract_path_text(electronic_access.jsonb, 'linkText') AS link_text,
    jsonb_extract_path_text(electronic_access.jsonb, 'materialsSpecification') AS material_specification,
    jsonb_extract_path_text(electronic_access.jsonb, 'publicNote') AS public_note,
    electronic_access.ordinality AS electronic_access_ordinality
FROM
    folio_inventory.holdings_record
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(holdings_record.jsonb, 'electronicAccess')) WITH ORDINALITY AS electronic_access (jsonb)
    LEFT JOIN folio_inventory.electronic_access_relationship ON electronic_access_relationship.id = jsonb_extract_path_text(electronic_access.jsonb, 'relationshipId') :: UUID

[top]

2.2.3. Statistic codes

You can enter statistic codes to a holdings record.

Example:

WITH statistical_code_ids AS (
    SELECT 
        holdings_record.id AS holdings_record_id,
        jsonb_array_elements_text(jsonb_extract_path(holdings_record.jsonb, 'statisticalCodeIds')) :: UUID AS statistical_code_id
    FROM 
        folio_inventory.holdings_record
)
SELECT 
    holdings_record_id,
    statistical_code_id,
    jsonb_extract_path_text(statistical_code.jsonb, 'code') AS statistical_code,
    jsonb_extract_path_text(statistical_code.jsonb, 'name') AS statistical_code_name,
    jsonb_extract_path_text(statistical_code.jsonb, 'source') AS statistical_code_source,
    jsonb_extract_path_text(statistical_code.jsonb, 'statisticalCodeTypeId') :: UUID AS statistical_code_type_id,
    jsonb_extract_path_text(statistical_code_type.jsonb, 'name') AS statistical_code_type_name,
    jsonb_extract_path_text(statistical_code_type.jsonb, 'source') AS statistical_code_type_source
FROM 
    statistical_code_ids
    LEFT JOIN folio_inventory.statistical_code ON statistical_code.id = statistical_code_ids.statistical_code_id
    LEFT JOIN folio_inventory.statistical_code_type ON statistical_code_type.id = jsonb_extract_path_text(statistical_code.jsonb, 'statisticalCodeTypeId') :: UUID

[top]

2.3. Items

A item data record representing a single item. It contains information needed to identify and track a single item or piece, such as barcode, availability, and material type.

[top]

2.3.1. Metadata to the item record

Every record in FOLIO has metadata. The metadata for a item record can be queried as follows. Please note: There can be very much records in the database. You should filter out specific item records with a WHERE clause.

Example:

SELECT 
    item.id AS item_id,
    jsonb_extract_path_text(item.jsonb, '_version') :: INTEGER AS version,
    COALESCE(jsonb_extract_path_text(item.jsonb, 'discoverySuppress') :: BOOLEAN, FALSE) AS discovery_suppress,
    jsonb_extract_path_text(item.jsonb, 'metadata', 'createdDate') :: TIMESTAMPTZ AS created_date,
    jsonb_extract_path_text(item.jsonb, 'metadata', 'updatedDate') :: TIMESTAMPTZ AS updated_date,
    jsonb_extract_path_text(item.jsonb, 'metadata', 'createdByUserId') :: UUID AS created_by_user_id,
    jsonb_extract_path_text(item.jsonb, 'metadata', 'updatedByUserId') :: UUID AS updated_by_user_id
FROM 
    folio_inventory.item

[top]

2.3.2. Item record

2.3.2.1. Item information

The most informations about an item record can querying via this sql statement. This is just an exemple with a selection of properties from the item record. The Reporting SIG created a derived table item_ext that can also be used.

SELECT
    item.id                                                                                AS item_id,
    jsonb_extract_path_text(item.jsonb, 'hrid')                                            AS item_hrid, 
    jsonb_extract_path_text(item.jsonb, 'accessionNumber')                                 AS accession_number,
    jsonb_extract_path_text(item.jsonb, 'barcode')                                         AS barcode,
    jsonb_extract_path_text(item.jsonb, 'chronology')                                      AS chronology,
    jsonb_extract_path_text(item.jsonb, 'copyNumber')                                      AS copy_number,
    jsonb_extract_path_text(item.jsonb, 'enumeration')                                     AS enumeration,
    jsonb_extract_path_text(item.jsonb, 'volume')                                          AS volume,
    jsonb_extract_path_text(item.jsonb, 'inTransitDestinationServicePointId') :: UUID      AS in_transit_destination_service_point_id,
    jsonb_extract_path_text(item_in_transit_destination_service_point.jsonb, 'name')       AS in_transit_destination_service_point_name,
    jsonb_extract_path_text(item.jsonb, 'itemIdentifier')                                  AS item_identifier,
    jsonb_extract_path_text(item.jsonb, 'itemLevelCallNumber')                             AS item_level_call_number,
    jsonb_extract_path_text(item.jsonb, 'itemLevelCallNumberTypeId')::uuid                 AS item_level_call_number_type_id,
    jsonb_extract_path_text(item_call_number_type.jsonb, 'name')                           AS call_number_type_name,
    jsonb_extract_path_text(item.jsonb, 'effectiveCallNumberComponents', 'prefix')         AS effective_call_number_prefix,
    jsonb_extract_path_text(item.jsonb, 'effectiveCallNumberComponents', 'callNumber')     AS effective_call_number,
    jsonb_extract_path_text(item.jsonb, 'effectiveCallNumberComponents', 'suffix')         AS effective_call_number_suffix,
    jsonb_extract_path_text(item.jsonb, 'effectiveCallNumberComponents', 'typeId') :: UUID AS effective_call_number_type_id,
    jsonb_extract_path_text(effective_call_number_type.jsonb, 'name')                      AS effective_call_number_type_name,
    jsonb_extract_path_text(item.jsonb, 'itemDamagedStatusId') :: UUID                     AS item_damaged_status_id,
    jsonb_extract_path_text(item_damaged_status.jsonb, 'name')                             AS damaged_status_name,
    item.materialtypeid                                                                    AS material_type_id,
    jsonb_extract_path_text(material_type.jsonb, 'name')                                   AS material_type_name,
    jsonb_extract_path_text(item.jsonb, 'numberOfPieces')                                  AS number_of_pieces,
    jsonb_extract_path_text(item.jsonb, 'numberOfMissingPieces')                           AS number_of_missing_pieces,
    item.permanentloantypeid                                                               AS permanent_loan_type_id,
    jsonb_extract_path_text(item_permanent_loan_type.jsonb, 'name')                        AS permanent_loan_type_name,
    item.temporaryloantypeid                                                               AS temporary_loan_type_id,
    jsonb_extract_path_text(item_temporary_loan_type.jsonb, 'name')                        AS temporary_loan_type_name,
    item.permanentlocationid                                                               AS permanent_location_id,
    jsonb_extract_path_text(item_permanent_location.jsonb, 'name')                         AS permanent_location_name,
    item.temporarylocationid                                                               AS temporary_location_id,
    jsonb_extract_path_text(item_temporary_location.jsonb, 'name')                         AS temporary_location_name,
    item.effectivelocationid                                                               AS effective_location_id,
    jsonb_extract_path_text(item_effective_location.jsonb, 'name')                         AS effective_location_name,
    jsonb_extract_path_text(item.jsonb, 'descriptionOfPieces')                             AS description_of_pieces,
    jsonb_extract_path_text(item.jsonb, 'status', 'date')                                  AS status_date,
    jsonb_extract_path_text(item.jsonb, 'status', 'name')                                  AS status_name,
    item.holdingsrecordid                                                                  AS holdings_record_id,
    COALESCE(jsonb_extract_path_text(item.jsonb, 'discoverySuppress') :: BOOLEAN, FALSE)   AS discovery_suppress
FROM
    folio_inventory.item
    LEFT JOIN folio_inventory.service_point        AS item_in_transit_destination_service_point ON item_in_transit_destination_service_point.id = jsonb_extract_path_text(item.jsonb, 'inTransitDestinationServicePointId') :: UUID
    LEFT JOIN folio_inventory.call_number_type     AS item_call_number_type                     ON item_call_number_type.id                     = jsonb_extract_path_text(item.jsonb, 'itemLevelCallNumberTypeId') :: UUID
    LEFT JOIN folio_inventory.call_number_type     AS effective_call_number_type                ON effective_call_number_type.id                = jsonb_extract_path_text(item.jsonb, 'effectiveCallNumberComponents', 'typeId') :: UUID
    LEFT JOIN folio_inventory.item_damaged_status                                               ON item_damaged_status.id                       = jsonb_extract_path_text(item.jsonb, 'itemDamagedStatusId') :: UUID
    LEFT JOIN folio_inventory.material_type                                                     ON material_type.id                             = item.materialtypeid
    LEFT JOIN folio_inventory.loan_type            AS item_permanent_loan_type                  ON item_permanent_loan_type.id                  = item.permanentloantypeid
    LEFT JOIN folio_inventory.loan_type            AS item_temporary_loan_type                  ON item_temporary_loan_type.id                  = item.temporaryloantypeid
    LEFT JOIN folio_inventory.location             AS item_permanent_location                   ON item_permanent_location.id                   = item.permanentlocationid
    LEFT JOIN folio_inventory.location             AS item_temporary_location                   ON item_temporary_location.id                   = item.temporarylocationid
    LEFT JOIN folio_inventory.location             AS item_effective_location                   ON item_effective_location.id                   = item.effectivelocationid

[top]

2.3.2.2. Notes

2.3.2.2.1. Notes

The notes are stored in an array. The following example show how you can extract the data.

Example:

SELECT
    item.id                                                                       AS item_id,
    jsonb_extract_path_text(item.jsonb, 'hrid')                                   AS item_hrid,
    item.holdingsrecordid                                                         AS holdings_record_id,
    jsonb_extract_path_text(notes.jsonb, 'itemNoteTypeId') :: UUID                AS note_type_id,
    jsonb_extract_path_text(item_note_type.jsonb, 'name')                         AS note_type_name,
    jsonb_extract_path_text(notes.jsonb, 'note')                                  AS note,
    COALESCE(jsonb_extract_path_text(notes.jsonb, 'staffOnly') :: BOOLEAN, FALSE) AS staff_only,
    notes.ordinality                                                              AS note_ordinality
FROM
    folio_inventory.item
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(jsonb, 'notes')) WITH ORDINALITY AS notes (jsonb)
    LEFT JOIN folio_inventory.item_note_type ON item_note_type.id = jsonb_extract_path_text(notes.jsonb, 'itemNoteTypeId') :: UUID

Sample output:

Attribute	Output
item_id	62f5aa31-232b-4291-8094-9e51c4966a97
item_hrid	it00000000001
holdings_record_id	bcf1980e-fae4-47fd-b0f9-cd95fc91e9ee
note_type_id	8d0a5eca-25de-4391-81a9-236eeefdd20b
note_type_name	Note
note	This is a note.
staff_only	True False
note_ordinality	1

[top]

2.3.2.2.2. Administrative notes

The administrative notes are stored in an array. The following example show how you can extract the data.

Example:

SELECT 
    item.id AS item_id,
    administrative_notes.jsonb AS administrative_notes
FROM 
    folio_inventory.item
    CROSS JOIN LATERAL jsonb_array_elements_text(jsonb_extract_path(item.jsonb, 'administrativeNotes')) WITH ORDINALITY AS administrative_notes (jsonb) 

Sample output:

item_id	administrative_notes
62f5aa31-232b-4291-8094-9e51c4966a97	Here is an administrative note
62f5aa31-232b-4291-8094-9e51c4966a97	Head Librarian approved read!
bd403a74-0527-41ee-b248-97be2aba54f0	Coming down from the TOP -- needed as soon as possible

[top]

2.3.2.3. Electronic access informations

If you have an electronic resource as an item, you can have additional informations about the electronic access in the item record. You can querying this informations with the following sql statement.

Example:

SELECT
    item.id AS item_id,
    jsonb_extract_path_text(item.jsonb, 'hrid') AS item_hrid,
    jsonb_extract_path_text(electronic_access.jsonb, 'linkText') AS link_text,
    jsonb_extract_path_text(electronic_access.jsonb, 'materialsSpecification') AS materials_specification,
    jsonb_extract_path_text(electronic_access.jsonb, 'publicNote') AS public_note,
    jsonb_extract_path_text(electronic_access.jsonb, 'relationshipId') :: UUID AS relationship_id,
    jsonb_extract_path_text(electronic_access_relationship.jsonb, 'name') AS relationship_name,
    jsonb_extract_path_text(electronic_access.jsonb, 'uri') AS uri
FROM
    folio_inventory.item
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(jsonb, 'electronicAccess')) AS electronic_access(jsonb)
    LEFT JOIN folio_inventory.electronic_access_relationship ON electronic_access_relationship.id = jsonb_extract_path_text(electronic_access.jsonb, 'relationshipId') :: UUID

[top]

2.3.3. Statistic codes

You can enter statistic codes to an item record.

Example:

WITH statistical_code_ids AS (
    SELECT 
        item.id AS item_id,
        jsonb_array_elements_text(jsonb_extract_path(item.jsonb, 'statisticalCodeIds')) :: UUID AS statistical_code_id
    FROM 
        folio_inventory.item
)
SELECT 
    item_id,
    statistical_code_id,
    jsonb_extract_path_text(statistical_code.jsonb, 'code') AS statistical_code,
    jsonb_extract_path_text(statistical_code.jsonb, 'name') AS statistical_code_name,
    jsonb_extract_path_text(statistical_code.jsonb, 'source') AS statistical_code_source,
    jsonb_extract_path_text(statistical_code.jsonb, 'statisticalCodeTypeId') :: UUID AS statistical_code_type_id,
    jsonb_extract_path_text(statistical_code_type.jsonb, 'name') AS statistical_code_type_name,
    jsonb_extract_path_text(statistical_code_type.jsonb, 'source') AS statistical_code_type_source
FROM 
    statistical_code_ids
    LEFT JOIN folio_inventory.statistical_code ON statistical_code.id = statistical_code_ids.statistical_code_id
    LEFT JOIN folio_inventory.statistical_code_type ON statistical_code_type.id = jsonb_extract_path_text(statistical_code.jsonb, 'statisticalCodeTypeId') :: UUID

[top]