What is PostgreSQL Array Column equivalent in Synapse SQL?

Question

I have a table in PostgreSQL that has array column like abc_id varchar[](1) and it has data in it like {A,B,C}.

I want to create that table in Synapse SQL how can I create array columns is it possible?

Answer 1

There is no explicit array type ibn Synapse SQL. We consider the array type data as complex type. As mentioned in the comment, consider using JSON as the main base.

The below image will explain the scheme of array and nested structure.

The following code block is the sample JSON example of implementing nested structures, which is a complex type and acceptable in Synapse SQL.

{
    "id": "66532691-ab20-11ea-8b1d-936b3ec64e54",
    "context": {
        "data": {
            "eventTime": "2020-06-10T13:43:34.553Z",
            "samplingRate": "100.0",
            "isSynthetic": "false"
        },
        "session": {
            "isFirst": "false",
            "id": "38619c14-7a23-4687-8268-95862c5326b1"
        },
        "custom": {
            "dimensions": [
                {
                    "customerInfo": {
                        "ProfileType": "ExpertUser",
                        "RoomName": "",
                        "CustomerName": "diamond",
                        "UserName": "[email protected]"
                    }
                },
                {
                    "customerInfo": {
                        "ProfileType": "Novice",
                        "RoomName": "",
                        "CustomerName": "topaz",
                        "UserName": "[email protected]"
                    }
                }
            ]
        }
    }
}