JSON field validation rules

Rule selection

JSON field validation extracts a value from a specified path and counts records that fail the check. These rules check table records. They do not perform strict JSON Schema validation, which validates a document against a complete structure definition.

The following table describes the metrics and template IDs for the two rules.

Use the key existence check to check for empty extraction results. Use the value format check to validate the format of an extracted value. Configure both rules if your business requires a value to be present and correctly formatted.

The examples are rules fragments for a data quality Spec and use an existing dataset configuration. For a system template, specify templateId and the template parameters. For an assertion, specify the extraction and counting expression in assertion. Choose one form for each check. For the complete Spec structure, see Data quality Spec.

The dataset-level datasets.filter and rule-level filter determine which records participate in a check. Keep the dataset filter, rule filter, and pass threshold identical when comparing the two configuration forms.

Sample data

Assume that the payload field in the orders table stores JSON text. The path $.user.id extracts the id value from the user object.

JSON key presence check

Both configurations count records for which extraction from $.user.id in payload returns an empty result. The pass threshold is 0. Neither configuration specifies a rule-level filter, and both use the same dataset scope.

System rule template

rules:
  - templateId: SYSTEM:field:json_key_missing_count:fixed
    name: user.id is not empty
    fields:
      - payload
    templateParameters:
      jsonPath: $.user.id
    pass:
      - when = 0

Assertion expression

rules:
  - assertion: "missing_count(json_extract(payload, '$.user.id'))"
    name: user.id is not empty
    pass:
      - when = 0

json_extract(payload, '$.user.id') extracts the path value, and missing_count(...) counts empty extraction results. The third sample record contributes to this metric.

The metric depends on the extraction result. If your business must distinguish a missing key from a key with a null value, verify JSON extraction and empty-value handling with test data on the target compute engine.

JSON value format check

Both configurations require $.user.id to start with a letter and contain only letters or digits after that letter. The invalid record count must be 0. Both use filter: payload IS NOT NULL to exclude records where the payload field is SQL NULL.

System rule template

rules:
  - templateId: SYSTEM:field:json_value_invalid_count:fixed
    name: user.id format check
    fields:
      - payload
    templateParameters:
      jsonPath: $.user.id
    filter: payload IS NOT NULL
    valid:
      regex: '^[A-Za-z][A-Za-z0-9]*$'
    pass:
      - when = 0

Assertion expression

rules:
  - assertion: "invalid_count(json_extract(payload, '$.user.id'))"
    name: user.id format check
    filter: payload IS NOT NULL
    valid:
      regex: '^[A-Za-z][A-Za-z0-9]*$'
    pass:
      - when = 0

invalid_count(...) counts extracted values that fail to match valid.regex. Specify the regular expression in the valid property of the rule. In the sample data, A123 matches the format, and bad-id! does not.

Excluding a NULL payload does not exclude a missing path in non-NULL JSON text. Verify whether missing paths contribute to the invalid format count based on JSON extraction and empty-value handling on the target compute engine. Add a key existence check if you also need to check for empty extraction results.

Parameters

The following table describes the configuration items used in these examples. Dots in parameter names indicate nesting. Use the YAML examples for the corresponding structure.

Related documentation

For more system templates and configuration details, see Rule templates.