I have json with nested arrays data vary for different jsons. I am putting my json parsing code and sample json file here. Query only got slow when there are many objects for rows tags as you can see below for this json query executes within a minute but when I have 100 objects then it will take up to 50 minutes. you can see the json below
{
"page_desktop_image": {
"responseAggregationType": "byPage",
"rows": [
{
"clicks": 5,
"ctr": 0.003048780487804878,
"impressions": 1640,
"keys": [
"abc"
],
"position": 10.207317073170731
},
{
"clicks": 2,
"ctr": 0.010638297872340425,
"impressions": 188,
"keys": [
"abc"
],
"position": 28.324468085106382
},
{
"clicks": 0,
"ctr": 0,
"impressions": 4,
"keys": [
"abc"
],
"position": 237.5
}
]
},
"page_desktop_video": {
"responseAggregationType": "byPage",
"rows": [
{
"clicks": 1,
"ctr": 0.038461538461538464,
"impressions": 26,
"keys": [
"abc"
],
"position": 4.5
},
{
"clicks": 0,
"ctr": 0,
"impressions": 19,
"keys": [
"abc"
],
"position": 6.947368421052632
}
]
},
"page_desktop_web": {
"responseAggregationType": "byPage",
"rows": [
{
"clicks": 8578,
"ctr": 0.28393631458740193,
"impressions": 30211,
"keys": [
"abc"
],
"position": 1.7217900764622156
},
{
"clicks": 0,
"ctr": 0,
"impressions": 22,
"keys": [
"abc"
],
"position": 12.318181818181818
}
]
},
"page_mobile_image": {
"responseAggregationType": "byPage",
"rows": [
{
"clicks": 3,
"ctr": 0.028037383177570093,
"impressions": 107,
"keys": [
"abc"
],
"position": 17.018691588785046
},
{
"clicks": 0,
"ctr": 0,
"impressions": 37,
"keys": [
"abcx"
],
"position": 38.4054054054054
}
]
},
"page_mobile_video": {
"responseAggregationType": "byPage",
"rows": [
{
"clicks": 2,
"ctr": 0.05128205128205128,
"impressions": 39,
"keys": [
"abc"
],
"position": 6.487179487179487
},
{
"clicks": 0,
"ctr": 0,
"impressions": 64,
"keys": [
"abc"
],
"position": 4.3125
}
]
},
"page_mobile_web": {
"responseAggregationType": "byPage",
"rows": [
{
"clicks": 3604,
"ctr": 0.1579385599719532,
"impressions": 22819,
"keys": [
"abc"
],
"position": 2.3936193522941407
},
{
"clicks": 0,
"ctr": 0,
"impressions": 12,
"keys": [
"abc"
],
"position": 6.583333333333333
}
]
},
"page_tablet_image": {
"responseAggregationType": "byPage",
"rows": [
{
"clicks": 1,
"ctr": 0.005649717514124294,
"impressions": 177,
"keys": [
"abc"
],
"position": 5.112994350282486
},
{
"clicks": 0,
"ctr": 0,
"impressions": 6,
"keys": [
"abc"
],
"position": 33.5
}
]
},
"page_tablet_video": {
"responseAggregationType": "byPage",
"rows": [
{
"clicks": 1,
"ctr": 0.1,
"impressions": 10,
"keys": [
"abc"
],
"position": 18.7
},
{
"clicks": 0,
"ctr": 0,
"impressions": 1,
"keys": [
"abc"
],
"position": 10
}
]
},
"page_tablet_web": {
"responseAggregationType": "byPage",
"rows": [
{
"clicks": 639,
"ctr": 0.2729602733874413,
"impressions": 2341,
"keys": [
"abc"
],
"position": 1.5262708244340026
},
{
"clicks": 0,
"ctr": 0,
"impressions": 27,
"keys": [
"abc"
],
"position": 60.55555555555556
}
]
}
}
and this is my json parsing code:
SELECT
JSON_FILE:page_desktop_image:responseAggregationType::String as responseAggregationType_desk_image
, r_desk_image.value:clicks as clicks_desk_image
, r_desk_image.value:ctr as ctr_desk_image
, r_desk_image.value:impressions as impressions_desk_image
, array_to_string(r_desk_image.value:keys , '') as keys_desk_image
, r_desk_image.value:position as position_desk_image
,JSON_FILE:page_desktop_video:responseAggregationType::String as responseAggregationType_desk_video
, r_desk_video.value:clicks as clicks_desk_video
, r_desk_video.value:ctr as ctr_desk_video
, r_desk_video.value:impressions as impressions_desk_video
, array_to_string(r_desk_video.value:keys,'') as keys_desk_video
, r_desk_video.value:position as position_desk_video
,JSON_FILE:page_desktop_web:responseAggregationType::String as responseAggregationType_desk_web
, r_desk_web.value:clicks as clicks_desk_web
, r_desk_web.value:ctr as ctr_desk_web
, r_desk_web.value:impressions as impressions_desk_web
, array_to_string(r_desk_web.value:keys,'') as keys_desk_web
, r_desk_web.value:position as position_desk_web
,JSON_FILE:page_mobile_image:responseAggregationType::String as responseAggregationType_mob_image
, r_mob_image.value:clicks as clicks_mob_image
, r_mob_image.value:ctr as ctr_mob_image
, r_mob_image.value:impressions as impressions__mob_image
, array_to_string(r_mob_image.value:keys,'') as keys_mob_image
, r_mob_image.value:position as position_mob_image
,JSON_FILE:page_mobile_video:responseAggregationType::String as responseAggregationType_mob_video
, r_mob_video.value:clicks as clicks_mob_video
, r_mob_video.value:ctr as ctr_mob_video
, r_mob_video.value:impressions as impressions_mob_video
, array_to_string(r_mob_video.value:keys,'') as keys_mob_video
, r_mob_video.value:position as position_mob_video
,JSON_FILE:page_mobile_web:responseAggregationType::String as responseAggregationType_mob_web
, r_mob_web.value:clicks as clicks_mob_web
, r_mob_web.value:ctr as ctr_mob_web
, r_mob_web.value:impressions as impressions_mob_web
, array_to_string(r_mob_web.value:keys,'') as keys_mob_web
, r_mob_web.value:position as position_mob_web
,JSON_FILE:page_tablet_image:responseAggregationType::String as responseAggregationType_tab_image
, r_tab_image.value:clicks as clicks_tab_image
, r_tab_image.value:ctr as ctr_tab_image
, r_tab_image.value:impressions as impressions_tab_image
, array_to_string(r_tab_image.value:keys,'') as keys_tab_image
, r_tab_image.value:position as position_tab_image
,JSON_FILE:page_tablet_video:responseAggregationType::String as responseAggregationType_tab_video
, r_tab_video.value:clicks as clicks_tab_video
, r_tab_video.value:ctr as ctr_tab_video
, r_tab_video.value:impressions as impressions_tab_video
, array_to_string(r_tab_video.value:keys,'') as keys_tab_video
, r_tab_video.value:position as position_tab_video
,JSON_FILE:page_tablet_web:responseAggregationType::String as responseAggregationType_tab_web
, r_tab_web.value:clicks as clicks_tab_web
, r_tab_web.value:ctr as ctr_tab_web
, r_tab_web.value:impressions as impressions_tab_web
, array_to_string(r_tab_web.value:keys ,'') as keys_tab_web
, r_tab_web.value:position as position_tab_web
from GSC_JSONS
,lateral flatten(input => JSON_FILE:page_desktop_image:rows) as r_desk_image
,lateral flatten(input => JSON_FILE:page_desktop_video:rows) as r_desk_video
,lateral flatten(input => JSON_FILE:page_desktop_web:rows) as r_desk_web
,lateral flatten(input => JSON_FILE:page_mobile_image:rows) as r_mob_image
,lateral flatten(input => JSON_FILE:page_mobile_video:rows) as r_mob_video
,lateral flatten(input => JSON_FILE:page_mobile_web:rows) as r_mob_web
,lateral flatten(input => JSON_FILE:page_tablet_image:rows) as r_tab_image
,lateral flatten(input => JSON_FILE:page_tablet_video:rows) as r_tab_video
,lateral flatten(input => JSON_FILE:page_tablet_web:rows) as r_tab_web
If anyone knows the workaround then please let me know. I know why it is slow because it makes cross joins for each object but I want to execute it faster.
I recommend you make a materialized view on top of the flattened output. https://docs.snowflake.com/en/user-guide/views-materialized.html