Use SQL Server's FOR JSON function with both Numeric and JSON inputs

Question

I'm struggling to combine the contents of a single table in SQL Server that contains both numeric data and JSON-formatted strings into a consolidated JSON output using SQL Server's "FOR JSON" function.

I'm running into issues with the escape characters mainly, doesn't appear to be an easy approach to combining JSON and non-JSON data into a JSON-formatted SQL Server Query output. Can anyone point me to an example of a successful query that's done this?

Sample Data:

CREATE TABLE settings (
    Id INT PRIMARY KEY NOT NULL,
    FileName nvarchar(max) NOT NULL,
    FilePath nvarchar(max) NOT NULL,
    Date datetime NOT NULL,
    Json nvarchar(max)
    );


INSERT INTO settings 
    (
    Id,
    FileName,
    FilePath,
    Date,
    Json
    )
VALUES
    (
    1,
    'contents.json',
    'folder1/folder2/contents.json',
    '2000-01-01T00:00:00.000',
    '{
                "A": 10,
                "B": 20,
                "C": {
                    "setting1": 30,
                    "setting2": 40,
                    "setting3": 50
                },
                "D": {
                    "setting1": 30,
                    "setting2": 40,
                    "setting3": 50
                },
                "E": true,
                "F": false
                "G": "John Doe"
            }'
    );

Sample Query:

SELECT
    FileName
    , FilePath
    , Id
    , Date
    , Json
FROM settings
FOR JSON AUTO;

Expected Output:

    [
        {
            "FileName": "contents.json",
            "FilePath": "folder1/folder2/contents.json",
            "Id": 1,
            "Date": "2000-01-01T00:00:00.000",
            "Json": [
                {
                    "A": 10,
                    "B": 20,
                    "C": {
                        "setting1": 30,
                        "setting2": 40,
                        "setting3": 50
                    },
                    "D": {
                        "setting1": 30,
                        "setting2": 40,
                        "setting3": 50
                    },
                    "E": true,
                    "F": false
                    "G": "John Doe"
                }
            ]
        }
    ]

Have a look at this post here to remove escape characters from JSon string if its valid. I believe in your case you need to check if json is valid then you can apply JSON AUTO. stackoverflow.com/questions/57242858/… — iVad
– iVad, Commented Feb 26, 2020 at 4:16
One more similar thread...social.msdn.microsoft.com/Forums/sqlserver/en-US/… — iVad
– iVad, Commented Feb 26, 2020 at 4:19

AlwaysLearning · Accepted Answer · 2020-02-26 09:45:27Z

1

Your example settings.Json is invalid:

                "F": false

Should be:

                "F": false,

Once you fix that issue you can change your query to nest Json's JSON like this:

SELECT
    FileName
    , FilePath
    , Id
    , Date
    , [Json] = Json_Query(concat('[', Json, ']'))
FROM settings
FOR JSON AUTO;

Which yields the result:

[
    {
        "FileName": "contents.json",
        "FilePath": "folder1\/folder2\/contents.json",
        "Id": 1,
        "Date": "2000-01-01T00:00:00",
        "Json": [
            {
                "A": 10,
                "B": 20,
                "C": {
                    "setting1": 30,
                    "setting2": 40,
                    "setting3": 50
                },
                "D": {
                    "setting1": 30,
                    "setting2": 40,
                    "setting3": 50
                },
                "E": true,
                "F": false,
                "G": "John Doe"
            }
        ]
    }
]

Note that SQL Server has escaped / characters in FilePath using \/ as per the JSON specification.

answered Feb 26, 2020 at 9:45

AlwaysLearning

8,9425 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kris Clegg Over a year ago

Thanks @AlwaysLearning, I rushed putting together my example data. Sorry about that. The JSON_QUERY() wrapper was the missing secret sauce on this one. Thanks for the quick reply.

Collectives™ on Stack Overflow

Use SQL Server's FOR JSON function with both Numeric and JSON inputs

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related