Report as data

The validation report is JSONB. You can query, join, store and alert on violations like any other PostgreSQL value.

pgrdf.validate returns one JSONB document. Its results field is an array with one element per result, and jsonb_array_elements turns that array into rows. The examples below use the graphs from the worked example, before Bob's mailbox is added.

Each result carries focusNode, resultPath, value, sourceShape, sourceConstraintComponent (a full IRI), resultSeverity ("sh:Violation", "sh:Warning" or "sh:Info") and resultMessage. The report itself also carries conforms, mode, data_triples, shapes_triples, data_graph_id, shapes_graph_id and elapsed_ms.

List who violated what

WITH r AS (
  SELECT pgrdf.validate(pgrdf.graph_id('http://example.org/data'),
                        pgrdf.graph_id('http://example.org/shapes')) AS rep
)
SELECT v ->> 'focusNode'     AS who,
       v ->> 'resultPath'    AS path,
       v ->> 'resultMessage' AS why
  FROM r, jsonb_array_elements(r.rep -> 'results') v;
--           who           |              path              |            why
-- ------------------------+--------------------------------+---------------------------
--  http://example.org/bob | http://xmlns.com/foaf/0.1/mbox | MinCount(1) not satisfied

Count violations per constraint component

WITH r AS (
  SELECT pgrdf.validate(pgrdf.graph_id('http://example.org/data'),
                        pgrdf.graph_id('http://example.org/shapes')) AS rep
)
SELECT v ->> 'sourceConstraintComponent' AS component,
       count(*)                          AS n
  FROM r, jsonb_array_elements(r.rep -> 'results') v
 GROUP BY component
 ORDER BY n DESC;
--                        component                        | n
-- --------------------------------------------------------+---
--  http://www.w3.org/ns/shacl#MinCountConstraintComponent | 1

Add WHERE v ->> 'resultSeverity' = 'sh:Violation' to leave warnings and info results out of a count. Note that any result, whatever its severity, makes conforms false.

Keep a history of runs

The report names its own graphs, so one statement can store a run:

CREATE TABLE validation_runs (
    run_at        timestamptz NOT NULL DEFAULT now(),
    data_graph    bigint      NOT NULL,
    shapes_graph  bigint      NOT NULL,
    conforms      boolean     NOT NULL,
    report        jsonb       NOT NULL
);

INSERT INTO validation_runs (data_graph, shapes_graph, conforms, report)
SELECT (rep ->> 'data_graph_id')::bigint,
       (rep ->> 'shapes_graph_id')::bigint,
       (rep ->> 'conforms')::boolean,
       rep
  FROM (SELECT pgrdf.validate(pgrdf.graph_id('http://example.org/data'),
                              pgrdf.graph_id('http://example.org/shapes')) AS rep) r;

Refuse a load that doesn't conform

Load and validate in one transaction, and raise an exception when the report doesn't conform. PostgreSQL then rolls the load back:

BEGIN;

SELECT pgrdf.parse_turtle('
@prefix foaf: <http://xmlns.com/foaf/0.1/> .
@prefix ex:   <http://example.org/> .
ex:carol a foaf:Person ; foaf:name "Carol" .
', pgrdf.graph_id('http://example.org/data'));

DO $$
DECLARE rep jsonb := pgrdf.validate(pgrdf.graph_id('http://example.org/data'),
                                    pgrdf.graph_id('http://example.org/shapes'));
BEGIN
  IF NOT (rep ->> 'conforms')::boolean THEN
    RAISE EXCEPTION 'validation failed: % result(s)', jsonb_array_length(rep -> 'results')
      USING DETAIL = rep -> 'results' -> 0 ->> 'resultMessage';
  END IF;
END $$;
-- ERROR:  validation failed: 1 result(s)
-- DETAIL:  MinCount(1) not satisfied

COMMIT;
-- ROLLBACK: Carol was not added.

Here the data graph started out conforming (Bob's mailbox already added), and Carol has no mailbox. Loads and SPARQL UPDATE in pgRDF are transactional, so nothing from the failed batch stays in the graph.

In production code, also treat a NULL report as a failure (IF rep IS NULL OR NOT …): validate returns NULL when either graph id is NULL, for example from a mistyped IRI in graph_id().

Next: SHACL-SPARQL →