json_extract should use double quotes on Snowflake

[LewisDavies]

I'm using the Stripe package and have set project variables for fields I want to extract from metadata columns. It works on most values but isn't extracting anything when the key contains a full stop, e.g. plan.id.

I have fixed this locally by adding double quotes to the Snowflake macro:

{% macro snowflake__json_extract(string, string_path) %}

  json_extract_path_text(try_parse_json( {{string}} ), {{ "'\"" ~ string_path ~ "\"'" }} )

{% endmacro %}

Happy to make a PR but I'm not sure whether the problem applies to other databases.

[fivetran-joemarkiewicz]

Hi @LewisDavies thanks so much for opening this issue and sharing the root of the issue and a potential fix. That is interesting that the full stop breaks the macro, but your suggestion seems to make sense to me. I agree with you that I would want to validate the fix on the other platforms as well if we were to roll this fix out in a future release.

Would you mind sharing the original error message (and possibly the compiled snippet where this breaks), and then the equivalent when using this suggested version of the macro and seeing the what the compiled output is and how it succeeds? In the meantime, I will explore recreating the issue with the other platforms and attempt a similar solution.

[LewisDavies]

There's isn't actually an error message, it just returns null. In Snowflake you can access JSON values with dot notation, so without quotes the function is looking for the id field in the plan object.

I suppose my suggested changes could be breaking if some people are using dot notation in their scripts. Fortunately there are other ways of accessing values though.