gistfile1.txt

WITH entity_providers AS (
    SELECT 
        entity_ref,
        JSONB_AGG(DISTINCT provider_id) AS provider_ids
    FROM 
        staging_entity_fragments
    WHERE 
        expiredAt IS NULL OR expiredAt > CURRENT_TIMESTAMP
    GROUP BY 
        entity_ref
)
SELECT 
    ep.entity_ref,
    ep.provider_ids AS expected_provider_ids,
    (f.final_entity::jsonb)->'metadata'->'tags' AS actual_tags,
    JSONB_ARRAY_LENGTH(ep.provider_ids) AS expected_count,
    (
        SELECT COUNT(*)
        FROM jsonb_array_elements_text((f.final_entity::jsonb)->'metadata'->'tags') AS tag
        WHERE tag IN ('tag a', 'tag b')
    ) AS actual_count
FROM 
    entity_providers ep
JOIN 
    final_entities f ON ep.entity_ref = f.entity_ref
WHERE 
    JSONB_ARRAY_LENGTH(ep.provider_ids) != (
        SELECT COUNT(*)
        FROM jsonb_array_elements_text((f.final_entity::jsonb)->'metadata'->'tags') AS tag
        WHERE tag IN ('tag a', 'tag b')
    )
ORDER BY 
    ep.entity_ref;