Cookbook: UM - folio-org/folio-analytics GitHub Wiki

User Management

UM reporting includes all reports relating to user management. In this cookbook, some connections and specific SQL statements should be explained.

Table of Contents

1. Querying user data to get user informations

If you want to retrieve user data in FOLIO, you should think carefully about what data you need beforehand. Some categories of a user record can appear multiple times in the FOLIO UI. This is the case, for example, when you enter addresses. You can enter multiple addresses for one user. Such data is stored in an array in FOLIO. If you querying these arrays, it can happen that you get multiple lines as a result for a user record.

Below we will show you some examples of how you can read these arrays. These queries are provided by Metadb as derived tables.

[top]

1.1. Users addresses

User addresses are stored in arrays because you can enter multiple addresses for one user.

Example:

SELECT
    users.id AS user_id,
    jsonb_extract_path_text(addresses.jsonb, 'id') AS address_id,
    jsonb_extract_path_text(addresses.jsonb, 'countryId') AS address_country_id,
    jsonb_extract_path_text(addresses.jsonb, 'addressLine1') AS address_line_1,
    jsonb_extract_path_text(addresses.jsonb, 'addressLine2') AS address_line_2,
    jsonb_extract_path_text(addresses.jsonb, 'city') AS address_city,
    jsonb_extract_path_text(addresses.jsonb, 'region') AS address_region,
    jsonb_extract_path_text(addresses.jsonb, 'postalCode') AS address_postal_code,
    jsonb_extract_path_text(addresses.jsonb, 'addressTypeId')::uuid AS address_type_id,
    addresstype__t.address_type AS address_type_name,
    addresstype__t.desc AS address_type_description,
    jsonb_extract_path_text(addresses.jsonb, 'primaryAddress')::boolean AS is_primary_address
FROM
    folio_users.users
    CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(jsonb, 'personal', 'addresses')) AS addresses (jsonb)
    LEFT JOIN folio_users.addresstype__t ON addresstype__t.id = jsonb_extract_path_text(addresses.jsonb, 'addressTypeId')::uuid

[top]

1.2. users departments

Users can be assigned to departments. If you would like to see these users, please use the example below.

Example:

WITH departments_array AS (
    SELECT
        users.id AS user_id,
        departments.jsonb #>> '{}' AS department_id,
        departments.ordinality AS department_ordinality
    FROM
        folio_users.users
        CROSS JOIN LATERAL jsonb_array_elements(jsonb_extract_path(jsonb, 'departments')) WITH ORDINALITY AS departments (jsonb)
)
SELECT
    departments_array.user_id,
    departments_array.department_id::uuid,
    departments_array.department_ordinality,
    departments__t.name AS department_name,
    departments__t.code AS department_code
FROM
    departments_array
    LEFT JOIN folio_users.departments__t ON departments__t.id= departments_array.department_id::uuid

[top]

1.3. Users groups

Users can be assigned to groups. If you would like to see these users, please use the example below.

Example:

SELECT
    users.id AS user_id,
    users__t.patron_group :: UUID AS group_id,
    groups__t.desc AS group_description,
    groups__t.group AS group_name,
    users__t.active,
    users__t.barcode,
    users__t.enrollment_date,
    users__t.expiration_date,    
    jsonb_extract_path_text(users.jsonb, 'personal', 'lastName') AS user_last_name,
    jsonb_extract_path_text(users.jsonb, 'personal', 'firstName') AS user_first_name,
    jsonb_extract_path_text(users.jsonb, 'personal', 'middleName') AS user_middle_name
FROM
    folio_users.users
    LEFT JOIN folio_users.users__t ON users__t.id = users.id  
    JOIN folio_users.groups__t ON groups__t.id = users__t.patron_group

[top]

2. Fee fines

Users can get fees with different types of fee fines. The following example shows how you can query this information. You can also find the query as derived table feesfines_accounts_actions.

Example:

