4

I am trying to retrieve some SQL data from anonymous type. Different queries may be passed into the SqlQuery option so I am trying to retrieve the data as JSON and then parse that JSON later on. The problem I run into is the data is automatically split into a string array once it reaches I think 2kb, this means that the parse fails because the split creates invalid JSON. I am looking for a way to change this query either so it splits the data on each new row instead of randomly, or some way to keep it in one string so I can parse it to JSON, or some other way to parse the result to JSON data. Example Query

var data =_invoices.GetContext().Database().SqlQuery("Select * from " + tableName + " For JSON AUTO");
//throws error here because the data is split causing json to be invalid
var json = JArray.parse(data);

The result of this data is splits at a certain size rather than at end of row, which causes my JSON object to be invalid, how can I fix this and get my rows as a valid JSON object

Dale K
  • 25,246
  • 15
  • 42
  • 71
nightwolf555
  • 327
  • 1
  • 14
  • Try `SELECT CONVERT(NVARCHAR(MAX), (SELECT * FROM ... FOR JSON AUTO))`. (Disclaimer: not tested.) – Jeroen Mostert Dec 13 '21 at 20:44
  • this doesnt work for me – nightwolf555 Dec 13 '21 at 21:16
  • 1
    @nightwolf555, this is the expected [output of the FOR JSON clause](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15#output-of-the-for-json-clause): _The result set contains a single column. A small result set may contain a single row. A large result set splits the long JSON string across multiple rows._. Possible solutions are: 1) To fetch all rows from the result set and build the final JSON or 2) To generate JSON content for each row from the source table. – Zhorov Dec 14 '21 at 07:05
  • `DECLARE @json nvarchar(max) = (Select * from tableName For JSON AUTO); SELECT @JSON;` should do the trick. Side point: you have some nasty SQL injection going here, I hope you are whitelisting/sanitizing your input – Charlieface Dec 14 '21 at 17:52

0 Answers0