Snowflake JSON

Example SQL reading native JSON data:

 
select
  value:attributes[0].value::string       data_item_number
  ,value:attributes[1].value::string      name
  ,value:attributes[2].value::string      definition
  ,value:attributes[3].value::string      business_criticality
  ,value:attributes[4].value::string      privacy_classification
  ,value:attributes[5].value::string      security_classification
  ,value:attributes[6].value::string      stewards
  ,value:attributes[7].value::string      stewardship_group
  ,value:attributes[8].value::string      notes
  ,value:attributes[9].value::string      examples
  ,value:attributes[10].value::string     dq_sql_multi_test
  ,value:attributes[11].value::string     data_quality_error_message
  ,value:attributes[12].value::string     cpg235_data_quality_dimension_name
  ,value:attributes[13].value::string     data_quality_rule_type
  ,value:attributes[14].value::string     data_quality_rule_severity
  ,value:attributes[15].value::string     data_quality_score
  ,value:attributes[16].value::string     target_dq_score
  ,value:attributes[17].value::date       effective_from
  ,value:attributes[18].value::date       effective_to
  ,value:attributes[19].value::date       last_modified_date
  ,value:attributes[20].value::date       last_viewed_date
  ,value:attributes[21].value::string     status
  ,value:attributes[22].value::string     state
  ,value:attributes[23].value::string     object_id
  ,value:attributes[24].value::string     object_stable_id
  ,value:attributes[25].value::string     primary_data_item_checked
  ,count(*) over (order by 1)             num_rules
from
  JSON_STAGING
  ,lateral flatten(input => raw_json:result) atbs
where
  UPPER(value:attributes[13].value::string) = 'BUSINESS RULE'