CODEWITHSUNDEEP
sqlgroovystreaming
MFIhsan
MFIhsan

Reputation: 1057

How to stream SQL results to JSON using Groovy StreamingJsonBuilder?

I am trying to execute a SQL query and convert the results to JSON as follows. Though I got it working without streaming, I'm having some issues using StreamingJsonBuilder to stream the results.

non-streaming code

def writer = new StringWriter()
def jsonBuilder = new StreamingJsonBuilder(writer)
sql.eachRow("select * from client"){ row -> 
    jsonBuilder( id: row.id, name: row.name )
}
println writer.toString()

Result from the code above

{"id":123,"name":"ABCD"}{"id":124,"name":"NYU"}

The problem with this result is that, all documents are printed on same line without delimitation. How do I get the results as an array and each document pretty-printed as below

Expected result

[
   {
      id: 123,
      name: "ABCD",
      ...
   }, 
   {
      id: 124,
      name: "NYU",
      ...
   }, 
]

Upvotes: 4

Views: 2277

Answers (2)

cfrick
cfrick

Reputation: 37033

I put this here more as an fallback. If your problem is just to have your data properly formatted as json, but the sheer amount of data make you use the streaming API, then you are better off with using the streaming for your data and handle the "array" for yourself.

All the calls in the StreamingJsonBuilder take an object and directly write it to the writer. So there is no safe way (I can see) to have the writer open the array, then send the data in chunks you provide and then close the array. So while we already hold the writer, why not just deal with the array your self (this part of json is rather easy to get right):

new File('/tmp/out.json').withWriter{ writer ->
    writer << '['
    def jsonBuilder = new groovy.json.StreamingJsonBuilder(writer)
    def first = true
    10000000.times{
        if (!first) writer << "\n,"
        first = false
        jsonBuilder(id: it, name: it.toString())
    }
    writer << ']'
}

Upvotes: 4

Opal
Opal

Reputation: 84794

I've no access to any SQL to try but the following piece of code should do the job (You need to replace the data variable):

import groovy.json.*

def writer = new StringWriter()
def jsonBuilder = new StreamingJsonBuilder(writer)
def data = [
   [id:1, name: 'n1', other: 'o1'],
   [id:2, name: 'n2', other: 'o2']
]
def dataJson = jsonBuilder(data.collect { [id:it.id, name:it.name] })

println(JsonOutput.prettyPrint(JsonOutput.toJson(dataJson)))

UPDATE (after @cfrick's comment)

Here, every row is processed one ofter another but, a key (data in this case) is needed.

import groovy.json.*

def writer = new StringWriter()
def jsonBuilder = new StreamingJsonBuilder(writer)
def data = [
   [id:1, name: 'n1', other: 'o1'],
   [id:2, name: 'n2', other: 'o2']
]
def root = jsonBuilder(data: [])
data.each { d ->
    root.data << [id:d.id, name: d.name]
}

println(JsonOutput.prettyPrint(JsonOutput.toJson(root)))

Upvotes: 1

Related Questions