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.
Advertisement
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;