[groovy-user] How merge json objects

[citron]

Hi,

I need some help in modifying a JSON string.

JSON objects with the same Date should be merged in the following code
sample.

Thanks for any suggestions how to do this!

import groovy.json.JsonSlurper

def json ="""[{
"Date": " 12:05","HOST3": 5,"HOST1": 5,"HOST2": 5},{
"Date": " 12:05","COMPLETED": 5},{
"Date": " 12:06","HOST3": 6,"HOST1": 6,"HOST2": 6},{
"Date": " 12:06","COMPLETED": 6},{
"Date": " 12:07","HOST3": 7,"HOST1": 7,"HOST2": 7},{
"Date": " 12:07","COMPLETED": 7}
]"""

def slurper = new JsonSlurper()
def result = slurper.parseText(json)

result.each{
println it.toString()
}

*CURRENT RESULT*
-------------------------------
[Date: 12:05, HOST2:5, HOST3:5, HOST1:5]
[Date: 12:05, COMPLETED:5]
[Date: 12:06, HOST2:6, HOST3:6, HOST1:6]
[Date: 12:06, COMPLETED:6]
[Date: 12:07, HOST2:7, HOST3:7, HOST1:7]
[Date: 12:07, COMPLETED:7]
--------------------------------

*DESIRED RESULT*
-------------------------------
[Date: 12:05, HOST2:5, HOST3:5, HOST1:5, COMPLETED:5]
[Date: 12:06, HOST2:6, HOST3:6, HOST1:6, COMPLETED:6]
[Date: 12:07, HOST2:7, HOST3:7, HOST1:7, COMPLETED:7]
-------------------------------



-----
Project pages
AndersTool
earBuddy
EMSMonster
--
View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335.html
Sent from the groovy - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Tim Yates]

Assuming they are always sorted so they are adjacent, and there is always a pair, you could do:

result.collate( 2 ).collect { a, b -> a << b }.each{

    println it.toString()

}

[Tim Yates]

If they aren't always next to each other, or in pairs, you can group them by the shared key (Date), and then collapse each list of maps into a single one with collectEntries:

result.groupBy { it.Date }.collect { key, value -> value.collectEntries() }.each {

    println it.toString()

}

[citron]

Perfect!

This will do the trick.

result.groupBy { it.Date }.collect { key, value -> value.collectEntries()
}.each {
println it.toString()
}

Question now is, what would be the best way of making a json string out of
the result.

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5717338.html

[Tim Yates]

Try:

String output = new JsonBuilder( result.groupBy { it.Date }

                                       .collect { key, value ->

                                           value.collectEntries()

                                       } ).toString()

[citron]

Very nice!!

Don't know why a space is prepended to the Date value, how to remove it?

