JSON_OBJECT cannot be parsed when param includes FOR JSON PATH

[MohammadAliAfsahi]

• MSSQL Extension Version: 1.28.0
• VSCode Version: 1.98.0 (user setup)
• OS Version: Windows_NT x64 10.0.19045

Problem Description:
I am using the SQL Database Projects extension with MSSQL. I created an SQL function that utilizes the JSON_OBJECT built-in function. While the function executes successfully in SQL Server without errors, I encounter a build error (SQL46010: Incorrect syntax near 'FOR') when compiling the project using the extension. The error points to the line containing FOR JSON PATH, INCLUDE_NULL_VALUES. However, removing the JSON_OBJECT portion of the query resolves the build issue, confirming the error’s association with this syntax.

Requirements for the Scalar-Valued Function:

1. Return type: NVARCHAR(MAX).
2. Use FOR JSON PATH, INCLUDE_NULL_VALUES to return JSON output.
3. Include JSON_OBJECT in the SELECT statement to return at least one column as a JSON dictionary.

Steps to Reproduce:

1. Create the function in SQL Server.
2. Build the project using SQL Database Projects and MSSQL extensions.

Sample Query Causing Error:

CREATE FUNCTION [dbo].[MyFunction]()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN
    (
        SELECT
            t1.Id,
            JSON_OBJECT(
                'Column1': t1.Column1,
                'Column2': 
                (
                    SELECT
                        t2.*
                    FROM table2 t2
                    WHERE t1.Id = t2.Table2Id
                    FOR JSON PATH
                )
            ) AS jsonObject
        FROM table1 t1
        FOR JSON PATH, INCLUDE_NULL_VALUES
    )
END;
GO

How ever when I changed the code in a way to use JSON_QUERY I could build the project.

Workaround Found:
Replacing JSON_OBJECT with JSON_QUERY allows the project to build successfully.

Working Example:

CREATE FUNCTION [dbo].[MyFunction]()
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN
    (
        SELECT
            t1.Id,
            JSON_QUERY ((
                SELECT
                    t1.Column1 AS Column1, 
                    (
                        SELECT
                            t2.*
                        FROM table2 t2
                        WHERE t1.Id = t2.Table2Id
                        FOR JSON PATH
                    ) AS Column2
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )) AS jsonObject
        FROM table1 t1
        FOR JSON PATH, INCLUDE_NULL_VALUES
    )
END;
GO

Question:
Could you advise on why JSON_OBJECT triggers a syntax error during the build process, while JSON_QUERY resolves it? Am I missing a compatibility setting or syntax nuance specific to the SQL Database Projects extension?

[axl220]

I'm getting the same issue with using JSON_OBJECTAGG