Best JSON Array Set-up For Flatten in SQL-CodePudding

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?

Currently, for the array, I have:

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

For the SQL (Snowflake) I have

    with tab as (select parse_json(column1) as json
     from values(
       '{   "user": "1",  
        "class"  : "a",
        "group"       : "a1" }'))
    select * from tab,
    lateral flatten(input => json) f

However, this gives me a table like:

| JSON | KEY | PATH | VALUE | THIS |
|------|-----|------|-------|------|
| {...}| class|class|"a" |   {...} |
| {...}| group|group|"a1" |  {...} |
| {...}| user |user |"1" |   {...} |

Ultimately, what I would like is

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

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

CodePudding user response：

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

CodePudding user response：

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