SELECT
    fine_account.id AS fine_account_id,
    jsonb_extract_path_text(fine_account.jsonb, 'amount')::numeric(12,2) AS fine_account_amount,
    jsonb_extract_path_text(fine_account.jsonb, 'dateCreated')::timestamptz AS fine_date, 
    jsonb_extract_path_text(fine_account.jsonb, 'dateUpdated')::timestamptz AS fine_updated_date,
    jsonb_extract_path_text(fine_account.jsonb, 'feeFineId')::uuid AS fee_fine_id,
    jsonb_extract_path_text(fine_account.jsonb, 'ownerId')::uuid AS owner_id,
    jsonb_extract_path_text(fine_account.jsonb, 'feeFineOwner') AS fee_fine_owner,
    jsonb_extract_path_text(fine_account.jsonb, 'feeFineType') AS fee_fine_type,
    jsonb_extract_path_text(fine_account.jsonb, 'materialTypeId')::uuid AS material_type_id,
    jsonb_extract_path_text(fine_account.jsonb, 'materialType') AS material_type,
    jsonb_extract_path_text(fine_account.jsonb, 'payment_status') AS payment_status,
    jsonb_extract_path_text(fine_account.jsonb, 'status', 'name') AS fine_status, -- open or closed
    jsonb_extract_path_text(fine_account.jsonb, 'userId')::uuid AS account_user_id,
    feefineactions.id AS transaction_id,
    jsonb_extract_path_text(feefineactions.jsonb, 'accountId')::uuid AS account_id,
    jsonb_extract_path_text(feefineactions.jsonb, 'amountAction')::numeric(12,2) AS transaction_amount,
    jsonb_extract_path_text(feefineactions.jsonb, 'balance')::numeric(12,2) AS account_balance,
    jsonb_extract_path_text(feefineactions.jsonb, 'typeAction') AS type_action,
    jsonb_extract_path_text(feefineactions.jsonb, 'dateAction')::timestamptz AS transaction_date,
    jsonb_extract_path_text(feefineactions.jsonb, 'createdAt') AS transaction_location,
    jsonb_extract_path_text(feefineactions.jsonb, 'transactionInformation') AS transaction_information,
    jsonb_extract_path_text(feefineactions.jsonb, 'source') AS operator_id,
    jsonb_extract_path_text(feefineactions.jsonb, 'paymentMethod') AS payment_method,
    users__t.id AS user_id,
    users__t.patron_group AS user_patron_group_id,
    user_groups.id AS patron_group_id,
    user_groups.group AS patron_group_name
FROM
    folio_feesfines.accounts AS fine_account
    LEFT JOIN folio_feesfines.feefineactions ON fine_account.id = jsonb_extract_path_text(feefineactions.jsonb, 'accountId')::uuid
    LEFT JOIN folio_users.users__t ON jsonb_extract_path_text(fine_account.jsonb, 'userId')::uuid = users__t.id
    LEFT JOIN folio_users.groups__t AS user_groups ON users__t.patron_group = user_groups.id
ORDER BY 
    fine_account_id, 
    transaction_date

[top]

3. Querying user data to see user permissions

In FOLIO you can give certain permissions to a user. This is usually done via permission sets. In the following example you can see how you can retrieve these permission sets and who they have been assigned to.

Example:

WITH permissions_users AS (
    SELECT
        jsonb_array_elements_text(jsonb->'permissions') AS user_permissions,
        jsonb_extract_path_text(jsonb, 'userId')::uuid AS user_id
    FROM
        folio_permissions.permissions_users
)
SELECT
    permissions.id AS permission_id,
    jsonb_extract_path_text(permissions.jsonb, 'displayName') AS permission_name,
    jsonb_extract_path_text(permissions.jsonb, 'description') AS permission_description,
    users.id AS user_id,
    jsonb_extract_path_text(users.jsonb, 'personal', 'lastName') AS user_lastname,
    jsonb_extract_path_text(users.jsonb, 'personal', 'firstName') AS user_firstname,
    jsonb_extract_path_text(users.jsonb, 'barcode') AS user_barcode,
    jsonb_extract_path_text(users.jsonb, 'username') AS user_username,
    CASE WHEN jsonb_extract_path_text(users.jsonb, 'active')::boolean THEN 'active'
        ELSE 'inactive'
    END AS user_status,
    jsonb_extract_path_text(user_groups.jsonb, 'group') AS user_patron_group,
    jsonb_extract_path_text(users.jsonb, 'expirationDate') AS user_expiration_date
FROM
    folio_permissions.permissions AS permissions
    JOIN permissions_users ON permissions_users.user_permissions = permissions.id::varchar
    LEFT JOIN folio_users.users AS users ON users.id = permissions_users.user_id
    LEFT JOIN folio_users.groups AS user_groups ON user_groups.id = jsonb_extract_path_text(users.jsonb, 'patronGroup')::uuid
ORDER BY
    permission_name,
    permission_description,
    user_lastname

[top]

4. Querying user data for statistics

If you want to make statistical queries, you can use simply the GROUP BY functionality. But it could also a good idea to use so-called window functions. With window functions you can split the dataset into different partitions. Then you can compare each record in each partition with an aggregate value or a calculated value of each partition. You can specify the PARTITION BY clause in the OVER function.

