SQL Server : foreign key selection, getting sub table as object in return

Question

I'm usually working with Firestore, so I'm quite new to SQL Server.

I'm trying to get a many-to-many relationship to work with a simple example.

Here's my approach - tables:

create table tbl_forms
(
    id INT NOT NULL IDENTITY(1, 1),
    title nvarchar(255), 
    description text,
    primary key(id)
)

create table tbl_tags
(
    id INT NOT NULL IDENTITY(1, 1),
    tag nvarchar(255), 
    color nvarchar(7),
    primary key(id)
)

I'm trying to get a selection with the following:

from tbl_forms           from tbl_tags
______________________   ______________
id, title, description,  { tag, color }

Is there a way to actually get the second table (in this case, tags) as an object? So in case of more than one result I'm getting an object that looks like:

[{ 'tag1', 'red' }, {'tag_no_2', '#abcabc' }]

I tried working with a help table which contains both id's but I'm not quite sure how to achieve the desired result

create table x_form_tags
(
    id_forms int FOREIGN KEY REFERENCES tbl_forms(id),
    id_tags int FOREIGN KEY REFERENCES tbl_tags(id)
)

Answer 1

You can use the FOR JSON PATH operator inside an APPLY to create a JSON array for each row of tbl_forms.

SELECT
  f.id,
  f.title,
  f.description,
  j.tags
FROM tbl_forms f
CROSS APPLY (
    SELECT
      t.tag,
      t.color
    FROM x_form_tags ft
    JOIN tbl_tags t ON t.id = ft.id_tags
    WHERE ft.id_forms = f.id   -- correlation condition
    FOR JSON PATH
) j(tags);