Best JSON Array Set-up For Flatten in SQL

Question

I need to write a simple array of user, group, and class that will eventually be parsed in SQL and flattened so that user, group, and class are each columns. I am wondering the best JSON array and SQL syntax to achieve this result?

Edit: Updated to show an array not an object

Currently, for the array, I have:

[

 {   
  "user": "1",  
  "class"  : "a",
  "group"  : "a1" 
 },

 {   
  "user": "2",  
  "class"  : "b",
  "group"  : "b1" 
 }
]

For the SQL (Snowflake) I have

   with tab as (select parse_json(column1) as json
 from values(
   '[

{   
"user": "1",  
"class"  : "a",
"group"  : "a1" 
},

{   
"user": "2",  
"class"  : "b",
"group"  : "b1" 
}]'))
select * from tab,
lateral flatten(input => json) f

However, this gives me a table like:

| JSON | KEY | PATH | VALUE |
|------|-----|------|-------|
| {...}|     |[0]|  { "class": "a", "group": "a1", "user": "1" } |  
| {...}|     |[1]|  { "class": "b", "group": "b1", "user": "2" } |

Ultimately, what I would like is

| JSON | user | class | group |
|------|------|-------|-------|
| {...}| 1    |     a |     a1|
| {...}| 2    |     b |     b1|

What is the best JSON array set-up and SQL syntax to get this result?

UPDATE/ANSWER

Combining the two suggestions below, I have reached a satisfying answer:

with tab as (select parse_json(column1) as json
     from values(
       '[

    {   
    "user": "1",  
    "class"  : "a",
    "group"  : "a1" 
    },

    {   
    "user": "2",  
    "class"  : "b",
    "group"  : "b1" 
    }

]'))
    select json, value
    ,value:"user"::STRING  AS "user"
      ,value:class::STRING   AS class
      ,value:"group"::STRING AS "group"from tab,
    lateral flatten(input => json) f

user_5070879 · Answer

FLATTEN is not required. To access specific key/value ::

with tab as (
   select parse_json(column1) as json
   from values(
       '{   "user": "1",  
        "class"  : "a",
        "group"       : "a1" }')
)
select json
      ,json:"user"::STRING  AS "user"
      ,json:class::STRING   AS class
      ,json:"group"::STRING AS "group"
from tab;

Output:

Related: JSON - Query Data

user_43992 · Answer

This is not an array, but an object.

{   
"user": "1",  
"class"  : "a",
"group"  : "a1" 
}

For it to be an array,

[

    {   
    "user": "1",  
    "class"  : "a",
    "group"  : "a1" 
    },

    {   
    "user": "1",  
    "class"  : "a",
    "group"  : "a1" 
    }

]

Now this if dynamic could/sholud be flattened.

BUT if you where reading this as a file, you would want to use a Json file tupe and strip outer set true. At which point you willjust be handed objects. So dont need flatten

Best JSON Array Set-up For Flatten in SQL

2 Answers