Snowflake DBT Json in string format

Question

I have the following column called int_calcs in snowflake which is a json saved as a string:

int_calcs =

'{
  "frequency": {
    "SA": {
      "ER": 1.00,
      "EXR": 0.18667007686333767,
      "CW": 0.18667007686333767
  },
}'

I'm trying to get out ER (1.00) out in dbt, but i'm running into problems. I think i need to use parse_json (as it's a string).

I have tried:

SELECT
    lower(cast(json_extract_path_text(parse_json(int_calcs), '$.frequency.SA.ER') as float)) as ER,

this doesn't crash but fails my tests dbt_utils.at_least_one (so the syntax is correct but it's not taking out the value).

Any tips on how to get this to work?

I've tried it in this format too:

lower(cast(parse_json(int_calcs:frequency:SA:ER::number))) as ER,

without syntax success.

Thanks in advance!

Answer 1

Using:

CREATE OR REPLACE TABLE tab(int_cals string)
AS
SELECT '{
  "frequency": {
    "SA": {
      "ER": 1.00,
      "EXR": 0.18667007686333767,
      "CW": 0.18667007686333767
  },
}
}';

SELECT PARSE_JSON(int_cals):frequency:SA:ER::NUMBER AS er
FROM tab;
-- ER
-- 1