[
{"active":false,"urls":{"6x6":"https://url.url.com/secure/?size=xsmall&ownerId=B12345&id=123","4x4":"https://url.url.com/secure/?size=small&ownerId=B12345&id=123","22x22":"https://url.url.com/secure/?size=medium&ownerId=B12345&id=123","44x44":"https://url.url.com/secure/?ownerId=B12345&id=123"},"displayName":"Name1,Lname1","emailAddress":"","key":"B12345","name":"B12345","self":"https://url.url.com/rest/api/2/user?username=B12345","timeZone":"India/Mumbai"},
{"active":true,"urls":{"6x6":"https://url.url.com/secure/?size=xsmall&ownerId=A12345&id=456","4x4":"https://url.url.com/secure/?size=small&ownerId=A12345&id=456","22x22":"https://url.url.com/secure/?size=medium&ownerId=A12345&id=456","44x44":"https://url.url.com/secure/?ownerId=A12345&id=456"},"displayName":"Name1,Lname2.","emailAddress":"name.lname@abcdef.com","key":"A12345","name":"A12345","self":"https://url.url.com/rest/api/2/user?username=A12345","timeZone":"India/Mumbai"}
 
{"active":true,"urls":{"6x6":"https://url.url.com/secure/?size=xsmall&ownerId=C12345&id=456","4x4":"https://url.url.com/secure/?size=small&ownerId=C12345&id=456","22x22":"https://url.url.com/secure/?size=medium&ownerId=C12345&id=456","44x44":"https://url.url.com/secure/?ownerId=C12345&id=456"},"displayName":"Name1,Lname3.","emailAddress":"name.lname@abcdef.com","key":"C12345","name":"C12345","self":"https://url.url.com/rest/api/2/user?username=C12345","timeZone":"India/Mumbai"}
 ]

[ 
[false,
[https://url.url.com/secure/?size=xsmall&ownerId=B12345&id=123,
https://url.url.com/secure/?size=small&ownerId=B12345&id=123,
https://url.url.com/secure/?size=medium&ownerId=B12345&id=123,
https://url.url.com/secure/?ownerId=B12345&id=123], Name1, Lname1, ,
B12345, B12345, https://url.url.com/rest/api/2/user?username=B12345,
India/Mumbai],  
[true,
[https://url.url.com/secure/?size=xsmall&ownerId=A12345&id=456,
https://url.url.com/secure/?size=small&ownerId=A12345&id=456,
https://url.url.com/secure/?size=medium&ownerId=A12345&id=456,
https://url.url.com/secure/?ownerId=A12345&id=456], Name1, Lname2.,
name.lname@abcdef.com, A12345,
A12345,https://url.url.com/rest/api/2/user?username=B12345,
India/Mumbai] ,
[true,
[https://url.url.com/secure/?size=xsmall&ownerId=C12345&id=456,
https://url.url.com/secure/?size=small&ownerId=C12345&id=456,
https://url.url.com/secure/?size=medium&ownerId=C12345&id=456,
https://url.url.com/secure/?ownerId=C12345&id=456], Name1, Lname3.,
name.lname@abcdef.com, C12345,
C12345,https://url.url.com/rest/api/2/user?username=B12345,
India/Mumbai] 
]

SELECT  
CONCAT('[[',
REPLACE(GET(flattened.value,  'active'), '"', '') , ', ',
'[',
listagg(CASE WHEN flattened_nested.value LIKE '%https%' THEN 'https:' || REPLACE(SPLIT_PART(flattened_nested.value, ':', 2), '"', '') ELSE NULL END,',')within group (order by null)
,']', ', ',  --<== this causes issue while trying to parse urls values for each elements and to store them.
REPLACE(GET(flattened.value,  'displayName'), '"', '') , ', ',
REPLACE(GET(flattened.value,  'emailAddress'), '"', '') , ', ',
REPLACE(GET(flattened.value,  'key'), '"', '') , ', ',
REPLACE(GET(flattened.value,  'name'), '"', '')  , ', ',
REPLACE(GET(flattened.value,  'self'), '"', '') , ', ',
REPLACE(GET(flattened.value,  'timeZone'), '"', ''),   
']]')  as output_column
FROM snowflake_table  SRC
,LATERAL FLATTEN(input=>SRC.json_values:"fields":"field_12345") AS flattened --<== the field that contains the above input.
,LATERAL FLATTEN(input=>flattened.value:urls) AS flattened_nested
group by  flattened.value
;

SELECT 
      t.value:active::BOOLEAN AS active
    , ARRAY_AGG(t.value:urls) WITHIN GROUP (ORDER BY seq) AS urls
    , SPLIT_PART(t.value:displayName::STRING, ',', 1) AS firstName
    , SPLIT_PART(t.value:displayName::STRING, ',', 2) AS lastName
    , t.value:emailAddress::STRING AS emailAddress
    , t.value:key::STRING AS key
    , t.value:name::STRING AS name
    , t.value:self::STRING AS self
    , t.value:timeZone::STRING AS timeZone
FROM snowflake_table
    , LATERAL FLATTEN(input => json_values) t
GROUP BY active, firstName, lastName, emailAddress, key, name, self, timeZone;

SELECT 
      json_values:active::BOOLEAN AS active
    , ARRAY_AGG(json_values:urls) AS urls
    , json_values:displayName::STRING AS displayName
    , json_values:emailAddress::STRING AS emailAddress
    , json_values:key::STRING AS key
    , json_values:name::STRING AS name
    , json_values:self::STRING AS self
    , json_values:timeZone::STRING AS timeZone
FROM snowflake_table
    , LATERAL JSON_PARSE(json_values) AS json_values
GROUP BY active, displayName, emailAddress, key, name, self, timeZone;