[Archivesspace_Users_Group] Query to retrieve boxes with restricted AOs + local access restrict type

Tue Apr 19 13:32:50 EDT 2022

Olivia -
Not a database query, but in case it helps: I have had some success by searching in Manage Top Container using the restriction enumeration values.  For example, if you have used the local restriction "InProcessSpecColl", you can search that as a keyword.

Mileage will vary depending on the detail you need, especially given the pitfalls of keyword searches. But, this can work in a pinch if you are only trying to search within 1 repository at a time, and only need a list of the Boxes, Resources/Accessions, or other high level detail.  A Download CSV option is also available to export some of the result details, depending on which aspace version you're on.

Nancy

From: archivesspace_users_group-bounces at lyralists.lyrasis.org <archivesspace_users_group-bounces at lyralists.lyrasis.org> On Behalf Of Andrew Morrison
Sent: Tuesday, April 19, 2022 12:13 PM
To: archivesspace_users_group at lyralists.lyrasis.org
Subject: Re: [Archivesspace_Users_Group] Query to retrieve boxes with restricted AOs + local access restrict type

External Email - Exercise Caution

If you need to ascend the hierarchy of archival objects within collections, from the ones that link to containers, to the ones with the access restrictions, then you are probably going to need to write a stored procedure.

Andrew.

On 19/04/2022 15:38, Olivia S Solis wrote:
Hello all,

I'm spending an inordinately long time trying to form a query to retrieve all boxes attached to restricted archival objects in ASpace. I'm hoping someone in the group with more advanced database skills than I have can help me out.

We want to identify any box in a certain building that has restricted materials and the kind of local access restriction it has. Since we use local access restrictions, the AO that is linked to the box may not have the local access restriction applied through the accessrestrict note, but a parent AO will. That parent may or may not be its immediate parent AO. We only care about the collection title in the box, not the AO title.

I've come up with this query, which is already a mouthful, to retrieve all the boxes + their collections in the building I want (SRH2):

SELECT DISTINCT rr.id<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Frr.id%2F&data=05%7C01%7CKennedyN%40si.edu%7Ced206f4baa1647de2d6508da221f80c6%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637859817714793226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=nk%2BmdUeMDDPoIfICYeEmZEkbHWLiA2tgx0a7yUJRh%2Fg%3D&reserved=0>, rr.title, t.indicator, r.top_container_id, t.barcode, l.title

FROM instance as i

JOIN sub_container as s

ON i.id<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fi.id%2F&data=05%7C01%7CKennedyN%40si.edu%7Ced206f4baa1647de2d6508da221f80c6%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637859817714793226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=q4SLEjyA4n0SCKgcMDhy42NK3E7IeLGKBRZkIaZyOrw%3D&reserved=0> = s.instance_id

JOIN archival_object as a

ON a.id<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fa.id%2F&data=05%7C01%7CKennedyN%40si.edu%7Ced206f4baa1647de2d6508da221f80c6%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637859817714793226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=8%2FV0Kz8QtFEFIug45llUUkgEFKd1AUKsKogafHq2d6E%3D&reserved=0> = i.archival_object_id

JOIN top_container_link_rlshp as r

ON s.id<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fs.id%2F&data=05%7C01%7CKennedyN%40si.edu%7Ced206f4baa1647de2d6508da221f80c6%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637859817714793226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=QZEkrnzpqaut58MXw6ZBHgMZMb5Ak1TMpYOfFryibUA%3D&reserved=0> = r.sub_container_id

JOIN top_container as t

ON t.id<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Ft.id%2F&data=05%7C01%7CKennedyN%40si.edu%7Ced206f4baa1647de2d6508da221f80c6%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637859817714793226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=GarT1h395FHJJmXcVKT%2FMx93ZQQh7H4wqJ2%2FBBexFPU%3D&reserved=0> = r.top_container_id

LEFT JOIN resource as rr

ON rr.id<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Frr.id%2F&data=05%7C01%7CKennedyN%40si.edu%7Ced206f4baa1647de2d6508da221f80c6%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637859817714793226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=nk%2BmdUeMDDPoIfICYeEmZEkbHWLiA2tgx0a7yUJRh%2Fg%3D&reserved=0> = a.root_record_id

JOIN top_container_housed_at_rlshp as tcha

ON tcha.top_container_id = t.id<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Ft.id%2F&data=05%7C01%7CKennedyN%40si.edu%7Ced206f4baa1647de2d6508da221f80c6%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637859817714793226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=GarT1h395FHJJmXcVKT%2FMx93ZQQh7H4wqJ2%2FBBexFPU%3D&reserved=0>

JOIN location as l

ON l.id<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fl.id%2F&data=05%7C01%7CKennedyN%40si.edu%7Ced206f4baa1647de2d6508da221f80c6%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637859817714793226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=rpkYl05VCFAGQgAAyu5AK3LJC%2FDZEERzS%2FvDOvslBaE%3D&reserved=0> = tcha.location_id

WHERE l.building LIKE "SRH2"

On top of this I want the archival objects in the query to reflect only AOs that have a local access restriction or who have an immediate or distant parent AO with one. This is the query I have that will ID AOs with a local access restriction, which I realize I'll have to extract from the JSON that is in the notes column of the note table:

SELECT id, resource_id, archival_object_id, notes FROM note WHERE notes LIKE '%local_access_restriction_type":["%';

Any help would be appreciated. Thank you!

-Olivia

--
Olivia Solis, MSIS (she/her)
Metadata Coordinator
Dolph Briscoe Center for American History
The University of Texas at Austin
2300 Red River St. Stop D1100
Austin TX, 78712-1426
(512) 232-8013

_______________________________________________

Archivesspace_Users_Group mailing list

Archivesspace_Users_Group at lyralists.lyrasis.org<mailto:Archivesspace_Users_Group at lyralists.lyrasis.org>

http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Flyralists.lyrasis.org%2Fmailman%2Flistinfo%2Farchivesspace_users_group&data=05%7C01%7CKennedyN%40si.edu%7Ced206f4baa1647de2d6508da221f80c6%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637859817714793226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&sdata=EBhTj9hEHqJYVQog%2BgQKMjeKC86X0Ju93jIjPS0RX8I%3D&reserved=0>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20220419/a35acae3/attachment.html>