Remove the object definition from json array that is within an json array keeping the nested array intact

Viewed 29

I have the following xml:

<root xmlns:json='http://james.newtonking.com/projects/json'>
   <majorDimension>ROWS</majorDimension>
   <range>Sheet6!A1:B2</range>
   <values json:Array='true'>
      <element json:Array='true'>
         <element>Good</element>
         <element>Value</element>
      </element>
      <element json:Array='true'>
         <element>Hello</element>
         <element>World</element>
      </element>
   </values>
</root>

and I'm using the following code:

var json = JsonConvert.SerializeXmlNode(xmlDoc, Newtonsoft.Json.Formatting.None, true);

The output that I am getting is like this

{
    "majorDimension": "ROWS",
    "range": "Sheet6!A1:B2",
    "values": [
                  { "element": [ [ "Good", "Value" ],
                                 [ "Hello", "World" ] ]
                  }
              ]
}

However, I would like get an output something like this

{
  "range": "Sheet6!A1:B2",
  "majorDimension": "ROWS",
  "values": [
    [
        "Good",
        "Value"
    ],
    [
        "Hello",
        "World"
    ]
  ]
}
2 Answers

Without knowing more details it's hard to offer a good solution. Should probably be able to deserialize into a well defined C# class and manipulate data from there. Or similar. But anyways.


First option, note the documentation

Multiple nodes with the same name at the same level are grouped together into an array.

https://www.newtonsoft.com/json/help/html/ConvertingJSONandXML.htm

So you can rename all element to values and get a nested array output (one more than request), but it's missing the { "element" : ... JSON. The "element" contents are all there though.


Second option, manipulate XML document before converting to JSON. This lifts each child element node to the same level in the tree as values. The only consideration is that the output array is called element instead of values.


using System;
using System.Xml;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

string xmlDoc = @"<root xmlns:json='http://james.newtonking.com/projects/json'>
   <majorDimension>ROWS</majorDimension>
   <range>Sheet6!A1:B2</range>
   <values json:Array='true'>
      <element json:Array='true'>
         <element>Good</element>
         <element>Value</element>
      </element>
      <element json:Array='true'>
         <element>Hello</element>
         <element>World</element>
      </element>
   </values>
</root>";

var xdoc = new XmlDocument();
xdoc.LoadXml(xmlDoc);
var valuesObject = xdoc.GetElementsByTagName("values")[0];
XmlNode parent = valuesObject.ParentNode;
while (valuesObject.ChildNodes.Count > 0)
{
    XmlNode child = valuesObject.ChildNodes[0];
    parent.InsertBefore(child, valuesObject);
}
parent.RemoveChild(valuesObject);

var json = JsonConvert.SerializeXmlNode(xdoc.ChildNodes[0], Newtonsoft.Json.Formatting.Indented, true);

Console.WriteLine(json);

output

{
  "majorDimension": "ROWS",
  "range": "Sheet6!A1:B2",
  "element": [
    [
      "Good",
      "Value"
    ],
    [
      "Hello",
      "World"
    ]
  ]
}

Third option, convert XML to JSON, serialize to jobject, manipulate the structure, deserialize to string.

using System;
using System.Xml;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

var xdoc = new XmlDocument();
xdoc.LoadXml(xmlDoc);

var jsonString = JsonConvert.SerializeXmlNode(xdoc.ChildNodes[0], Newtonsoft.Json.Formatting.Indented, true);
var jobj = JObject.Parse(jsonString);

var elements = jobj["values"][0]["element"];
// remove existing "values" node
jobj.Remove("values");
// add a new node called "values" with the contents of the "elements" array
jobj.Add("values", elements);

Console.WriteLine(jobj.ToString());

output

{
  "majorDimension": "ROWS",
  "range": "Sheet6!A1:B2",
  "values": [
    [
      "Good",
      "Value"
    ],
    [
      "Hello",
      "World"
    ]
  ]
}

Replace the "Newtonsoft.Json.Formatting.None" by "Newtonsoft.Json.Formatting.

var json = JsonConvert.SerializeXmlNode(xmlDoc, Newtonsoft.Json.Formatting.Indented, true);
Related