RESULT
[{"Date":*" 1*2:05","HOST2":5,"HOST3":5,"HOST1":5,"COMPLETED":5},{"Date":*"
1*2:06","HOST2":6,"HOST3":6,"HOST1":6,"COMPLETED":6},{"Date":"
12:07","HOST2":7,"HOST3":7,"HOST1":7,"COMPLETED":7}]

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5717343.html

[Tim Yates]

The space is in the input data, so carries through to the result

[citron]

ehm.. that's kind of embarrassing... well, problem solved!

Many thanks for your help Tim!!


For anyone interested, here is the complete code example


import groovy.json.JsonSlurper
import groovy.json.JsonBuilder

def json ="""[{
"Date": "12:05","HOST3": 5,"HOST1": 5,"HOST2": 5},{
"Date": "12:05","COMPLETED": 5},{
"Date": "12:06","HOST3": 6,"HOST1": 6,"HOST2": 6},{

"Date": "12:06","COMPLETED": 6},{
"Date": "12:07","HOST3": 7,"HOST1": 7,"HOST2": 7},{

"Date": "12:07","COMPLETED": 7}
]"""

def slurper = new JsonSlurper()
def result = slurper.parseText(json)

String output = new JsonBuilder( result.groupBy { it.Date }
.collect { key, value ->
value.collectEntries()
} ).toString()

println output

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5717347.html

[Dinko Srkoč]

Just a small and probably insignificant note...

On 29 October 2013 22:00, citron <viklund...@hotmail.com> wrote:

[...]

String output = new JsonBuilder( result.groupBy { it.Date }
                               .collect { key, value ->
                                   value.collectEntries()
                               } ).toString()

Since `output` is declared as being of type String, explicit conversion to String ( `new JsonBuilder(...).toString()` ) is  probably not needed. So:

    String output = new JsonBuilder(...)

should suffice.

Cheers,

Dinko

[citron]

What if its important to keep the order of the elements after all?

*collectEntries* does not seem to keep element sequence intact.

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5717425.html

[Tim Yates]

Order shouldn't matter other than in a List as it's just an object with properties.

However can you give an example of when the order is wrong, and the output you are expecting?

[citron]

I've created a combined chart using JChartFX and feeding data to it with a
JSON object.
The element sequence is very important in this particular case.

//---------------------------------------------------------
import groovy.json.JsonBuilder
import groovy.json.JsonSlurper
import groovy.json.JsonOutput

String json = """[{
"Date": "2013-11-12 09:58",
"HIGH": 10,
"LOW": 0,
"AVG": 5,
"COMPLETED": 100,
"ABORTED": 0
}
,{
"Date": "2013-11-12 09:59",
"HIGH": 10,
"LOW": 0,
"AVG": 5,
"COMPLETED": 100,
"ABORTED": 0
}
,{
"Date": "2013-11-12 10:00",
"HIGH": 10,
"LOW": 0,
"AVG": 5,
"COMPLETED": 100,
"ABORTED": 0
},{
"Date": "2013-11-12 09:58",
"MY-HOST": 62
}
,{
"Date": "2013-11-12 09:59",
"MY-HOST": 57
}
,{
"Date": "2013-11-12 10:00",
"MY-HOST": 55
}]""".toString()


String mergedJSON = new JsonBuilder( new JsonSlurper().parseText(json)

.groupBy { it.Date }
.collect { key, value ->
value.collectEntries()
} ).toString()

println new JsonOutput().prettyPrint(mergedJSON)

*OUTPUT*
[
{
*"ABORTED": 0,*
"Date": "2013-11-12 09:58",
"HIGH": 10,
"LOW": 0,
"AVG": 5,
"COMPLETED": 100,
"MY-HOST": 62
},
{
*"ABORTED": 0,*
"Date": "2013-11-12 09:59",
"HIGH": 10,
"LOW": 0,
"AVG": 5,
"COMPLETED": 100,
"MY-HOST": 57
},
{
*"ABORTED": 0,*
"Date": "2013-11-12 10:00",
"HIGH": 10,
"LOW": 0,
"AVG": 5,
"COMPLETED": 100,
"MY-HOST": 55
}
]


*DESIRED OUTPUT*
[
{
"Date": "2013-11-12 09:58",
"HIGH": 10,
"LOW": 0,
"AVG": 5,
"COMPLETED": 100,
*"ABORTED": 0, *
"MY-HOST": 62
},
{
"Date": "2013-11-12 09:59",
"HIGH": 10,
"LOW": 0,
"AVG": 5,
"COMPLETED": 100,
*"ABORTED": 0, *
"MY-HOST": 57
},
{
"Date": "2013-11-12 10:00",
"HIGH": 10,
"LOW": 0,
"AVG": 5,
"COMPLETED": 100,
*"ABORTED": 0,*
"MY-HOST": 55
}
]

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5717429.html

[Tim Yates]

It's actually the `groupBy` that's changing the order, I'll try and think of a solution...

Out of interest, why is the order important?

[Tim Yates]

After a bit more digging, it's actually not `groupBy` that's to blame, it's the `parseText` call

println new JsonSlurper().parseText(json)

shows ABORTED is first in the resulting Maps.

JsonSlurper uses a HashMap internally (not a LinkedHashMap) as order doesn't matter according to the Json specification

So if you have to have a specified order for some reason, then you'll need to sort the maps after the grouping:

def order = [ 'Date':0,

              'HIGH':1,

              'LOW':2,

              'AVG':3,

              'COMPLETED':4,

              'ABORTED':5,

              'MY-HOST':6 ].withDefault { -1 }

String mergedJSON = new JsonBuilder( new JsonSlurper().parseText(json)

                        .groupBy { it.Date }

                        .collect { key, value ->

                           value.collectEntries().sort { order[ it.key ] }

                        } ).toString()

[citron]

This information not really related to Groovy, its just to give a better
understanding of the problem.

*JSON sample*
sampleJSON.txt
<http://groovy.329449.n5.nabble.com/file/n5717433/sampleJSON.txt>

*Java Script Sample*
sampleJavaScriptFunction.txt
<http://groovy.329449.n5.nabble.com/file/n5717433/sampleJavaScriptFunction.txt>

*More info about JChartFX*
Passing Data using JSON
<http://support.softwarefx.com/jChartFX/article/2501027#fb9ec7bf-1b86-e211-84a5-0019b9e6b500>

Because of the wrong sequence of elements in the JSON passed to the chart,
the time axis is reversed, and the *ABORTED/AVG* series ends up in wrong
chart in sample the screenshot below .

Perhaps it is possible to get the series by name instead of order in element
sequence *chart1.getSeries().getItem(N)*, because apparently the chart is
smart enough to recognize the "Date" element, despite not being the first
element as in other samples from their site. This is still to be looked
into.

*Screenshot*
<http://groovy.329449.n5.nabble.com/file/n5717433/Statistics_-_Mozilla_Firefox_2013-11-12_11-35-53.png>

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5717433.html

[Tim Yates]

Erk, I see what you mean...  They do seem to rely on the order of properties in a Json object.

This goes against the Json specification (http://www.json.org/) which states

An object is an unordered set of name/value pairs

But hopefully the sort method above will help you get the data into the format you require, and get it past their hokey way of doing things ;-)

Tim

PS:

Probably worth changing:

def order = [ 'Date':0,

              'HIGH':1,

              'LOW':2,

              'AVG':3,

              'COMPLETED':4,

              'ABORTED':5,

              'MY-HOST':6 ].withDefault { -1 }

to

def order = [ 'Date':0,

              'HIGH':1,

              'LOW':2,

              'AVG':3,

              'COMPLETED':4,

              'ABORTED':5,

              'MY-HOST':6 ].withDefault { 9999 }

So unknown items go at the end :-)

[citron]

Many thanks for your help, *its working great so far*, except for one thing..


The way JChartFX works is that it only checks the first object in the
collection, properties(series) missing there will not be rendered in the
chart.

Example

*A JSON that looks like this...*
[
{
"Date": "2013-11-11 00:00",
"MY-VAIO": 45
},
{
"Date": "2013-11-12 00:00",
"MY-VAIO": 65
},
{
"Date": "2013-11-13 00:00",
"AVG": 5261,
"LOW": 3043,
"HIGH": 10265,
"COMPLETED": 857,
"ABORTED": 0,
"MY-VAIO": 59
}
]

*.... needs to be modified into this*
[
{
"Date": "2013-11-11 00:00",
* "AVG": 0,
"LOW": 0,
"HIGH": 0,
"COMPLETED": 0,
"ABORTED": 0,*
"MY-HOST": 45
},
{
"Date": "2013-11-12 00:00",
"MY-HOST": 65
},
{
"Date": "2013-11-13 00:00",
"AVG": 5261,
"LOW": 3043,
"HIGH": 10265,
"COMPLETED": 857,
"ABORTED": 0,
"MY-HOST": 59
}
]


The question is, how collect all properties in the JSON and make sure that
they are present in the first object?

Thanks!

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5717449.html

[Tim Yates]

And MY-VAIO has to be transformed into MY-HOST?

[Tim Yates]

And should the "Date": "2013-11-12 00:00", entry be getting default values as well?

[Tim Yates]

Assuming MY-VAIO was a typo, and the Date thing was as well, you *could* try something like this:

// A map showing the order of the elements, and the value if they are missing

def order = [ 'Date'     :[ order:0 ],

              'HIGH'     :[ order:1, missing:0 ],

              'LOW'      :[ order:2, missing:0 ],

              'AVG'      :[ order:3, missing:0 ],

              'COMPLETED':[ order:4, missing:0 ],

              'ABORTED'  :[ order:5, missing:0 ],

              'MY-HOST'  :[ order:6, missing:999 ] ].withDefault { [order:999,missing:0] }

String mergedJSON = new JsonBuilder( new JsonSlurper().parseText(json)

                        .groupBy { it.Date }

                        .collect { key, value ->

                           def rslt = value.collectEntries()

                           rslt << ( order.keySet() - rslt.keySet() ).collectEntries {

                               [ it, order[ it ].missing ]

                           }

                           rslt.sort { order[ it.key ].order }

                        } ).toString()

Tim

[Dinko Srkoč]

Perhaps a slightly simpler implementation, building on Tim’s previous solution:

def order = [ 'Date': 0,
'HIGH': 1,
'LOW': 2,
'AVG': 3,
'COMPLETED': 4,
'ABORTED': 5,
'MY-HOST': 6 ].withDefault { 9999 }

def defaults = order.collectEntries { [it.key, 0] }

String mergedJSON = new JsonBuilder(new JsonSlurper().parseText(json)

.groupBy { it.Date }
.collect { key, value ->

value.collectEntries(defaults).sort { order[it.key] }
})

Cheers,
Dinko

[Tim Yates]

*sigh*

I always forget about the collector on collectEntries

:-)

