Querying nested json in snowflake

I have the following JSON and my snowflake query is below. I am getting empty rows. What is wrong with my query? IAECARBONBASELINESBREAKDOWN is the column in ESG_ACCOUNTS_DATA. Json may or may not have level2breakdown

SELECT ROUND(ZEROIFNULL(LEVEL1ARRAY.VALUE:weightedAverage),1) as weightedAverage,  
  REPLACE(LEVEL2ARRAY.VALUE:name,'"','') as name,
  LEVEL2ARRAY.VALUE:percent as percent,
  LEVEL2ARRAY.VALUE:total as total,
  COALESCE(LEVEL2ARRAY.VALUE:breakdownBy,'') as level2Breakdown,
  LEVEL3ARRAY.VALUE:name AS level2BreakdownName,
  LEVEL3ARRAY.VALUE:total AS level2BreakdownTotal,
  LEVEL3ARRAY.VALUE:percent AS level2BreakdownPercent
FROM
  ESG_ACCOUNTS_DATA,
  LATERAL FLATTEN (INPUT => PARSE_JSON(IAECARBONBASELINESBREAKDOWN):breakdownScope1and2) LEVEL1ARRAY,
  LATERAL FLATTEN (INPUT => LEVEL1ARRAY.VALUE:breakdown) LEVEL2ARRAY,
  LATERAL FLATTEN (INPUT => LEVEL2ARRAY.VALUE:breakdown) LEVEL3ARRAY,
WHERE
  variationid = '99e00658-a85e-419d-89f1-751f1dce1aba'
  AND IAECARBONBASELINESBREAKDOWN IS NOT NULL
  AND UPPER(LEVEL1ARRAY.VALUE:viewBy) = UPPER('account') AND
      UPPER(LEVEL1ARRAY.VALUE:breakdownBy) = UPPER('yearofEmissions')
  AND IAECARBONBASELINESBREAKDOWN LIKE '%level2Breakdown%';

Json for the same is below "level2Breakdown" might be null or can have a value. any pointers is really appreciated

{
    "breakdownScope1and2": [
        {
            "breakdownBy": "DQS",
            "viewBy": "account",
            "weightedAverage": 5,
            "breakdown": [
                {
                    "name": "DQS5",
                    "total": 30,
                    "percent": 100,
                    "level2Breakdown": null
                }
            ]
        },
        {
            "breakdownBy": "DQS",
            "viewBy": "premium",
            "weightedAverage": 5,
            "breakdown": [
                {
                    "name": "DQS5",
                    "total": 384134972,
                    "percent": 100,
                    "level2Breakdown": null
                }
            ]
        },
        {
            "breakdownBy": "yearOfEmissions",
            "viewBy": "account",
            "weightedAverage": null,
            "breakdown": [
                {
                    "name": null,
                    "total": 0,
                    "percent": 0,
                    "level2Breakdown": {
                        "breakdownBy": "Emission Type",
                        "breakdown": [
                            {
                                "name": "Stub Reported",
                                "total": 6,
                                "percent": 60
                            },
                            {
                                "name": "Stub Estimated",
                                "total": 3,
                                "percent": 30
                            },
                            {
                                "name": "Stub User Imported",
                                "total": 1,
                                "percent": 10
                            }
                        ]
                    }
                }
            ]
        }
    ]
}

SELECT ROUND(ZEROIFNULL(LEVEL1ARRAY.VALUE:weightedAverage),1) as weightedAverage,  
  REPLACE(LEVEL2ARRAY.VALUE:name,'"','') as name,
  LEVEL2ARRAY.VALUE:percent as percent,
  LEVEL2ARRAY.VALUE:total as total,
  COALESCE(LEVEL2ARRAY.VALUE:breakdownBy,'') as level2Breakdown,
  LEVEL3ARRAY.VALUE:name AS level2BreakdownName,
  LEVEL3ARRAY.VALUE:total AS level2BreakdownTotal,
  LEVEL3ARRAY.VALUE:percent AS level2BreakdownPercent
FROM
  ESG_ACCOUNTS_DATA,
  LATERAL FLATTEN (INPUT => PARSE_JSON(IAECARBONBASELINESBREAKDOWN):breakdownScope1and2) LEVEL1ARRAY,
  LATERAL FLATTEN (INPUT => LEVEL1ARRAY.VALUE:breakdown) LEVEL2ARRAY,
  LATERAL FLATTEN (INPUT => LEVEL2ARRAY.VALUE:breakdown) LEVEL3ARRAY,
