CODEWITHSUNDEEP
sqljsonsql-server
Kris Clegg
Kris Clegg

Reputation: 25

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

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"
                }
            ]
        }
    ]

Upvotes: 0

Views: 80

Answers (1)

AlwaysLearning
AlwaysLearning

Reputation: 8829

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.

Upvotes: 1

Related Questions