Querying nested json in snowflake - Stack Overflow

admin管理员组

文章数量:1357683

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
                            }
                        ]
                    }
                }
            ]
        }
    ]
}

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
                            }
                        ]
                    }
                }
            ]
        }
    ]
}

• snowflake-cloud-data-platform
• snowflake-schema

Share Improve this question Follow asked Mar 28 at 3:35 user3897533user3897533 48711 gold badge99 silver badges2525 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 snowflakeStack Overflow