Getting the index of the JSON array using SQL in Snowflake

Question

I'm flattening a JSON data in snowflake using the Lateral Flatten.

I have the JSON data as follows:

{
   "Fruits": [
    {
      "Apple_Type" : Type_A,
      "Banana_Type": Type_B
    },
    {
      "Apple_Type" : Type_A2,
      "Banana_Type": Type_B3
    }
  ]
}

I used the following query to get the flattened data

SELECT  v.value:Apple_Type,
        v.value:Banana_Type
FROM Table1, LATERAL FLATTEN(input => Fruits) v

My Result:

--------------------------------
| Apple_Type    |  Banana_Type |
--------------------------------
| Type_A        |    Type_B   |
| Type_A2       |    Type_B3   |
--------------------------------

How do I get the index of the data. I want the table as follows

----------------------------------------------
| Apple_Type    |  Banana_Type |    Index    |
----------------------------------------------
| Type_A        |    Type_B   |      0      | -> Because Apple_Type is from index 0 in the Fruit Array
| Type_A2       |    Type_B3   |      1      | -> Because Banana_Type is from index 1 in the Fruit Array
----------------------------------------------

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2020-11-19T19:13:24

Using INDEX:

INDEX

The index of the element, if it is an array; otherwise NULL.

SELECT  v.value:Apple_Type,
        v.value:Banana_Type,
        v.index
FROM Table1, LATERAL FLATTEN(input => Fruits) v

Getting the index of the JSON array using SQL in Snowflake

1 Answers