Here's my sample query in SQL Server 2017 for anizing the SQL normalized data to JSON data.

DECLARE @tbl_name 
    TABLE(
        id INT IDENTITY(1,1),
        u_id VARCHAR(30),
        f_name VARCHAR(50),
        l_name VARCHAR(50),
        dt_stamp DATETIME DEFAULT GETDATE()
        );

DECLARE @tbl_add
    TABLE(
        id INT IDENTITY(1,1),
        address VARCHAR(50),
        zip_code VARCHAR(5),
        u_id VARCHAR(30),
        dt_stamp DATETIME DEFAULT GETDATE()
        );

DECLARE @ID1 VARCHAR(30) = 'ID-1000-1001';
DECLARE @ID2 VARCHAR(30) = 'ID-1000-1002';
DECLARE @ID3 VARCHAR(30) = 'ID-1000-1003';

DECLARE @FN1 VARCHAR(50) = 'Juan';
DECLARE @FN2 VARCHAR(50) = 'Anna';
DECLARE @FN3 VARCHAR(50) = 'John';

DECLARE @LN1 VARCHAR(50) = 'Dela Cruz';
DECLARE @LN2 VARCHAR(50) = 'Smith';
DECLARE @LN3 VARCHAR(50) = 'Doe';

DECLARE @ADD1 VARCHAR(50) = 'Lucena City, Quezon, Ph';
DECLARE @ADD2 VARCHAR(50) = 'Quezon City, Metro Manila, Ph';
DECLARE @ADD3 VARCHAR(50) = 'Tayabas City, Quezon, Ph';

DECLARE @ZCODE1 VARCHAR(5) = '4301';
DECLARE @ZCODE2 VARCHAR(5) = '1100';
DECLARE @ZCODE3 VARCHAR(5) = '4327';

INSERT INTO
    @tbl_name
(
    u_id,
    f_name,
    l_name
)
VALUES
(
    @ID1,
    @FN1,
    @LN1
),
(
    @ID2,
    @FN2,
    @LN2
),
(
    @ID3,
    @FN3,
    @LN3
);

INSERT INTO
    @tbl_add
(
     address,
     zip_code,
     u_id
)
VALUES
(
    @ADD1,
    @ZCODE1,
    @ID1
),
(
    @ADD2,
    @ZCODE2,
    @ID2
),
(
    @ADD3,
    @ZCODE3,
    @ID3
);

SELECT
    tn.u_id AS [u_id],
    tn.f_name AS [name.f_name],
    tn.l_name AS [name.l_name],
    outer_apply.json_data AS [address_key]
FROM
    @tbl_name tn
OUTER APPLY
    (
        SELECT
            ta.address,
            ta.zip_code
        FROM
            @tbl_add ta
        WHERE
            ta.u_id = tn.u_id
        FOR JSON AUTO
    ) outer_apply(json_data)
FOR JSON PATH;

Surprisingly, the query is working fine after I executed it as shown in the image below.

With beautified JSON data output:

[
  {
    "u_id": "ID-1000-1001",
    "name": {
      "f_name": "Juan",
      "l_name": "Dela Cruz"
    },
    "address_key": [
      {
        "address": "Lucena City, Quezon, Ph",
        "zip_code": "4301"
      }
    ]
  },
  {
    "u_id": "ID-1000-1002",
    "name": {
      "f_name": "Anna",
      "l_name": "Smith"
    },
    "address_key": [
      {
        "address": "Quezon City, Metro Manila, Ph",
        "zip_code": "1100"
      }
    ]
  },
  {
    "u_id": "ID-1000-1003",
    "name": {
      "f_name": "John",
      "l_name": "Doe"
    },
    "address_key": [
      {
        "address": "Tayabas City, Quezon, Ph",
        "zip_code": "4327"
      }
    ]
  }
]

But I noticed an obvious syntax error that I am worried about:

'outer_apply' has more columns than specified in the column list

From this part of the query:

Moreover, I've been watching this relevant thread, but I ended up in confusion as to what to do next.

According to Martin Smith,

If you are actually getting this as a runtime error rather than an intellisense warning check that you are on the latest CU

However, when I execute SELECT @@VERSION, it prints:

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

According to my senior colleague, it is because the compiler of the SQL Server I am using does not consider my query as a valid code (i.e., on the portion that indicates a syntax error).

I want to know what exactly causes the conflict in the syntax of the given query to eventually address it properly.

After reevaluating my case scenario, I needed address_key's value to be encoded as array since I have to process multiple entries per parent record (e.g., a person could have more than one address). Therefore, I need to monitor the syntax issue on this portion:

OUTER APPLY
    (
        SELECT
            ta.address,
            ta.zip_code
        FROM
            @tbl_add ta
        WHERE
            ta.u_id = tn.u_id
        FOR JSON AUTO -- here
    ) outer_apply(json_data)

The goal here is to ensure that it does not introduce exceptions, particularly in production.

Interestingly, the query works fine as well when I call it inside the SP (just added for additional idea about the case).