Snowflake query to create json - multiple results for single attribute

Question

I have a table in snowflake that has three different columns being used as name type/values. These need to be mapped in an MDM tool using json. Of course, I cannot use the same attribute name more than once, or an error occurs.

NameType1	NameType2	NameType3
AL_PL	Alpha Place	Alpha Place Main Store
BET_PL	Beta Place	Beta Place Primary Store

The goal is to have the following:

 "OrgNames": [
                {
                    "value": {
                        "OrgName": [
                            {
                                "value": "AL_PL"
                            }
                        ],
                        "OrgNameType": [
                            {
                                "value": "NameType1"
                            }
                        ]
                    }
                },
                {
                    "value": {
                        "OrgName": [
                            {
                                "value": "Alpha Place"
                            }
                        ],
                        "OrgNameType": [
                            {
                                "value": "NameType2"
                            }
                        ]
                    }
                },
                {
                    "value": {
                        "OrgName": [
                            {
                                "value": "Alpha Place Main Store"
                            }
                        ],
                        "OrgNameType": [
                            {
                                "value": "NameType3"
                            }
                        ]
                    }
                }
            ],

I have created CTEs

org_ali AS (
SELECT tablespaceID
        ,DEPARTMENTID
        ,(object_construct_keep_null(
            'OrgName', array_agg(object_construct_keep_null(
            'value', src.NameType1
            )
            ),
            'OrgNameType', array_agg(object_construct_keep_null(
             'value', 'NameType1'
            )
            )
        )
        ) AS AliasName                  
FROM TABLE AS src
        GROUP BY tablespaceID,
        DEPARTMENTID,
        NameType1

And the in a later CTE added (with a join in place)

,
                'OrgNames',array_agg(object_construct_keep_null('value', org_ali.AliasName)
                ),

When only one result is required, this works perfectly. However, I need to replicate the value key pairs with different data under the same named struct.

I cannot seem to build this without a conflict in one of the keys. And the constraints of Snowflake is no more than 1 argument in the struct.

Clearly, there is something I am missing. Any help would be great. Thank you.

Answer 1

I've got the code, and it works great (tested in Snowflake). Just use the LISTAGG function to make it happen.

    SELECT
  PARSE_JSON(
    '[' ||
      LISTAGG(
        '{
          "value": {
            "OrgName": [{"value": "' || column_value || '"}],
            "OrgNameType": [{"value": "' || column_name || '"}]
          }
        }',
        ', '
      )
      WITHIN GROUP (ORDER BY column_index) ||
    ']'
  )::VARIANT AS OrgNames
FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS column_index,
    column_name,
    column_value
  FROM --ENTER YOUR TABLE NAME HERE
  UNPIVOT (column_value FOR column_name IN (NameType1, NameType2, NameType3))
);