0

I want to convert the SQL query result as json using SQL statement with Python. This is the SQL command I am trying

select * 
from mydb 
for json auto

I also tried for json path.

And I also tried with without_array_wrapper but its returning only one row from the list.

If I run the query without for json auto|path, it's returning 8 rows, but when I run it with for json auto|path, I only get 4 rows (instead of 8), and its converting the 8 rows into 4 and returns an array of 4.

This is my Python code

cursor.execute(query)
result = cursor.fetchall()

Can somebody please help me figure out and fix this issue?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tstudent
  • 79
  • 1
  • 12
  • 3
    If you put `FOR JSON AUTO` at the end of a statement you will get 1 row, that's correct. Why would you expect more? Sample data and expected results will help us help you here. – Thom A Oct 27 '21 at 11:50
  • what is the error you get? – Milad Dastan Zand Oct 27 '21 at 12:24
  • @larnu thank you for your reply.. the data is huge it has 41 columns. so i could not paste it here. when i run query without for json path its returning 8 row and its working fine .. but when i run query with json path its converting 8 rows into 4 json objects. – tstudent Oct 27 '21 at 12:45
  • @MiladDastanZand thank you .... actually there is no error the problem is its converting 8 rows into 4 json object instead of 8. – tstudent Oct 27 '21 at 12:47
  • 1
    @tstudent , We just need a [mre], emphasis on **minimal**. – Thom A Oct 27 '21 at 12:49
  • 1
    Try `SELECT CAST((select * from mydb for json auto) AS nvarchar(max))` – Charlieface Oct 27 '21 at 13:23
  • Does this answer your question? [FOR JSON PATH results in SSMS truncated to 2033 characters](https://stackoverflow.com/questions/54973536/for-json-path-results-in-ssms-truncated-to-2033-characters) It's a known issue with `FOR JSON` – Charlieface Oct 27 '21 at 13:34
  • @Larnu Please mark this as duplicate – Charlieface Oct 27 '21 at 13:35
  • @Charlieface I can't hammer a question that is already closed. Nor can a hammer a question I have already made a VTC on. – Thom A Oct 27 '21 at 13:37
  • @Larnu Oh I thought a gold badge could change the VTC reason – Charlieface Oct 27 '21 at 13:39
  • Nope, @Charlieface , we can only unilaterally close and reopen as dupes, and (just like everyone else) we only get 1 VTC on a question. If i VTC as unclear, I can't then VTC as a dupe later on if we get clarrification. – Thom A Oct 27 '21 at 13:41

1 Answers1

2

Please try this solution .

Select cast ((select * from mydb for json auto) as nvarchar(max))
Thameem
  • 3,426
  • 4
  • 26
  • 32
  • Nothing to do with pyodbc, it's documented here https://stackoverflow.com/questions/54973536/for-json-path-results-in-ssms-truncated-to-2033-characters/54973676 – Charlieface Oct 27 '21 at 13:34