Return Table Object from Stored JSON in a Column

I am sorry, I know this has been asked a billion times but I have tried to put together CROSS APPLY statements without any luck and needs some help. I have this data in SQL in a single column which I can get to by select column from table where id = blah:

[ { “User”: “Bob”, “Domain”: “DOMAIN”, “Sid”: “S-1-5-21-3434343-3434343434-34343343434-242210” }, { “User”: “adm-Bob”, “Domain”: “DOMAIN”, “Sid”: “S-1-5-21-34343434-3434343434-3434334-242240” } ]

How do I turn it into a table with three columns and two roles? I can do this when there is only one record in the JSON but I am failing to figure it out when there is more then one account in the JSON.

Answer

Without knowing what you tried, I can’t tell you where you went wrong, but OPENJSON and a WITH works fine here:

DECLARE @JSON nvarchar(MAX) = '[ { "User": "Bob", "Domain": "DOMAIN", "Sid": "S-1-5-21-3434343-3434343434-34343343434-242210" }, { "User": "adm-Bob", "Domain": "DOMAIN", "Sid": "S-1-5-21-34343434-3434343434-3434334-242240" } ]';

SELECT OJ.[User],
       OJ.Domain,
       OJ.[Sid]
FROM (VALUES(@JSON))V(JSON)
     CROSS APPLY OPENJSON(V.JSON) 
     WITH ([User] nvarchar(100) '$.User',
           Domain nvarchar(100) '$.Domain',
           [Sid] nvarchar(100) '$.Sid') OJ;

DB<>Fiddle