integer[] type in Azure SQL Server

Question

In PostgresSQL I used array types for storing ids, uuids etc like:

CREATE TABLE IF NOT EXISTS eventing.notifications (event_type integer NOT NULL, transport_type integer, user_id uuid, user_ids uuid[]);

or

CREATE TABLE IF NOT EXISTS public.sources (name character varying(255), timestamp without time zone, read_only_organization_ids integer[], organization_id uuid);

Is there an equivalent using Microsoft SQL server in Azure?

Answer 1

This is blurring the lines between SQL Server and nosql, however you can do it by encoding your array into a json array and store that in a varchar(max) column.

Then to create the json array from some other table storing user ids you would use the for json clause.

To get the original array out of the varchar column you can cross apply with the openjson function:

declare @notifications table (user_ids varchar(max))

declare @user_ids varchar(max)

;with cte_jsonUser(jsonIds) as
(
    select id
    from (values(1), (2)) as tbluser(id)
    for json auto
)
insert into @notifications(user_ids)
select replace(replace(jsonIds,'{"id":',''),'}','')
from cte_jsonUser

select user_ids from @notifications

-- user_ids
-- [1,2]

select i.user_ids
from @notifications as n
cross apply openjson(n.user_ids)
with (user_ids int '$') as i

-- user_ids
-- 1
-- 2