CODEWITHSUNDEEP
snowflake-cloud-data-platform
Jonito
Jonito

Reputation: 459

Snowflake, how to delete flatten records?

I can't delete my records using this syntax :

DELETE FROM "DB_NAME"."SCHEMA_NAME"."TABLE_NAME",LATERAL FLATTEN(INPUT => src:Records) rec WHERE Records.value:requestParameters:key is null;

Could you help me please with the good syntax ? Table source is a variant table as follow:

{
"Records": [
  {
  "additionalEventData": {
    "AuthenticationMethod": "xxxxxxxxxxxxxx",
    "CipherSuite": "xxxxxxxxxxxxxxxxxx",
    "SignatureVersion": "xxxxxxxxxxxx",
    "bytesTransferredIn": xxxxxxxxxxxx,
    ...

Upvotes: 1

Views: 374

Answers (1)

waldente
waldente

Reputation: 1424

Try this pattern:

delete from table_name a
using table_name b, lateral flatten(input => b.src:Records, recursive=>true) c 
where a.src=b.src and c.key='key' and c.value::text is null

The trick is the USING clause with the self join. (The recursive option is not necessary, just update the where clause accordingly.)

Upvotes: 2

Related Questions