CODEWITHSUNDEEP
snowflake-cloud-data-platformdbtsnowflake-schemazend-db-table
Avenger
Avenger

Reputation: 877

Issue with JSON parsing in Snowflake when using dbt

I have a array of json column called params which has values such as

 [
    {"key": "abc", "value": 1},
    {"key": "def", "value": 2},
    {"key": "ghi", "value": 3}
]

I want to convert it to {"abc":1, "def":2, "ghi":3}

I have this dbt macro to do the job

{% macro array_to_dict(ARR, KEY_FIELD_NAME, VALUE_FIELD_NAME) %}
    {% set sql_query %}
        SELECT OBJECT_AGG(
            PARSE_JSON(f.{{ VALUE_FIELD_NAME }}):{{ KEY_FIELD_NAME }},
            PARSE_JSON(f.{{ VALUE_FIELD_NAME }}):{{ VALUE_FIELD_NAME }}
        ) AS json_object
        FROM LATERAL FLATTEN(INPUT => PARSE_JSON('{{ ARR }}')) AS f
        GROUP BY f.this
    {% endset %}
    {{ log("SQL to run: " ~ sql_query, info=True) }}
    {{ return(run_query(sql_query)) }}
{% endmacro %}

I am calling this macro from model

select
    id,
    {{ array_to_dict('params', 'key', 'value') }} as _params
from
    test

I am getting the error

100069 (22P02): Error parsing JSON: unknown keyword "params", pos 7

how can I make it work?

Upvotes: 0

Views: 204

Answers (1)

Aleix CC
Aleix CC

Reputation: 2099

to have this fully functional on multiple cases, I'd do the following:

  • create a macro that builds a CTE
  • that CTE will have an ID to later join, and will handle the flatten
  • then, you can call that macro at the top of your models, and only need to join later by the ID
-- macros/array_to_dict_cte.sql

{% macro array_to_dict_cte(
    id, 
    model_name, 
    field_name, 
    key_field_name, 
    value_field_name
  )
%}

with array_to_dict_cte as (
  select
    {{ id }},
    object_agg(
      parse_json(j.value):{{ key_field_name }}::varchar, 
      parse_json(j.value):{{ value_field_name }}::number
    ) as transformed_json
  from {{ ref('model_name') }},
  lateral flatten(input => {{ field_name }}) as j
),

{% endmacro %}

From here, you would just need your model to do the following:

-- your_model.sql

{{ array_to_dict_cte('id', '<your_model_name>', 'params', 'key', 'value') }}

joined as (
  select
    <your_model_name>.id,
    array_to_dict_cte.transformed_json
  from {{ ref('<your_model_name>') }}
  left join array_to_dict_cte on <your_model_name>.id = array_to_dict_cte.id
)

select * from joined

Give it a try and let me know! Haven't tested this specific use case, but we use a similar approach in our dbt project for an almost identical challenge.

Upvotes: 0

Related Questions