Why do you want to convert the records to JSON? Does it have to be SQL?

You could read the table with the DataFrame APIs and write a JSON file out. Please note that "coalesce" is only there so that it produces a single file. It would be something like this:

df = spark.read.table("YOUR TABLE") 
 
df.coalesce(1).write.format('json').save("/path/to/file")

Also, can you show your example with the to_json() function?