Snowflake Flatten JSON Multiple Arrays

Snowflake Flatten JSON Multiple Arrays

Problem Description:

I’m receiving multiple JSON files and loading them into a Snowflake variant field. I would like to then extract some of the child arrays into a separate table but am having issues with that. Here is an example of the JSON:

{
  "ordernumber": 123,
  "customername": "Smith,John",
  "orderdata": {
    "Order": {
      "ItemCountGroup": {
        "items": {
          "item": [
            {
              "ItemCount": "1.00"
            },
            {
              "ItemCount": "1.00"
            }
          ]
        }
      },
      "ItemDescGroup": {
        "items": {
          "item": [
            {
              "ItemDesc": "Series 100 Food Bucket"
            },
            {
              "ItemDesc": "Series X Leather Gloves"
            }
          ]
        }
      },
      "ItemTypeGroup": {
        "items": {
          "item": [
            {
              "ItemType": "Bucket/Pail"
            },
            {
              "ItemType": "Gloves"
            }
          ]
        }
      },
    }
  }
}

Ideally, I’d like to flatten the table so that it comes out:

I’ve tried a series of different flatten commands and lateral flattens but usually am getting a row for every combination possible between each of the elements. Is there a way or example out there for me to get these into the required format?

Solution – 1

If you have a table named T1 with your JSON in a variant column named V, you can flatten each part in a CTE and join them by the index created during the flattening:

with ItemCountGroup as
(
select v:ordernumber ORDERNUMBER, INDEX IDX, VALUE:ItemCount::number(38,2) ITEM_COUNT from T1, 
    table(flatten(v:orderdata.Order.ItemCountGroup.items.item))
),
ItemDescriptionGroup as
(
select INDEX IDX, VALUE:ItemDesc::string ITEM_DESC from T1, 
    table(flatten(v:orderdata.Order.ItemDescGroup.items.item))
),
ItemTypeGroup as
(
select INDEX IDX, VALUE:ItemType::string ITEM_TYPE from T1, 
    table(flatten(v:orderdata.Order.ItemTypeGroup.items.item))
)
select ITEM_COUNT, ITEM_DESC, ITEM_TYPE, ORDERNUMBER
from ItemCountGroup IC
    left join ItemDescriptionGroup ID on IC.IDX = ID.IDX
    left join ItemTypeGroup IT on IC.IDX = IT.IDX
;