Query

Values where sources disagree

Current values for which another source states something that contradicts them (compared normalised: a different spelling or a less specific value is no contradiction), with the value the policy chose, the contradicting values it passed over, and how many others differ only in spelling or specificity.

The SQL (always shown and editable; runs on a read-only connection with a 5 second limit and at most 500 rows)

WITH d AS (
    SELECT * FROM v_current WHERE disagreeing_sources > 0
),
members AS (  -- every subject id that stands for one of those subjects (merged ones too): read by index
    SELECT subject_id AS member, subject_id FROM d
    UNION SELECT cs.subject_id, cs.canonical_id FROM canonical_subject cs WHERE cs.canonical_id IN (SELECT subject_id FROM d)
),
other AS (
    SELECT d.subject_id, d.attribute_id, jsonb_agg(DISTINCT o.value) AS other_values,
           array_agg(DISTINCT s.identifier) AS other_sources
    FROM d
    JOIN members m ON m.subject_id = d.subject_id
    JOIN observation x ON x.id = d.observation_id
    JOIN observation o ON o.subject_id = m.member AND o.attribute_id = d.attribute_id AND o.retracted_at IS NULL
                      AND (o.valid_during IS NULL OR o.valid_during @> now())
    JOIN source s ON s.id = o.source_id
    WHERE NOT values_compatible(COALESCE(o.compare_key, jsonb_build_object('#', encode(o.value_hash, 'hex'))),
                                COALESCE(x.compare_key, jsonb_build_object('#', encode(x.value_hash, 'hex'))))
      AND COALESCE(o.qualifier ->> 'address_kind', 'unknown') <> 'postal'
      AND NOT (COALESCE((o.qualifier ->> 'unparsed')::boolean, false)
               AND d.attribute_urn = 'urn:matchory:attribute:profile:street')  -- a line no format reads: not compared
    GROUP BY d.subject_id, d.attribute_id
)
SELECT s.urn, s.name, c.attribute_urn AS attribute, c.value AS chosen, c.source AS chosen_source,
       c.evidence_class AS chosen_class, c.disagreeing_sources, c.spelled_differently, c.less_specific,
       x.other_values, x.other_sources
FROM d c
JOIN v_subject s ON s.subject_id = c.subject_id
LEFT JOIN other x ON x.subject_id = c.subject_id AND x.attribute_id = c.attribute_id
ORDER BY s.name, c.attribute_urn