I am using a database that was created in a SMO DW in Azure from a PostgreSQL database. There are column in some of the tables that are arrays. I would like to unnest these columns to be able to use them but can not seem to find a way to do this. Any help would be ideal?

CodePudding user response：

Those almost look like JSON arrays with uniqueidentifiers

So if you switch the curly brackets {} to square brackets [], then you can unnest them with OPENJSON.

create table Your_Table (
 users varchar(100)
);

insert into Your_Table (users) values
('{"6F9619FF-8B86-D011-B42D-00C04FC964FF"}'), 
('{"6F9619FF-8B86-D011-B42D-00C04FC964FF","0E984725-C51C-4BF4-9960-E1C80E27ABA0"}')

SELECT TRY_CONVERT(uniqueidentifier, a.value) as student_uid
FROM Your_Table
CROSS APPLY OPENJSON(TRANSLATE(users,'{}','[]')) a

student_uid
6f9619ff-8b86-d011-b42d-00c04fc964ff
6f9619ff-8b86-d011-b42d-00c04fc964ff
0e984725-c51c-4bf4-9960-e1c80e27aba0

Test on db<>fiddle here

CodePudding user response：

One way to handle it.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (userID INT IDENTITY PRIMARY KEY, users VARCHAR(1000));
INSERT INTO @tbl (users) VALUES
('{"01B334D2-64F9-4A3E-A5C3-D115E7A54A4E"}'),
('{"1BE8F1FC-08F1-4A73-A5FA-8A62AD0A1C47","504D4B75-27D8-42FF-9811-03B18247AE09"}'),
('{"AAD375D3-1244-4EC1-8346-DA783EBE591A"}');
-- DDL and sample data population, end

SELECT userID, users AS [Before]
    , QUOTENAME(TRIM('{}' FROM value), '{') AS [After]
FROM @tbl
    CROSS APPLY STRING_SPLIT(users, ',');

Output

 -------- ------------------------------------------ 
| userID |                  After                   |
 -------- ------------------------------------------ 
|      1 | {"01B334D2-64F9-4A3E-A5C3-D115E7A54A4E"} |
|      2 | {"1BE8F1FC-08F1-4A73-A5FA-8A62AD0A1C47"} |
|      2 | {"504D4B75-27D8-42FF-9811-03B18247AE09"} |
|      3 | {"AAD375D3-1244-4EC1-8346-DA783EBE591A"} |
 -------- ------------------------------------------