snowflake creating json structure from table data

Question

I have a view which returns data in below format and I want that to be UPSERTED into a table in addition to that I want the row values loaded into new column in JSON structure. JSON structure need to be formed based on ID and NAME column and the rest need to be array inside JSON.

Sample source data

I want the values to be loaded into target table as similar to above additionally need JSON structured values to be formed by grouping on ID and NAME column and loaded into JSON column which is variant type.

Sample Target table

Sample JSON column value need to be populated for JSON1, JSON2 and JSON 3 are below. Any help to achieve the desired result is much appreciated. Thanks

JSON1

[
    {
        "col1": "1",
        "col2": "TEST001",
        "war_detail":[
                        {   "War_id": "WR001",
                            "War_start_date": "1/1/1970",
                            "War_end_date": "12/12/9999"
                        }
                    ],
        "Con_details": [
                            {   "Cont_id": "CON001",
                                "Con_start_date": "1/1/1970",
                                "Con_end_date": "12/12/9999"
                            },
                            {   "Cont_id": "CON002",
                                "Con_start_date": "1/1/2000",
                                "Con_end_date": "12/12/9999"
                            }
                        ]
    },
    {
        "col1": "9",
        "col2": "TEST001",
        "war_detail":[
                        {   "War_id": "WR001",
                            "War_start_date": "1/1/1970",
                            "War_end_date": "12/12/9999"
                        }
                    ],
        "Con_details": [
                            {   "Cont_id": "CON123",
                                "Con_start_date": "1/1/2010",
                                "Con_end_date": "12/12/9999"
                            }
                        ]
    },
    
    
]

---------------------------------------
JSON2

[
    JSON:{
            "col1": "2",
            "col2": "TEST002",
            "war_detail":[
                            {   "War_id": "WR987",
                                "War_start_date": "1/1/1970",
                                "War_end_date": "12/12/9999"
                            },
                            {   "War_id": "WR123",
                                "War_start_date": "1/1/1990",
                                "War_end_date": "12/12/9999"
                            }
                        ],
            "Con_details": [
                                {   "Cont_id": "CON003",
                                    "Con_start_date": "1/1/2020",
                                    "Con_end_date": "12/12/9999"
                                }
                            ]
        }
]
---------------------------------------
JSON3

[
    JSON:{
            "col1": "2",
            "col2": "TEST002",
            "war_detail":[
                            {   "War_id": "WR678",
                                "War_start_date": "1/1/2001",
                                "War_end_date": "12/12/2023"
                            },
                            {   "War_id": "WR004",
                                "War_start_date": "1/1/2010",
                                "War_end_date": "12/12/2030"
                            }
                        ],
            "Con_details": []
        }
]

Simeon Pilgrim Simeon Pilgrim · Accepted Answer · 2021-04-13T05:06:47

So firstly, the picture is helpful, but really a text block with the values in them makes copying the values into SQL much nicer than have to type it all out by hand.

But using a CTE for the data:

WITH data AS (
    SELECT * FROM VALUES
        (1, 'test001', 'WR001', '1970-01-01', '9999-12-12', 'CON001', '1970-01-01', '9999-12/12'),
        (1, 'test001', 'WR001', '1970-01-01', '9999-12-12', 'CON002', '1970-01-01', '9999-12/12'),
        (9, 'test001', 'WR001', '1970-01-01', '9999-12-12', 'CON123', '1970-01-01', '9999-12/12'),
        (2, 'test002', 'WR987', '2020-01-01', '9999-12-12', 'CON003', '1970-01-01', '9999-12/12'),
        (2, 'test002', 'WR123', '1990-01-01', '9999-12-12', 'CON003', '1970-01-01', '9999-12/12'),
        (3, 'test003', 'WR678', '2001-01-01', '2023-12-12', null, null, null),
        (3, 'test003', 'WR004', '2010-01-01', '2030-12-12', null, null, null)
        v(id, name, war_id, war_start_date, war_end_date, cont_id, con_start_date, con_end_date)
)

Assuming your JSON1,JSON2,JSON3 are effectively NAME then

We can, nest some OBJECT_CONSTRUCT and ARRAY_AGG to build up the data as expected.

SELECT 
    array_agg(war_block) WITHIN GROUP (ORDER BY war_block:col1) as json_block
FROM (
    SELECT name,
        object_construct('col1', id, 'col2', name, 'war_detail', a_war, 'con_details', a_con) as war_block
    FROM (
        SELECT id
            ,name
            ,array_agg(distinct war) WITHIN GROUP (ORDER BY war:war_start_date) AS a_war
            ,array_agg(distinct con) WITHIN GROUP (ORDER BY con:con_start_date) AS a_con
        FROM (
            SELECT id
                ,name
                ,object_construct('war_id', war_id, 'war_start_date', war_start_date, 'war_end_date', war_end_date) as war
                ,object_construct('cont_id', cont_id, 'con_start_date', con_start_date, 'con_end_date', con_end_date) as con
            FROM data
        )
        GROUP BY id, name
    )
)
GROUP BY name
ORDER BY name;

which gives:

JSON_BLOCK
[    {      "col1": 1,      "col2": "test001",      "con_details": [        {          "con_end_date": "9999-12/12",          "con_start_date": "1970-01-01",          "cont_id": "CON001"        },        {          "con_end_date": "9999-12/12",          "con_start_date": "1970-01-01",          "cont_id": "CON002"        }      ],      "war_detail": [        {          "war_end_date": "9999-12-12",          "war_id": "WR001",          "war_start_date": "1970-01-01"        }      ]    },    {      "col1": 9,      "col2": "test001",      "con_details": [        {          "con_end_date": "9999-12/12",          "con_start_date": "1970-01-01",          "cont_id": "CON123"        }      ],      "war_detail": [        {          "war_end_date": "9999-12-12",          "war_id": "WR001",          "war_start_date": "1970-01-01"        }      ]    }  ]
[    {      "col1": 2,      "col2": "test002",      "con_details": [        {          "con_end_date": "9999-12/12",          "con_start_date": "1970-01-01",          "cont_id": "CON003"        }      ],      "war_detail": [        {          "war_end_date": "9999-12-12",          "war_id": "WR123",          "war_start_date": "1990-01-01"        },        {          "war_end_date": "9999-12-12",          "war_id": "WR987",          "war_start_date": "2020-01-01"        }      ]    }  ]
[    {      "col1": 3,      "col2": "test003",      "con_details": [        {}      ],      "war_detail": [        {          "war_end_date": "2023-12-12",          "war_id": "WR678",          "war_start_date": "2001-01-01"        },        {          "war_end_date": "2030-12-12",          "war_id": "WR004",          "war_start_date": "2010-01-01"        }      ]    }  ]

To note the order of the properties are not the same, but they have no order, but the arrays are ordered by what I assume you intended.

snowflake creating json structure from table data

1 Answers