[citron]

Excellent!!

Is it some how possible to only populate the first object with the missing
properties?

This solution populates all objects in the JSON.

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5717455.html

[Tim Yates]

Not sure I understand the requirement...  You only want defaults in the first one?

Was the answer to:

And should the "Date": "2013-11-12 00:00", entry be getting default values as well?

"No"? 

;-)

[citron]

"You only want defaults in the first one?"

-Yes, since all series to be drawn in the chart needs to be present in the
first object.

A missing value for any of the series can be anything, and not just assumed
to be 0.
If the property(series) is not present in the first object in the JSON, then
the series will not be drawn at all.
I want the series to be visible on the chart even if a few values are
missing here and there.

Here is some more info from the guys at JChart regarding this:
/"We only check the first object in the collection, you will have to
"preprocess" the data before you pass it to jChartFX..."/
http://community.jchartfx.com/forums/t/197.aspx

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5717458.html

[citron]

Hi,

I put this aside for a while and now I need to pick it up again.

*This code:*

import groovy.json.JsonSlurper
import groovy.json.JsonBuilder
import groovy.json.JsonOutput

def json = """[
{
"Date": "2014-05-14 16:45",
"AVG": 6,
"COMPLETED": 10,
"ABORTED": 0
},
{
"Date": "2014-05-14 16:51",
"AVG": 4,
"COMPLETED": 10,
"ABORTED": 0
},
{
"Date": "2014-05-14 16:42",
"WDEV01": 47
},
{
"Date": "2014-05-14 16:43",
"WDEV01": 47
},
{
"Date": "2014-05-14 16:44",
"WDEV01": 47
},
{
"Date": "2014-05-14 16:45",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:46",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:47",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:48",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:49",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:50",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:51",
"WDEV01": 51
}
]"""

