Snowflake regextract no longer working


We have just upgraded to v0.50.6 using the docker image, when creating a custom column using the below expression:

substring(regexextract([Tags], "\"Name\":\"((\\\"|[^\"])*)"), 9, 60)

we're now getting "Invalid expression". I can't remember the exact version we were on but I believe it was v.0.48. I've also tried the sample from the documentation regexextract([Tags], "^[^?#]+\?utm_campaign=(.*)") and that gives the same error

1 Like

@nmeneil I have tried to reproduce this and after a few tests I think there may be some relationship to the data type of the field you're performing the operation on. Can you go to Table Metadata and see what the "Type" setting is for this Tags field?

@leeloo Thanks for checking, it's a "Field containing JSON", this is happening on a large number of tables now and we've not changed any "Type" settings since the upgrade.

@nmeneil I can see, if I attempt this regex formula on v49 and v50 on an ID type column - this error pops up in 50 but not in 49 even though the field is set up the same way. So I suspect some validation has been added. I wasn't able to produce this message in v50 on a field with semantic type "Field containing JSON". What's the data type in Snowflake?

Hi @leeloo it's set to Variant in Snowflake and again has not changed since the upgrade.

Hi @leeloo , did you get anywhere with this? Just upgraded to 0.50.10 and it's still broken