Ideally, you would replace the query between the two parentheses with your own query. SELECT chats that lead up to this can be found here. With the help of Max Vernon and Andriy M we have found a fairly simple solution to this problem. This means that only ~2000 characters can be sent per chunk. Sql Server splits result of FOR JSON query into ~2KB chunk, so you should either concatenate fragments like on the MSDN page or you can stream results into some output stream. 23 (X64)ĭeveloper Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) Īfter further research, I have discovered from this StackOverflow post that SQL Server breaks FOR JSON queries into "~2kb chunks". I am using the following version of SQL Server Microsoft SQL Server 2019. Obviously this is undesirable.Īfter investigation, I found that if I remove the LEFT JOIN's, then the query responds as it should (only one row with the entire string intact).īelow is an example of the following behavior: SELECT n = sc1.name FROM sys.syscolumns sc1 FOR JSON AUTOĪs can be seen above, 11 rows are being returned.Īnd the length of the JSON output is about 20,000 characters. The problem is: when I execute this query it responds with two rows where the resulting JSON string is split in two. LEFT JOIN users AS instructor ON course.instructor = instructor.id FOR JSON PATH, LEFT JOIN courses AS course ON urse = course.id LEFT JOIN users AS author ON thor = author.id Questions.quiz = quizzes.id FOR JSON PATH, INCLUDE_NULL_VALUES LEFT JOIN question_types ON question_types.id = questions.type LEFT JOIN users AS question_author ON question_author.id = thor Question_author.last_updated AS 'author.last_updated', Question_author.created_at AS 'author.created_at', Question_partment_name AS 'partment_name', Question_author.last_name AS 'author.last_name', Question_author.first_name AS 'author.first_name', Question_types.created_at AS 'type.created_at', Instructor.last_updated AS '_updated',Ĭourse.created_at AS 'course.created_at',Ĭourse.last_updated AS 'course.last_updated', I've found that I can use subqueries to populate a field (in this case the questions field) with a JSON string that that contains a list of questions.Īuthor.first_name AS 'author.first_name',Īpartment_name AS 'partment_name',Īuthor.created_at AS 'author.created_at',Īuthor.last_updated AS 'author.last_updated',Ĭscription AS 'scription', You’ll notice that much of this is a bit cumbersome, but I only use short JSON Strings and haven’t noticed a performance problem in doing this.I am trying to build a query that results in a JSON object generated by SQL Server. In this blog, I’ll take the whole process around the circle. I ran out of space in the article and thought it was really the sort of thing that readers of the article would want to experiment with. The first was how to read a JSON string as a table, and the other was how to produce a JSON document from a table. There were a few questions that I’d left unanswered. Also, an anonymous troll on StackOverflow had told me it was impossible. It came from a real requirement I had at the time, but I got interested in it in order to show how one could analyse hierarchical data documents iteratively in TSQL. I hadn’t really expected it to be so appreciated in fact I was nervous about posting it at all. One of the surprises that I got from writing for Simple-Talk was the popularity of my article Consuming JSON Strings in SQL Server.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |