CODEWITHSUNDEEP
snowflake-cloud-data-platform
Dharma
Dharma

Reputation: 3

alter XML/JSON variant object

In our Snowflake datalake, we have a requirement to delete corresponding PII data (e.g. email address) from all tables when a customer requests a forget me. In the COMPOSED and MODELLED Layer where the attributes are stored in individual columns this is easy, however I am struggling to work out how to do this in the RAW layer, where native XML / JSON datasets are retained. How would I amend these native XML/JSON variant dataobjects and remove the PII elements, such as email address.

Upvotes: 0

Views: 84

Answers (1)

Sergiu
Sergiu

Reputation: 4578

You can update a VARIANT column using OBJECT_INSERT like this:

UPDATE <tablename> SET properties = OBJECT_INSERT(properties, key, 'new_value', TRUE) WHERE properties:key::VARCHAR = 'existing_value';

The TRUE above is the if you want to overwrite data, see more here

Cast to VARCHAR is optional but good to have explicit cast rather than rely on implicit casting.

Upvotes: 2

Related Questions