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 · Accepted Answer · 2020-11-19 19:13:24Z

4

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

answered Nov 19, 2020 at 19:13

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

R0bert Over a year ago

Thank you, I have been searching for this on many websites but couldn't find it. Thanks, for our help

Emanuel Oliveira Over a year ago

Just complementing Lukasz answer that index order is not guaranteed, so be very careful not to use python like PK or FK in future accesses: docs.snowflake.com/en/sql-reference/functions/flatten#output

Collectives™ on Stack Overflow

Getting the index of the JSON array using SQL in Snowflake

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related