I am trying to decrypt specific column (ssn) in Bigquery, that is encrypted with key as explained below.

The below query run successfully (trying to decrypt specific ssn value with my key):

DECLARE KEYSET STRING;
SET KEYSET = '{"primaryKeyId":1,"key":[{"keyData":{"typeUrl":"a","value":"a","keyMaterialType":"SYMMETRIC"},"status":"ENABLED","keyId":1,"outputPrefixType":"TINK"}]}';

SELECT name,ssn ,DETERMINISTIC_DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(KEYSET) , FROM_BASE64("value_of_the_encrypted_col"), "") ;

But when I try to pass the encrypted column name(ssn) to the function FROM_BASE64, as below:

DECLARE KEYSET STRING;
SET KEYSET = '{"primaryKeyId":1,"key":[{"keyData":{"typeUrl":"a","value":"a","keyMaterialType":"SYMMETRIC"},"status":"ENABLED","keyId":1,"outputPrefixType":"TINK"}]}';

SELECT name,ssn ,DETERMINISTIC_DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(KEYSET) , FROM_BASE64(ssn), "") AS d_ssn FROM `project.dataset.table` ;

I am getting the below error:

DETERMINISTIC_DECRYPT_STRING failed: decryption failed; error in DETERMINISTIC_DECRYPT_STRING expression

Not sure why passing the column name instead of hardcoded value isn’t working.

Appreciate your support.