Below are 2 examples of how you can use this.

[top]

4.1. Number of users by user group

If you want to see the number of users by user group.

Example:

SELECT  
    users__t.patron_group :: UUID AS group_id,
    groups__t.desc AS group_description,
    groups__t.group AS group_name,
    COUNT(users__t.id) AS count_by_group
FROM
    folio_users.users__t  
    LEFT JOIN folio_users.groups__t ON groups__t.id = users__t.patron_group
GROUP BY 
    users__t.patron_group,
    groups__t.desc,
    groups__t.group

Sample Output:

group_id group_description group_name count_by_group
3684a786-6671-4268-8ed0-9db82ebca60b Staff Member staff 11
503a81cd-6c26-400f-b620-14c08943697c Faculty Member faculty 1
bdc2b6d4-5ceb-4a12-ab46-249b9a68473e Undergradate Student undergrad 1
NULL NULL NULL 5

[top]

4.2. Average age by user group

If you want to see the average age by user group.

Example:

SELECT
    users.id AS user_id,
    users__t.patron_group :: UUID AS group_id,
    groups__t.desc AS group_description,
    groups__t.group AS group_name,
    users__t.active,
    users__t.barcode,
    users__t.enrollment_date,
    users__t.expiration_date,    
    jsonb_extract_path_text(users.jsonb, 'personal', 'lastName') AS user_last_name,
    jsonb_extract_path_text(users.jsonb, 'personal', 'firstName') AS user_first_name,
    jsonb_extract_path_text(users.jsonb, 'personal', 'middleName') AS user_middle_name,
    COALESCE(jsonb_extract_path_text(users.jsonb, 'personal', 'dateOfBirth'), '1900-01-01') :: date AS user_date_of_birth,
    AGE(COALESCE(jsonb_extract_path_text(users.jsonb, 'personal', 'dateOfBirth'), '1900-01-01') :: date) AS age,
    AVG(AGE(COALESCE(jsonb_extract_path_text(users.jsonb, 'personal', 'dateOfBirth'), '1900-01-01') :: date)) OVER (PARTITION BY groups__t.group) AS average_age_by_group,
    MAX(AGE(COALESCE(jsonb_extract_path_text(users.jsonb, 'personal', 'dateOfBirth'), '1900-01-01') :: date)) OVER (PARTITION BY groups__t.group) AS max_age_by_group,
    MIN(AGE(COALESCE(jsonb_extract_path_text(users.jsonb, 'personal', 'dateOfBirth'), '1900-01-01') :: date)) OVER (PARTITION BY groups__t.group) AS min_age_by_group
FROM
    folio_users.users
    LEFT JOIN folio_users.users__t ON users__t.id = users.id  
    LEFT JOIN folio_users.groups__t ON groups__t.id = users__t.patron_group
GROUP BY 
    users.id,
    users__t.patron_group,
    groups__t.desc,
    groups__t.group,
    users__t.active,
    users__t.barcode,
    users__t.enrollment_date,
    users__t.expiration_date,    
    jsonb_extract_path_text(users.jsonb, 'personal', 'lastName'),
    jsonb_extract_path_text(users.jsonb, 'personal', 'firstName'),
    jsonb_extract_path_text(users.jsonb, 'personal', 'middleName'),
    jsonb_extract_path_text(users.jsonb, 'personal', 'dateOfBirth'),
    jsonb_extract_path_text(users.jsonb, 'personal', 'dateOfBirth')

Sample Output (a part of it):

user_id group_id group_name user_date_of_birth age average_age_by_group max_age_by_group min_age_by_group
caf3bb4a-b6b2-4e91-b14c-fbec128ac509 503a81cd-6c26-400f-b620-14c08943697c faculty 1900-01-01 124 years 2 mons 124 years 2 mons 124 years 2 mons 124 years 2 mons
67adb68e-232f-4cdf-8bce-faf09093071a 3684a786-6671-4268-8ed0-9db82ebca60b staff 1987-01-01 37 years 2 mons 116 years 3 mons 2 days 17:27:16.3872 124 years 2 mons 37 years 2 mons
e45fccb3-1f39-4125-a39d-a573cda300d9 3684a786-6671-4268-8ed0-9db82ebca60b staff 1900-01-01 124 years 2 mons 116 years 3 mons 2 days 17:27:16.3872 124 years 2 mons 37 years 2 mons

As you can see, age is calculated depending on group membership. Please note: In this example, NULL values ​​have been replaced. Usually the date of birth should be present.

