Snowflake Flatten JSON Multiple Arrays

Question

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:

ItemCount	ItemDesc	ItemType	OrderNumber
1.00	Series 100 Food Bucket	Bucket/Pail	123
1.00	Series X Leather Gloves	Gloves	123

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?

Greg Pavlik · Accepted Answer · 2022-11-22T13:01:04.583

0

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
;

ITEM_COUNT	ITEM_DESC	ITEM_TYPE	ORDERNUMBER
1	Series 100 Food Bucket	Bucket/Pail	123
1	Series X Leather Gloves	Gloves	123

edited Nov 22 '22 at 13:01

answered Nov 21 '22 at 07:01

Greg Pavlik

10,089
2
12
29

That worked well. I had to tweak it a bit because each order had a different number of items (they are not consistent) and adding the Order Number to criteria solved that. Is there a good way to add the Order Number (ordernumber) as a column? – user3890455 Nov 22 '22 at 05:40
You can just project the ordernumber on any one of the table expressions to join. I updated the answer to show this. – Greg Pavlik Nov 22 '22 at 13:01
Thanks, I had to add order number to each of the select statements and use that as part of the join criteria as well which solved the issue. Appreciate the help! – user3890455 Nov 22 '22 at 13:32

Snowflake Flatten JSON Multiple Arrays

1 Answers1