Array within Element within Array in Variant

Question

How can I get the data out of this array stored in a variant column in Snowflake. I don't care if it's a new table, a view or a query. There is a second column of type varchar(256) that contains a unique ID. If you can just help me read the "confirmed" data and the "editorIds" data I can probably take it from there. Many thanks!

Output example would be

UniqueID    ConfirmationID     EditorID
u3kd9       xxxx-436a-a2d7     nupd
u3kd9       xxxx-436a-a2d7     9l34c
R3nDo       xxxx-436a-a3e4     5rnj
yP48a       xxxx-436a-a477     jTpz8
yP48a       xxxx-436a-a477     nupd
    
[
  {
    "confirmed": {
      "Confirmation": "Entry ID=xxxx-436a-a2d7-3525158332f0: Confirmed order submitted.",
      "ConfirmationID": "xxxx-436a-a2d7-3525158332f0",
      "ConfirmedOrders": 1,
      "Received": "8/29/2019 4:31:11 PM Central Time"
    },
    "editorIds": [
      "xxsJYgWDENLoX",
      "JR9bWcGwbaymm3a8v",
      "JxncJrdpeFJeWsTbT"
    ] ,
    "id": "xxxxx5AvGgeSHy8Ms6Ytyc-1",
    "messages": [],
    "orderJson": {
      "EntryID": "xxxxx5AvGgeSHy8Ms6Ytyc-1",
      "Orders": [
        {
          "DropShipFlag": 1,
          "FromAddressValue": 1,
          "OrderAttributes": [
            {
              "AttributeUID": 548
            },
            {
              "AttributeUID": 553
            },
            {
              "AttributeUID": 2418
            }
          ],
          "OrderItems": [
            {
              "EditorId": "aC3f5HsJYgWDENLoX",
              "ItemAssets": [
                {
                  "AssetPath": "https://xxxx573043eac521.png",
                  "DP2NodeID": "10000",
                  "ImageHash": "000000000000000FFFFFFFFFFFFFFFFF",
                  "ImageRotation": 0,
                  "OffsetX": 50,
                  "OffsetY": 50,
                  "PrintedFileName": "aC3f5HsJYgWDENLoX-10000",
                  "X": 50,
                  "Y": 52.03909266409266,
                  "ZoomX": 100,
                  "ZoomY": 93.75
                }
              ],
              "ItemAttributes": [
                {
                  "AttributeUID": 2105
                },
                {
                  "AttributeUID": 125
                }
              ],
              "ItemBookAttribute": null,
              "ProductUID": 52,
              "Quantity": 1
            }
          ],
          "SendNotificationEmailToAccount": true,
          "SequenceNumber": 1,
          "ShipToAddress": {
            "Addr1": "Addr1",
            "Addr2": "0",
            "City": "City",
            "Country": "US",
            "Name": "Name",
            "State": "ST",
            "Zip": "00000"
          }
        }
      ]
    },
    "orderNumber": null,
    "status": "order_placed",
    "submitted": {
      "Account": "350000",
      "ConfirmationID": "xxxxx-436a-a2d7-3525158332f0",
      "EntryID": "xxxxx-5AvGgeSHy8Ms6Ytyc-1",
      "Key": "D83590AFF0CC0000B54B",
      "NumberOfOrders": 1,
      "Orders": [
        {
          "LineItems": [],
          "Note": "",
          "Products": [
            {
              "Price": "00.30",
              "ProductDescription": "xxxxxint 8x10",
              "Quantity": 1
            },
            {
              "Price": "00.40",
              "ProductDescription": "xxxxxut Black 8x10",
              "Quantity": 1
            },
            {
              "Price": "00.50",
              "ProductDescription": "xxxxx"
            },
            {
              "Price": "00.50",
              "ProductDescription": "xxxscount",
              "Quantity": 1
            }
          ],
          "SequenceNumber": "1",
          "SubTotal": "00.70",
          "Tax": "1.01",
          "Total": "00.71"
        }
      ],
      "Received": "8/29/2019 4:31:10 PM Central Time"
    },
    "tracking": null,
    "updatedOn": 1.598736670503000e+12
  }
]

Can you share how you want to see the output of the data you've requested? That will help shape the query correctly. — Mike Walton

Mike Walton Mike Walton · Accepted Answer · 2020-09-23T00:45:27

So, this is how I'd query that exact JSON assuming the data is in column var in table x:

SELECT x.var[0]:confirmed:ConfirmationID::varchar as ConfirmationID,
       f.value::varchar as EditorID
FROM x,
LATERAL FLATTEN(input => var[0]:editorIds) f
;

Since your sample output doesn't match the JSON that you provided, I will assume that this is what you need.

Also, as a note, your JSON includes outer [ ] which indicates that the entire JSON string is inside an array. This is the reason for var[0] in my query. If you have multiple records inside that array, then you should remove that. In general, you should exclude those and instead load each record into the table separately. I wasn't sure whether you could make that change, so I just wanted to make note.

Array within Element within Array in Variant

1 Answers