Nested JSON using FOR JSON PATH

I have several tables that aren't joined but want to return the data out as JSON from the query using SQL Server using FOR JSON, possibly using sub-queries?

Create tables;

DECLARE @Filter AS TABLE
(
    filter_id int primary key,
    filter_field varchar(255),
    filter_values varchar(255)
)
DECLARE @Other AS TABLE
(
    other_id int primary key,
    other_field varchar(255),
    other_values varchar(255)
)
DECLARE @Data AS TABLE
(
    data_id int primary key,
    Col1 varchar(255),
    Col2 varchar(255),
    Col3 varchar(255)
)

Insert the data;

INSERT INTO @Filter (filter_id, filter_field,filter_values) VALUES
(1, 'SC.Type','Parent'),
(2, 'ScanDateTime','20200620')

INSERT INTO @Other (other_id, other_field,other_values) VALUES
(1, 'header','This is the header'),
(2, 'footer','This is the footer')

INSERT INTO @Data (data_id,Col1,Col2,Col3) VALUES
(1, 'Val1','Val2','Val3'),
(2, 'Val4','Val5','Val6'),
(3, 'Val7','Val8','Val9')

I consistently get the @Filter and @Other repeated with every line of the @Data.

What would be the query FOR JSON to get back the following result;

{
    "filter":
        [
            {
                "field":"SC.Type",
                "values":"Parent"
            },
            {
                "field":"ScanDateTime"
                "values":"20200620"
            }
        ],
    "header":"This is the header",
    "footer":"This is the footer",
    "data":
        [
            {
                "col1":"Val1",
                "col2":"Val2",
                "col3":"Val3"
            },
            {
                "col1":"Val4",
                "col2":"Val5",
                "col3":"Val6"
            },
            {
                "col1":"Val7",
                "col2":"Val8",
                "col3":"Val9"
            }
        ]
}

Any help would be appreciated.

Original answer:

One possible approach is the following statement:

SELECT 
   filter = (
      SELECT filter_field AS [field], filter_values AS [values]
      FROM @filter
      FOR JSON AUTO     
   ),
   MAX(CASE WHEN other_field = 'header' THEN other_values END) AS header,
   MAX(CASE WHEN other_field = 'footer' THEN other_values END) AS footer,
   data = (
      SELECT Col1, Col2, Col3
      FROM @Data
      FOR JSON AUTO
   )
FROM @Other   
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Result:

{
   "filter":[
       {"field":"SC.Type","values":"Parent"}, 
       {"field":"ScanDateTime","values":"20200620"}
   ],
   "header":"This is the header",
   "footer":"This is the footer",
   "data":[
       {"Col1":"Val1","Col2":"Val2","Col3":"Val3"}, 
       {"Col1":"Val4","Col2":"Val5","Col3":"Val6"}, 
       {"Col1":"Val7","Col2":"Val8","Col3":"Val9"}
   ]
}

Update:

If @Other table has more than header and footer rows and the values of these rows are different (valid JSON content or text), you need a mixed approach using string aggregation with STRING_SPLIT() and JSON_MODIFY():

Tables:

DECLARE @Filter AS TABLE
(
   filter_id int primary key,
   filter_field varchar(255),
   filter_values varchar(255)
)
DECLARE @Other AS TABLE
(
   other_id int primary key,
   other_field varchar(255),
   other_values varchar(255)
)
DECLARE @Data AS TABLE
(
   data_id int primary key,
   Col1 varchar(255),
   Col2 varchar(255),
   Col3 varchar(255)
)
INSERT INTO @Filter (filter_id, filter_field, filter_values) VALUES
   (1, 'SC.Type','Parent'),
   (2, 'ScanDateTime','20200620')
INSERT INTO @Other (other_id, other_field, other_values) VALUES
   (1, 'header','[{"Row1":"Val1"},{"Row2":"Val2"}]'),
   (2, 'footer','This is the footer'),
   (3, 'other','Something different')
INSERT INTO @Data (data_id, Col1, Col2, Col3) VALUES
   (1, 'Val1','Val2','Val3'),
   (2, 'Val4','Val5','Val6'),
   (3, 'Val7','Val8','Val9')

Statement:

SELECT
   JSON_MODIFY(
      JSON_MODIFY(
         CONCAT(
            '{', 
            STRING_AGG(CONCAT(
               '"', 
               other_field , 
               '":', 
               CASE WHEN ISJSON(other_values) = 1 THEN other_values ELSE CONCAT('"', other_values, '"') END, 
               ''
            ), ','), 
            '}'
         ),
         '$.filter',
         JSON_QUERY((
            SELECT filter_field AS [field], filter_values AS [values]
            FROM @filter
            FOR JSON AUTO     
         ))
      ),
      '$.data',
      JSON_QUERY((
          SELECT Col1, Col2, Col3
          FROM @Data
          FOR JSON AUTO     
      ))   
   )   
FROM @Other

Result:

{
  "header":[
    {
      "Row1":"Val1"
    },
    {
      "Row2":"Val2"
    }
  ],
  "footer":"This is the footer",
  "other":"Something different",
  "filter":[
    {
      "field":"SC.Type",
      "values":"Parent"
    },
    {
      "field":"ScanDateTime",
      "values":"20200620"
    }
  ],
  "data":[
    {
      "Col1":"Val1",
      "Col2":"Val2",
      "Col3":"Val3"
    },
    {
      "Col1":"Val4",
      "Col2":"Val5",
      "Col3":"Val6"
    },
    {
      "Col1":"Val7",
      "Col2":"Val8",
      "Col3":"Val9"
    }
  ]
}