Json output format from SQL Server 2017

Question

I have a stored procedure in SQL Server 2017 that outputs a result set as JSON. It works to output the set just fine, but I want it in a different format.

According to the MS documentation, the format it's sending out is as expected, but it seems counter-intuitive to me. I'm not JSON expert by any means but I've always assumed it to be a single object representation of a structure.

The query is:

SELECT 
    e.EventID AS 'Event.ID',
    EventDate AS 'Event.Date',
    ea.ActivityID AS 'Event.Activity.ID',
    ea.CreateDate AS 'Event.Activity.Date',
    ea.Notes AS 'Event.Activity.Notes'
FROM
    Events e  
JOIN 
    dbo.EventActivities ea ON e.EventID = ea.EventID
FOR JSON PATH

This returns an output of:

[
    {"Event":  { 
                   "ID":"236",
                   "Date":"2019-03-01",
                   "Activity": {"ID": 10,
                                "Date":"2019-01-02T11:47:33.2800000",
                                "Notes":"Event created"}
               }
    },
    {"Event": {
                  "ID":"236",
                  "Date":"2019-03-01",
                  "Activity": {"ID":20,
                               "Date":"2019-01-02T11:47:34.3933333",
                               "Notes":"Staff selected"}
              }
    },
    {"Event": {
                  "ID":"236",
                  "Date":"2019-03-01",
                  "Activity": {"ID":20,
                              "Date":"2019-01-02T11:47:34.3933333",
                              "Notes":"Staff selected"}
              }
     }
]

When I format this manually (to visualise it better, it's giving me an array of 3 identical events, for each activity. This is consistent with what MS say in Format Nested JSON

I was expecting (or rather hoping) to see something like:

[
    {
        "Event": {
            "ID": "236",
            "Date": "2019-03-01",
            "Activity": [
                {
                    "ID": 10,
                    "Date": "2019-01-02T11:47:33.2800000",
                    "Notes": "Event created"
                },
                {
                    "ID": 20,
                    "Date": "2019-01-02T11:47:34.3933333",
                    "Notes": "Staff selected"
                },
                {
                    "ID": 20,
                    "Date": "2019-01-02T11:47:34.3933333",
                    "Notes": "Staff selected"
                }
            ]
        }
    }
]

Is it possible to get an output formulated like this? or would this be invalid?

Formating and spacing and indentation usually don't belong to the DB engine and are better left to whatever presentation layer your program uses (similarly to converting dates to text for display). For ad-hoc testing it's often enough to paste the output into a proper tool that does the format for you, even if it's somewhat inconvenient. — Alejandro
– Alejandro, Commented Jan 11, 2019 at 20:06

solutionist · Accepted Answer · 2019-01-11 21:35:12Z

To start, you can test if a JSON String is valid with ISJSON. The expected output you indicated does not pass validation, but is close. It is missing a "[]" for the inner array.

However, I see where you were going with this. To better explain what I think the issue you are running into is, I am going to beautify the format of the output JSON from your query to match your expected JSON.

Original output as follows:

[
    {"Event":
        {"ID":"236","Date":"2019-03-01",
            "Activity":{
                "ID":10,"Date":"2019-01-02T11:47:33.2800000","Notes":"Event created"
            }
        }
    },
    {"Event":
        {"ID":"236","Date":"2019-03-01",
            "Activity":{
                "ID":20,"Date":"2019-01-02T11:47:34.3933333","Notes":"Staff selected"}
        }
    },
    {"Event":
        {"ID":"236","Date":"2019-03-01",
            "Activity":{
                "ID":20,"Date":"2019-01-02T11:47:34.3933333","Notes":"Staff selected"
            }
        }
    }
]

Based on your ideal format, a possible valid JSON string would be as follows:

{"Event":
    [
        {"ID":236,"Date":"2019-03-01",
            "Activity":
                [
                    {"ID":10,"Date":"2019-01-02T11:47:33.2800000","Notes":"Event created"},
                    {"ID":20,"Date":"2019-01-02T11:47:34.3933333","Notes":"Staff selected"},
                    {"ID":20,"Date":"2019-01-02T11:47:34.3933333","Notes":"Staff selected"}
                ]
        }
    ]
}

You can achieve this by adjusting your table alias for the second table and using FOR JSON AUTO and ROOT. It will return an output with the "Event" attributes not repeated for each of its "EventActivities". For each "Event" it will put its related "EventActivities" into an array instead.

The following SQL will return the desired output:

SELECT [Event].EventID AS 'ID',
    [Event].EventDate AS 'Date',
    Activity.ActivityID AS 'ID',
    Activity.CreateDate AS 'Date',
    Activity.Notes AS 'Notes'
FROM #Events [Event]
JOIN #EventActivities Activity
    ON [Event].EventID = Activity.EventID
FOR JSON AUTO, ROOT('Event')

The exact output for this will be as follows:

{"Event":[{"ID":236,"Date":"2019-03-01","Activity":[{"ID":10,"Date":"2019-01-02T11:47:33.2800000Z","Notes":"Event created"},{"ID":20,"Date":"2019-01-02T11:47:33.2800000Z","Notes":"Staff selected"},{"ID":20,"Date":"2019-01-02T11:47:33.2800000Z","Notes":"Staff selected"}]}]}

It will not return in a beautified format, but spaces and indentation can be added without compromising the fact that it is valid JSON, while also achieving the intended array requirements.

Perfect solutionist, thank you. I'd read about IsJson but completely forgotten I could have proven it with that. The format you suggest is exactly what I was looking for. This is, in my opinion, a better solution than that offered by Hector Montero, purely because it doesn't use a correlated query. Thank you both for your help.

Hector Montero · Accepted Answer · 2019-01-11 21:23:15Z

0

Your expected output is not a valid JSON. But, assuming you are referring Activity field as an array of activities, you can use nested queries.

SELECT
    E.EventID AS 'Event.ID',
    E.EventDate AS 'Event.Date',
    (
        SELECT
            A.ActivityID AS 'ID',
            A.CreateDate AS 'Date',
            A.Notes AS 'Notes'
        FROM dbo.EventActivities AS A
        WHERE A.EventID = E.EventID
        FOR JSON PATH
    ) AS 'Event.Activities'
FROM dbo.Events AS E
FOR JSON PATH

answered Jan 11, 2019 at 21:23

Hector Montero

2211 silver badge5 bronze badges

Collectives™ on Stack Overflow

Json output format from SQL Server 2017

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related