I wrote an article recently on the JSON_OBJECTAGG function, but neglected to include an example of why it can behave weirdly. This post looks at something you might not realize unless you remember this is an aggregate function.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
A Strange Result
I have some data, which I show below. Some teams, cities, and years.
If I ask for this data in a JSON_OBJECTAGG query, I get one document back. Which is fine. You can see this below
However, if I were to add the year separately as a column, I get an error without a GROUP BY. This shows me this is an aggregate, which I read and knew, but wasn’t thinking about in my testing:
If I add a GROUP BY, then I see these results. Notice that I get documents for each section of the GROUP BY with just that data. Each of these documents is 1 team, except for the 1960 year, where three teams started.
If you are using this to assemble simple JSON documents with a WHERE clause, you might not realize this is an aggregate and behaves that way.
Make sure you test your code in a few ways and treat this function as an aggregate.
SQL New Blogger
This post took a little longer to write (20 minutes). I took the code from my previous article, but I restructured a few things once I realized how this works as an aggregate. It’s a simple look at the function, but it also points out something I learned, which isn’t necessarily obvious. Talking about that shows you have some knowledge besides just copy/pasting code to create JSON documents.
You could do this as well, and leverage the idea of building off one post to show something in another.