Example (without user data):

WITH user_date_of_birth AS (
    SELECT 
        users.id AS user_id,
        AGE(COALESCE(jsonb_extract_path_text(users.jsonb, 'personal', 'dateOfBirth'), '1900-01-01') :: date) AS date_of_birth
    FROM 
        folio_users.users
)
SELECT
    users__t.patron_group :: UUID AS group_id,
    groups__t.desc AS group_description,
    groups__t.group AS group_name,
    AVG(user_date_of_birth.date_of_birth) AS average_age_by_group,
    MAX(user_date_of_birth.date_of_birth) AS max_age_by_group,
    MIN(user_date_of_birth.date_of_birth) AS min_age_by_group,
    COUNT(users__t.id) AS count_users 
FROM
    folio_users.users__t
    LEFT JOIN user_date_of_birth ON user_date_of_birth.user_id = users__t.id
    LEFT JOIN folio_users.groups__t ON groups__t.id = users__t.patron_group
GROUP BY 
    users__t.patron_group,
    groups__t.desc,
    groups__t.group

Sample Output:

group_id group_description group_name average_age_by_group max_age_by_group min_age_by_group count_users
3684a786-6671-4268-8ed0-9db82ebca60b Staff Member staff 116 years 3 mons 2 days 17:27:16.3872 124 years 2 mons 37 years 2 mons 11
503a81cd-6c26-400f-b620-14c08943697c Faculty Member faculty 124 years 2 mons 124 years 2 mons 124 years 2 mons 1
bdc2b6d4-5ceb-4a12-ab46-249b9a68473e Undergradate Student undergrad 124 years 2 mons 124 years 2 mons 124 years 2 mons 1
NULL NULL NULL 124 years 2 mons 124 years 2 mons 124 years 2 mons 5

[top]

4.3. Number of users by age and user category

If you want the number of users by age and user category, you must first calculate the age of each person in a CTE. After that you create another CTE for each age category and use the age previously calculated from the CTE as the basis for the WHERE clause. Then you need to add this CTE using Left Join to your main query where you have grouped the user groups.

Example:

WITH user_date_of_birth AS (
    SELECT 
        users.id AS user_id,
        AGE(jsonb_extract_path_text(users.jsonb, 'personal', 'dateOfBirth') :: date) AS date_of_birth
    FROM 
        folio_users.users
),
users_between_30_and_39 AS (
    SELECT 
        COUNT(users.id) AS counts,
        groups.id AS group_id
    FROM 
        folio_users.users
        LEFT JOIN user_date_of_birth ON user_date_of_birth.user_id = users.id
        LEFT JOIN folio_users.groups ON groups.id = jsonb_extract_path_text(users.jsonb, 'patronGroup') :: UUID
    WHERE 
        EXTRACT('YEAR' FROM date_of_birth) >=30 
        AND 
        EXTRACT('YEAR' FROM date_of_birth) <=39
    GROUP BY 
        groups.id
)
SELECT
    jsonb_extract_path_text(users.jsonb, 'patronGroup') :: UUID AS group_id,
    jsonb_extract_path_text(groups.jsonb, 'desc') AS group_description,
    jsonb_extract_path_text(groups.jsonb, 'group') AS group_name,
    AVG(user_date_of_birth.date_of_birth) AS average_age_by_group,
    MAX(user_date_of_birth.date_of_birth) AS max_age_by_group,
    MIN(user_date_of_birth.date_of_birth) AS min_age_by_group,
    COUNT(users.id) AS count_users,    
    COALESCE(users_between_30_and_39.counts, 0) AS cat30to39
FROM
    folio_users.users
    LEFT JOIN user_date_of_birth ON user_date_of_birth.user_id = users.id
    LEFT JOIN folio_users.groups ON groups.id = jsonb_extract_path_text(users.jsonb, 'patronGroup') :: UUID
    LEFT JOIN users_between_30_and_39 ON users_between_30_and_39.group_id = groups.id
GROUP BY 
    jsonb_extract_path_text(users.jsonb, 'patronGroup'),
    jsonb_extract_path_text(groups.jsonb, 'desc'),
    jsonb_extract_path_text(groups.jsonb, 'group'),
    users_between_30_and_39.counts

Sample Output:

group_id group_description group_name average_age_by_group max_age_by_group min_age_by_group count_users cat30to39
3684a786-6671-4268-8ed0-9db82ebca60b Staff Member staff 45 years 2 mons 57 years 4 mons 37 years 2 mons 11 1

[top]