Validating data in a stored JSON file in the DB using YII framework

I am trying to write a rule in my Yii project to add an exempt rule when we have a data point collected on a stored JSON. The following is my sample Yii script for the validation rule

public function rules()
{
    return [
        [['animal_id', 'event_type', 'event_date'], 'required'],
        [['animal_id', 'event_type', 'country_id', 'region_id', 'district_id', 'ward_id', 'village_id', 'field_agent_id'], 'integer'],
        [['event_date', 'data_collection_date'], 'date', 'format' => 'php:Y-m-d'],
        [['latitude', 'longitude'], 'number'],
        [['map_address', 'uuid'], 'string', 'max' => 255],
        ['event_date', 'validateNoFutureDate'],
        ['event_date', 'unique', 'targetAttribute' => ['animal_id', 'event_type', 'event_date'], 'message' => '{attribute} should be unique per animal', 'except' => [self::SCENARIO_MISTRO_DB_UPLOAD]],
        [['org_id', 'client_id'], 'safe'],
        ['migration_id', 'unique', 'except' => self::SCENARIO_MISTRO_DB_UPLOAD],
        [[self::SEARCH_FIELD], 'safe', 'on' => self::SCENARIO_SEARCH],
    ];
} 

In my database, I have a column where I store extra variables as a stored JSON as shown in the diagram below, and all the extra attributes are stored under the column additional attributes which is a stored JSON

CREATE TABLE `core_animal_event` (
  `id` int NOT NULL AUTO_INCREMENT,
  `animal_id` int NOT NULL,
  `event_type` int NOT NULL,
  `country_id` int NOT NULL,
  `region_id` int DEFAULT NULL,
  `district_id` int DEFAULT NULL,
  `ward_id` int DEFAULT NULL,
  `village_id` int DEFAULT NULL,
  `org_id` int DEFAULT NULL,
  `client_id` int DEFAULT NULL,
  `event_date` date DEFAULT NULL,
  `data_collection_date` date DEFAULT NULL,
  `latitude` decimal(13,8) DEFAULT NULL,
  `longitude` decimal(13,8) DEFAULT NULL,
  `map_address` varchar(255) DEFAULT NULL,
  `latlng` point DEFAULT NULL,
  `uuid` varchar(255) NOT NULL,
  `field_agent_id` int DEFAULT NULL,
  `lactation_id` int DEFAULT NULL COMMENT 'lactation Id/Calving Id for milking record',
  `lactation_number` int DEFAULT NULL COMMENT 'lactation number for calving records',
  `testday_no` int DEFAULT NULL COMMENT 'Test day number for milk record',
  `additional_attributes` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `migration_id` varchar(255) DEFAULT NULL COMMENT 'This is the migrationSouce plus primary key from migration source table of the record e.g KLBA_001',
  `odk_form_uuid` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `animal_id` (`animal_id`),
  KEY `event_type` (`event_type`),
  KEY `lactation_id` (`lactation_id`),
  KEY `country_id` (`country_id`,`region_id`,`district_id`,`ward_id`,`village_id`),
  KEY `org_id` (`org_id`,`client_id`),
  KEY `event_date` (`event_date`),
  KEY `data_collection_date` (`data_collection_date`),
  CONSTRAINT `core_animal_event_ibfk_1` FOREIGN KEY (`animal_id`) REFERENCES `core_animal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2623841 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT;

I would like to add a validation in my code that exempt a record under the additional attribute, I am new to the Yii framework and I have gone through the validation solution on the Yii documentation with no clear way forward

Answer

Yii2 don’t have JSON specific validations, it can validate is as a string, or you could validate each index from array.

But if you need more precise validation, you have to create your own validation rule for that. First add your custom rule name to the rules method:

public function rules()
{
    return [
        // ...
        ['additional_attributes', 'validateAdditionalAttributes'],
        // ...
    ];
}

Create your custom rule method:

public function validateAdditionalAttributes($attribute, $params, $validator)
{
    // The JSON might already be a string
    if ( is_string( $this->$attribute ) ) {
        $decoded = yiihelpersJson::decode( $this->$attribute );
    }
    // Now perform your custom validation
    // In case of error add the error to the field
    // $this->addError( $attribute, 'My custom error' );
    // This will validate the model to false
}

Read more on Creating Validators