Postgres to Snowflake json syntax

Question

I am looking for help with migrating from Postgres to SnowFlake with Json syntax while building ETL model

select 

n.Object->c.name->>'some key in json file' as code

from names n
left join country c

How would you write this in Snowflake dbt ??

-- update: Here i will try to explain better as this involves 3 tables

1- Company 2- Country 3- company rating

to make things easier we can call the columns A,B,C respectively

json column in company table:

{
  "AU": {},
  "CA": {},
  "GB": {
    "company rating": "ijbfgp"
  },
  "US": {},
  "ES": {
    "company rating": "piayerb",
  },
}

Country table:

country name/code column with all countries as TEXT

Company rating table:

technical name column that explains what that rating is as TEXT

What i am trying to do is query sth like in postgres

company.A -> country.B ->> company.country_rating

@FelipeHoffa , i updated the input if you guys can take a look, would appreciate it — Mostafa El-Zayat

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2021-02-02T10:33:13

This is the best that I could re-build from your description:

with company as (
    select parse_json('    
        {
      "AU": {},
      "CA": {},
      "GB": {
        "company rating": "ijbfgp"
      },
      "US": {},
      "ES": {
        "company rating": "piayerb",
      },
    }') x
), country as (
    select 'GB' country_code, 'Great Britain' country_name
    union all select 'ES', 'Spain'
), ratings as (
    select 'ijbfgp' rating_code, 'incredible just before forgetting goal posts' rating
    union all select 'piayerb', 'praying into abiss you exit running beach'
)

select country_name, rating
from company, country, ratings
where ratings.rating_code=get(x, country_code):"company rating"

The key to navigate the JSON in this case is get(x, country_code):"company rating".

Postgres to Snowflake json syntax

1 Answers