Add filtering by primary license to dependency aggregations

What does this MR do and why?

Add filtering by primary license to dependency aggregations

Allows for filtering by the first license belonging to an sbom occurrence.

Fixes: #470098 (closed)

Database Queries

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/29614/commands/91930

WITH namespaces AS MATERIALIZED (
    SELECT
        namespaces.traversal_ids
    FROM
        namespaces
    WHERE
        namespaces.type = 'Group' AND
        traversal_ids @> '{9970}'
)
SELECT
    outer_occurrences.component_id,
    outer_occurrences.component_version_id,
    min( outer_occurrences.id )::pg_catalog.int8 AS id,
    min( outer_occurrences.package_manager ) AS package_manager,
    min( outer_occurrences.input_file_path ) AS input_file_path,
    COALESCE(
        jsonb_agg( outer_occurrences.licenses -> 0 ) FILTER ( WHERE outer_occurrences.licenses -> 0 IS NOT NULL ),
        '[]'
    ) AS licenses,
    sum( counts.occurrence_count )::pg_catalog.int4 AS occurrence_count,
    sum( counts.vulnerability_count )::pg_catalog.int4 AS vulnerability_count,
    sum( counts.project_count )::pg_catalog.int4 AS project_count
FROM
    (
        SELECT
            DISTINCT ON ( inner_occurrences.component_id, inner_occurrences.component_version_id )
            inner_occurrences.id,
            inner_occurrences.created_at,
            inner_occurrences.updated_at,
            inner_occurrences.component_version_id,
            inner_occurrences.project_id,
            inner_occurrences.pipeline_id,
            inner_occurrences.source_id,
            inner_occurrences.commit_sha,
            inner_occurrences.component_id,
            inner_occurrences.uuid,
            inner_occurrences.package_manager,
            inner_occurrences.component_name,
            inner_occurrences.input_file_path,
            inner_occurrences.licenses,
            inner_occurrences.highest_severity,
            inner_occurrences.vulnerability_count,
            inner_occurrences.source_package_id,
            inner_occurrences.archived,
            inner_occurrences.traversal_ids,
            inner_occurrences.ancestors
        FROM
            namespaces,
            LATERAL (
                SELECT
                    DISTINCT ON ( sbom_occurrences.component_id, sbom_occurrences.component_version_id )
                    sbom_occurrences.id,
                    sbom_occurrences.created_at,
                    sbom_occurrences.updated_at,
                    sbom_occurrences.component_version_id,
                    sbom_occurrences.project_id,
                    sbom_occurrences.pipeline_id,
                    sbom_occurrences.source_id,
                    sbom_occurrences.commit_sha,
                    sbom_occurrences.component_id,
                    sbom_occurrences.uuid,
                    sbom_occurrences.package_manager,
                    sbom_occurrences.component_name,
                    sbom_occurrences.input_file_path,
                    sbom_occurrences.licenses,
                    sbom_occurrences.highest_severity,
                    sbom_occurrences.vulnerability_count,
                    sbom_occurrences.source_package_id,
                    sbom_occurrences.archived,
                    sbom_occurrences.traversal_ids,
                    sbom_occurrences.ancestors
                FROM
                    sbom_occurrences
                WHERE
                    sbom_occurrences.traversal_ids = namespaces.traversal_ids::pg_catalog.int8[] AND
                    sbom_occurrences.archived = false AND
                    (
                        ( ( licenses -> 0 ) ->> 'spdx_identifier' )::text = 'Apache-2.0' OR
                        licenses = '[]'
                    )
                ORDER BY
                    sbom_occurrences.component_id ASC,
                    sbom_occurrences.component_version_id ASC
                LIMIT 21
            ) AS inner_occurrences
        ORDER BY
            inner_occurrences.component_id ASC,
            inner_occurrences.component_version_id ASC
        LIMIT 21
    ) AS outer_occurrences,
    LATERAL (
        SELECT
            count( project_id ) AS occurrence_count,
            count( project_id ) AS project_count,
            sum( vulnerability_count ) AS vulnerability_count
        FROM
            sbom_occurrences
        WHERE
            traversal_ids >= '{9970}' AND
            traversal_ids < '{9971}' AND
            sbom_occurrences.archived = false AND
            sbom_occurrences.component_version_id = outer_occurrences.component_version_id
    ) AS counts
GROUP BY
    outer_occurrences.component_id,
    outer_occurrences.component_version_id
ORDER BY
    min( outer_occurrences.component_id ) ASC,
    min( outer_occurrences.component_version_id ) ASC;

Time: 183.735 ms
  - planning: 6.364 ms
  - execution: 177.371 ms
    - I/O read: 114.640 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 27010 (~211.00 MiB) from the buffer pool
  - reads: 92 (~736.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 18 (~144.00 KiB)
  - writes: 0

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Screen_Recording_2024-07-02_at_5.29.57_PM

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

1. Go to the group-level dependency list
2. Use the "Licenses" filter
3. It works