def order = [ 'Date':0,

'AVG':1,
'COMPLETED':2,
'ABORTED':3].withDefault { 9999 }

String mergedJSON = new JsonBuilder( new JsonSlurper().parseText(json)

.sort { it.Date }

.groupBy { it.Date }
.collect { key, value ->

value.collectEntries().sort { order[
it.key ] }
} ).toString()


println new JsonOutput().prettyPrint(mergedJSON)

*.. currently produces this output:*

[
{
"Date": "2014-05-14 16:42",
"WDEV01": 47
},
{
"Date": "2014-05-14 16:43",
"WDEV01": 47
},
{
"Date": "2014-05-14 16:44",
"WDEV01": 47
},
{
"Date": "2014-05-14 16:45",
"AVG": 6,
"COMPLETED": 10,
"ABORTED": 0,
"WDEV01": 49
},
{
"Date": "2014-05-14 16:46",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:47",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:48",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:49",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:50",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:51",
"AVG": 4,
"COMPLETED": 10,
"ABORTED": 0,
"WDEV01": 51
}
]



*The code needs to be modified to make sure that all elements are present in
the first object:*

[
{
"Date": "2014-05-14 16:42",
* "AVG": 0,
"COMPLETED": 0,
"ABORTED": 0,*
"WDEV01": 47
},
{
"Date": "2014-05-14 16:43",
"WDEV01": 47
},
{
"Date": "2014-05-14 16:44",
"WDEV01": 47
},
{
"Date": "2014-05-14 16:45",
"AVG": 6,
"COMPLETED": 10,
"ABORTED": 0,
"WDEV01": 49
},
{
"Date": "2014-05-14 16:46",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:47",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:48",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:49",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:50",
"WDEV01": 49
},
{
"Date": "2014-05-14 16:51",
"AVG": 4,
"COMPLETED": 10,
"ABORTED": 0,
"WDEV01": 51
}
]



