Concatenate nested json array with column groups in Snowflake

Question

I'm using Snowflake to parse json and have a query which returns a concatenated string for Resource and Action keys in the Statement array. There are multiple Statement lists associated with GROUP and ROLE and what I would like to do is have a single row for GROUP and ROLE and the concat the values for "merged_actions" into one field

Current result:

GROUP1, ROLE1, [Resource: foo=Action:bar, foo]
GROUP1, ROLE1, [Resource: bar=Action:foo, blah]
GROUP2, ROLE2, [Resource: blah=Action:bar, foo]

Result hoping to achieve:

GROUP1, ROLE1, [Resource: foo=Action:bar, foo],[Resource: bar=Action:foo, blah]  
GROUP2, ROLE2, [Resource: blah=Action:bar, foo]

Query so far:

select GROUP,
       ROLE,
       CONCAT('[Resource: ', value:Resource::string, '= Action: ', value:Action::string, ']') merged_actions
from GROUP_PERMISSIONS,
LATERAL FLATTEN(INPUT => merged_json:Statement)

Thanks for any help

Answer 1

Use listagg on this one. Assuming everything is a string, it'd look something like this:

Setting up your data:

with x as (
    select *
    from (values('GROUP1', 'ROLE1', '[Resource: foo=Action:bar, foo]'),
                ('GROUP1', 'ROLE1', '[Resource: bar=Action:foo, blah]'),
                ('GROUP2', 'ROLE2', '[Resource: blah=Action:bar, foo]')
                )x(grp, rle, rsrc)
)

and then running a select against that CTE to show the result you'd like:

select grp, rle, listagg(rsrc,',')
from x
group by 1,2;