How to check if a json nested object has empty arrays in snowflake

Question

In snowflake, How can I check if each of the regions below are empty or not. In the below example, since since all 3 regions are empty, it should return true

"objects": [
    {
      "zone": "zone_name",
      "clusters": [
        {
          "cluster_name": "abc" ,
          "regions": []
        },
        {
          "cluster_name": "def",
          "regions": []
        },
        {
          "cluster_name": "ghi",
          "regions": []
        }
      ]
    }
  ]

and in this example, since one of the region is not empty it should return false

"objects": [
        {
          "zone": "zone_name",
          "clusters": [
            {
              "cluster_name": "abc" ,
              "regions": []
            },
            {
              "cluster_name": "def",
              "regions": []
            },
            {
              "cluster_name": "ghi",
              "regions": ['UK','london']
            }
          ]
        }
      ]

How can I achieve this using sql in snowflake? Thanks a lot in advance 🙏

Answer 1

You'll need to recursively flatten it first

create or replace temporary table t as 

select 1 as id, parse_json($${"objects": [
        {
          "zone": "zone_name",
          "clusters": [
            {
              "cluster_name": "abc" ,
              "regions": []
            },
            {
              "cluster_name": "def",
              "regions": []
            },
            {
              "cluster_name": "ghi",
              "regions": ['UK','london']
            }
          ]
        }
      ]}$$) as col;

 select id, case when max(value) = [] then true else false end as flag
 from t, lateral flatten(col, recursive=>true)
 where key ='regions'
 group by id;