Once you are connected to the Cornell LDP and running reports, you may want to include SQL that queries MARC data. Here are some samples for querying MARC data.


Sample 1: Obtain data in MARC 336, 337, and 338


One query that is very frequent is that we need to have the content and tags (and often indicators) for multiple MARC tags in the results. This example creates a report where the columns are bib_id, 336 subfied and content, 337 subfield and content, and 338 subfield and content. This is with the understanding that these 3 MARC fields can repeat or one bib_id can have 1+ 336, 337, or 338. 


SELECT
    marc.bib_id,
    marc.tag,
    string_agg('$'::varchar || marc.sf || marc.content, '') AS sf_content
FROM
    folio_marc_test.folio_source_record."__marc" AS marc
WHERE
    marc.tag IN ('336','337','338')
GROUP BY
    marc.bib_id,
    marc.tag
;

Sample 2: Headings from MARC 100, 110, 111

WITH tags AS (select
m.bib_id,
m.tag AS tag_number,
'$'::varchar || m.sf || m.CONTENT AS tag
from folio_source_record.__marc m
where m.tag in ('100','110','111')
limit 100
)
SELECT 
tags.bib_id,
tags.tag_number,
string_agg(tag, '') AS header
FROM tags
GROUP BY 
bib_id,
tag_number
;


Sample 3

WITH tags AS (select
m.bib_id,
m.tag AS tag_number,
'$'::varchar || m.sf || m.CONTENT AS tag
from folio_source_record.__marc m
where m.tag in ('245')
limit 100
)

SELECT
tags.bib_id,
tags.tag_number,
string_agg(tag, '') AS header
FROM tags

GROUP BY
bib_id,
tag_number
;



  • No labels