Question

Source:https://stackoverflow.com/questions/66955052/how-to-modify-values-of-jsonobject

I want to add a new field to jsonObject and this new field's name will be based on a value of another field. To be clear, this is an example of what I want to achieve.

{

"values": [

{

"id": "1",

"properties": [

{

"stat": "memory",

"data": 8

},

{

"stat": "cpu",

"data": 4

}

]

},

{

"id": "2",

"properties": [

{

"stat": "status",

"data": "OK"

},

{

"stat": "cpu",

"data": 4

}

]

}

]

}

I want to add a new field to each JSON object that will have the value of field "stat" as name.

{

"values": [

{

"id": "1",

"properties": [

{

"stat": "memory",

"data": 8,

"memory": 8

},

{

"stat": "cpu",

"data": 4,

"cpu": 4

}

]

},

{

"id": "2",

"properties": [

{

"stat": "status",

"data": 0,

"status": 0

},

{

"stat": "cpu",

"data": 4,

"cpu": 4

}

]

}

]

}

I have tried to do the following with JSONPath library but for me it's an ugly solution as I will parse the JSON three times and I do some manual replacements.

val configuration = Configuration.builder().options(Option.DEFAULT_PATH_LEAF_TO_NULL, Option.ALWAYS_RETURN_LIST).build()

val jsonContext5 = JsonPath.using(configuration).parse(jsonStr)

val listData = jsonContext.read("$['values'][*]['properties'][*]['data']").toString

.replace("[", "").replace("]","").split(",").toList

val listStat = jsonContext.read("$['values'][*]['properties'][*]['stat']").toString

.replace("[", "").replace("]","")

.replace("\"","").split(",").toList

// Replacing values of "stat" by values of "data"

jsonContext5.map("$['values'][*]['properties'][*]['stat']", new MapFunction() {

var count = - 1

override def map(currentValue: Any, configuration: Configuration): AnyRef = {

count += 1

listData(count)

}

})

// replace field stat by its value

for(count <- 0 to listStat.size - 1){

val path = s"['values'][*]['properties'][$count]"

jsonContext5.renameKey(path, "stat", s"${listStat(count)}")

}

This is the result obtained:

{

"values": [

{

"id": "1",

"properties": [

{

"data": 8,

"memory": "8"

},

{

"data": 4,

"cpu": "4"

}

]

},

{

"id": "2",

"properties": [

{

"data": 0,

"memory": "0"

},

{

"data": 4,

"cpu": "4"

}

]

}

]

}

Is there any better method to achieve this result? I tried to do it with Gson but it's not good at handling paths.

This is a way to do it with Gson, but I will lose the information about other columns since I'm creating another JSON.

val jsonArray = jsonObject.get("properties").getAsJsonArray

val iter = jsonArray.iterator()

val agreedJson = new JsonArray()

while(iter.hasNext) {

val json = iter.next().getAsJsonObject

agreedJson.add(replaceCols(json))

}

def replaceCols(json: JsonObject) = {

val fieldName = "stat"

if(json.has(fieldName)) {

val columnName = json.get(fieldName).getAsString

val value: String = if (json.has("data")) json.get("data").getAsString else ""

json.addProperty(columnName, value)

}

json

}

Answer

Your task is to add a new field to each record under each properties in the JSON file, make the current stat value the field name and data values the new field values. The code will be rather long if you try to do it in Java.

Suggest you using SPL, an open-source Java package to get it done. Coding will be very easy and you only need one line:

	A
1	=json(json(file("data.json").read()).values.run(properties=properties.(([["stat","data"]|stat]|[~.array()|data]).record())))

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as addfield.splx and invoke it in a Java application as you call a stored procedure:

…

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

st = con.prepareCall("call addfield()");
st.execute();

…

View SPL source code.