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?
-
1SQL Server has no array data type(s); the closest you could get would be XML or JSON data. Use a normalised design.Thom A– Thom A ♦2022-05-18 09:28:28 +00:00Commented May 18, 2022 at 9:28
Add a comment
|
1 Answer
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]"
}
}
]
}
}
}