Actually I am a bit clueless how to achieve this.

Any ideas?

Thanks!

-----
Project pages
AndersTool
earBuddy
EMSMonster
--

View this message in context: http://groovy.329449.n5.nabble.com/How-merge-json-objects-tp5717335p5719616.html

[Dinko Srkoč]

Why not insert a dummy entry with all the required fields and ensure to be the first element in the list after sort. Then merge it with the second one:

import groovy.json.JsonSlurper
import groovy.json.JsonBuilder
import groovy.json.JsonOutput

def json = """[ here comes the JSON string ] """

def order = [ 'Date':0, 'AVG':1, 'COMPLETED':2, 'ABORTED':3].withDefault { 9999 }

def parsed = new JsonSlurper().parseText(json)

// insert dummy entry and sort
// Date value ensures this entry comes first after sort
def dummy = [Date: -1, AVG: 0, COMPLETED: 0, ABORTED: 0]
def sorted = (parsed + dummy).sort { it.Date }

// merge 1st and 2nd entry
def merged = [sorted.take(2).collectEntries()] + sorted.drop(2)

// the rest is business as usual
def processed = merged.groupBy { it.Date }.collect { key, value ->
   value.collectEntries().sort { order[it.key] }
}

String mergedJSON = new JsonBuilder( processed ).toString()

println new JsonOutput().prettyPrint(mergedJSON)

Cheers,
Dinko

[Robert Stagner]

Dinko,

I get an error when I try to run the above script

Caught: groovy.json.JsonException: Unable to determine the current character, it is not a string, number, array, or object

The current character read is 'h' with an int value of 104

Unable to determine the current character, it is not a string, number, array, or object

line number 1

index number 2

[ here comes the JSON string ] 

..^

groovy.json.JsonException: Unable to determine the current character, it is not a string, number, array, or object

The current character read is 'h' with an int value of 104

Unable to determine the current character, it is not a string, number, array, or object

line number 1

index number 2

[ here comes the JSON string ] 

..^

        at ExampleOfMergingJSON.run(ExampleOfMergingJSON.groovy:9)

--

Regards,
Robert

• The human race has one really effective weapon, and that is laughter.
  
• If you tell the truth, you don't have to remember anything.
  
• Whenever you find yourself on the side of the majority, it is time to pause and reflect.
  

                -- Mark Twain

[Dinko Srkoč]

On 15 May 2014 18:20, Robert Stagner <rest...@gmail.com> wrote:

Dinko,

I get an error when I try to run the above script

Caught: groovy.json.JsonException: Unable to determine the current character, it is not a string, number, array, or object

The current character read is 'h' with an int value of 104

Unable to determine the current character, it is not a string, number, array, or object

line number 1

index number 2

[ here comes the JSON string ] 

..^

groovy.json.JsonException: Unable to determine the current character, it is not a string, number, array, or object 

"here comes the JSON string" was meant to be a placeholder for the actual JSON string from the citron's example. I thought I could leave that 

out as it is quite long.

Instead of my `def json = """[ here comes the JSON string ] """` put `def json = """ [{ "Date": ...` from the post that I replied to.

Sorry for the confusion.

Cheers,

Dinko