WHERE
  variationid = '99e00658-a85e-419d-89f1-751f1dce1aba'
  AND IAECARBONBASELINESBREAKDOWN IS NOT NULL
  AND UPPER(LEVEL1ARRAY.VALUE:viewBy) = UPPER('account') AND
      UPPER(LEVEL1ARRAY.VALUE:breakdownBy) = UPPER('yearofEmissions')
  AND IAECARBONBASELINESBREAKDOWN LIKE '%level2Breakdown%';

Json for the same is below "level2Breakdown" might be null or can have a value. any pointers is really appreciated

{
    "breakdownScope1and2": [
        {
            "breakdownBy": "DQS",
            "viewBy": "account",
            "weightedAverage": 5,
            "breakdown": [
                {
                    "name": "DQS5",
                    "total": 30,
                    "percent": 100,
                    "level2Breakdown": null
                }
            ]
        },
        {
            "breakdownBy": "DQS",
            "viewBy": "premium",
            "weightedAverage": 5,
            "breakdown": [
                {
                    "name": "DQS5",
                    "total": 384134972,
                    "percent": 100,
                    "level2Breakdown": null
                }
            ]
        },
        {
            "breakdownBy": "yearOfEmissions",
            "viewBy": "account",
            "weightedAverage": null,
            "breakdown": [
                {
                    "name": null,
                    "total": 0,
                    "percent": 0,
                    "level2Breakdown": {
                        "breakdownBy": "Emission Type",
                        "breakdown": [
                            {
                                "name": "Stub Reported",
                                "total": 6,
                                "percent": 60
                            },
                            {
                                "name": "Stub Estimated",
                                "total": 3,
                                "percent": 30
                            },
                            {
                                "name": "Stub User Imported",
                                "total": 1,
                                "percent": 10
                            }
                        ]
                    }
                }
            ]
        }
    ]
}

Share Improve this question asked Mar 28 at 3:35 user3897533 4871 gold badge9 silver badges25 bronze badges

Add a comment |

1 Answer 1

Sorted by: Reset to default 1

LEVEL2ARRAY does not contain breakdown directly but it is under

 >level2Breakdown 
        >breakdown

You can simply paste the json and see it more clearly in any of the json editor online

meaning your LEVEL3ARRAY should be

  LATERAL FLATTEN (INPUT => LEVEL2ARRAY.VALUE:level2Breakdown:breakdown) LEVEL3ARRAY,

Your sample query becomes

SELECT ROUND(ZEROIFNULL(LEVEL1ARRAY.VALUE:weightedAverage),1) as weightedAverage,  
  REPLACE(LEVEL2ARRAY.VALUE:name,'"','') as name,
  LEVEL2ARRAY.VALUE:percent as percent,
  LEVEL2ARRAY.VALUE:total as total,
  COALESCE(LEVEL2ARRAY.VALUE:breakdownBy,'') as level2Breakdown,
  LEVEL3ARRAY.VALUE:name AS level2BreakdownName,
  LEVEL3ARRAY.VALUE:total AS level2BreakdownTotal,
  LEVEL3ARRAY.VALUE:percent AS level2BreakdownPercent
FROM
  ESG_ACCOUNTS_DATA,
  LATERAL FLATTEN (INPUT => PARSE_JSON(IAECARBONBASELINESBREAKDOWN):breakdownScope1and2) LEVEL1ARRAY,
  LATERAL FLATTEN (INPUT => LEVEL1ARRAY.VALUE:breakdown) LEVEL2ARRAY,
  LATERAL FLATTEN (INPUT => LEVEL2ARRAY.VALUE:level2Breakdown:breakdown) LEVEL3ARRAY,
WHERE
  variationid = '99e00658-a85e-419d-89f1-751f1dce1aba'
  AND IAECARBONBASELINESBREAKDOWN IS NOT NULL
  AND UPPER(LEVEL1ARRAY.VALUE:viewBy) = UPPER('account') AND
      UPPER(LEVEL1ARRAY.VALUE:breakdownBy) = UPPER('yearofEmissions')
  AND IAECARBONBASELINESBREAKDOWN LIKE '%level2Breakdown%';

Output

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

Querying nested json in snowflake - Stack Overflow

1 Answer 1

与本文相关的文章

评论列表(0)