Convert Hive query to Snowflake

Question

we want to change our the following query to work with Snowflake.

INSERT OVERWRITE INTO {events_scoring}.profile_to_json_with_classified_campaigns_results
SELECT to_json.ksname,
       to_json.cust_profile_id,
       concat("{{",concat_ws('$',sort_array(collect_list(concat($$"$$,cast(to_json.portfolio_type AS STRING),$$":"$$, cast(to_json.counter AS STRING),$$"$$)))),'}}') AS json_of_campaign_counters_according_to_portfolio_types
FROM
  (SELECT final.ksname,
          final.cust_profile_id,
          final.portfolio_type_final AS portfolio_type,
          count(*) AS counter
   FROM {events_scoring}.campaign_to_portfolio_type FINAL
   GROUP BY final.ksname,
            final.cust_profile_id,
            final.portfolio_type_final
            ORDER BY portfolio_type) to_json
GROUP BY to_json.ksname,
         to_json.cust_profile_id

mention that "\"" already replaced with $$"$$.

But I didn't find a suitable replacement to sort_array function.

Can anyone help?

Answer 1

listagg aggregation function returns string concatenated with delimiter, similar to the concat_ws. Can be used with group by or with over().

select listagg(concat($$"$$,cast(to_json.portfolio_type AS STRING),$$":"$$, cast(to_json.counter AS STRING),$$"$$), "$") within group (ORDER BY cast(to_json.portfolio_type AS STRING), cast(to_json.counter AS STRING) )