Keep the order of the JSON keys during JSON conversion to CSV

Question

I am using the JSON library provided here http://www.json.org/java/index.html to convert a json string I have to CSV. But the problem I have is, the order of the keys is lost after conversion.

This is the conversion code:

    JSONObject jo = new JSONObject(someString);
    JSONArray ja = jo.getJSONArray("items");
    String s = CDL.toString(ja);
    System.out.println(s);

This is the content of "someString":

{
    "items":
    [
        {
            "WR":"qwe",
            "QU":"asd",
            "QA":"end",
            "WO":"hasd",
            "NO":"qwer"
        },
    ]
}

This is the result:

WO,QU,WR,QA,NO
hasd,asd,qwe,end,qwer

While what I expect is to keep the order of the keys:

WR,QU,QA,WO,NO
qwe,asd,end,hasd,qwer

Is there any way I can have this result using this library? If not, is there any other library that will provide the capability to keep the order of keys in the result?

Answer 1

There are (hacky) ways to do it ... but you shouldn't.

In JSON, an object is defined thus:

An object is an unordered set of name/value pairs.

See http://json.org.

Most implementations of JSON make no effort to preserve the order of an object's name/value pairs, since it is (by definition) not significant.

If you want order to be preserved, you need to redefine your data structure; e.g.

{
    "items":
    [
        [
            {"WR":"qwe"},
            {"QU":"asd"},
            {"QA":"end"},
            {"WO":"hasd"},
            {"NO":"qwer"}
        ],
    ]
}

or more simply:

{
    "items":
    [
        {"WR":"qwe"},
        {"QU":"asd"},
        {"QA":"end"},
        {"WO":"hasd"},
        {"NO":"qwer"}
    ]
}

FOLLOWUP

Thanks for the info, but I have no choice but to use JSON in my application and my application needs to keep the order of the keys regardless of the definition of JSON object... I am not allowed to change the format of the JSON file as well...

You need to have a hard conversation with whoever designed that file structure and won't let you change it. It is / they are plain wrong. You need to convince them.

If they really won't let you change it:

• You should insist on not calling it JSON ... 'cos it isn't.
• You should point out that you are going to have to write / modify code specially to handle this "not JSON" format ... unless you can find some JSON implementation that preserves the order. If they are a paying client, make sure that they pay for this extra work you have to do.
• You should point out that if the "not JSON" needs to be used by some other tool, it is going to be problematic. Indeed, this problem will occur over and over ...

This kind of thing as really bad. On the one hand, your software will be violating a well established / long standing specification that is designed to promote interoperability. On the other hand, the nit-wits who designed this lame (not JSON!) file format are probably slagging off other people's systems etc 'cos the systems cannot cope with their nonsense.

UPDATE

It is also worth reading what the JSON RFC (RFC 7159) says on this subject. Here are some excerpts:

In the years since the publication of RFC 4627, JSON has found very wide use. This experience has revealed certain patterns, which, while allowed by its specifications, have caused interoperability problems.

JavaScript Object Notation (JSON) is a text format for the serialization of structured data. ...

JSON can represent four primitive types (strings, numbers, booleans, and null) and two structured types (objects and arrays).

An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array.

JSON parsing libraries have been observed to differ as to whether or not they make the ordering of object members visible to calling software. Implementations whose behavior does not depend on member ordering will be interoperable in the sense that they will not be affected by these differences.

Answer 2

It is quite simple to maintain order. I had the same problem with maintaining the order from DB layer to UI Layer.

Open JSONObject.java file. It internally uses HashMap which doesn't maintain the order.

Change it to LinkedHashMap:

    //this.map = new HashMap();
    this.map = new LinkedHashMap();

This worked for me. Let me know in the comments. I suggest the JSON library itself should have another JSONObject class which maintains order, like JSONOrderdObject.java. I am very poor in choosing the names.

Answer 3

JSONObject.java takes whatever map you pass. It may be LinkedHashMap or TreeMap and it will take hashmap only when the map is null .

Here is the constructor of JSONObject.java class that will do the checking of map.

 public JSONObject(Map paramMap)
  {
    this.map = (paramMap == null ? new HashMap() : paramMap);
  }

So before building a json object construct LinkedHashMap and then pass it to the constructor like this ,

LinkedHashMap<String, String> jsonOrderedMap = new LinkedHashMap<String, String>();

jsonOrderedMap.put("1","red");
jsonOrderedMap.put("2","blue");
jsonOrderedMap.put("3","green");

JSONObject orderedJson = new JSONObject(jsonOrderedMap);

JSONArray jsonArray = new JSONArray(Arrays.asList(orderedJson));

System.out.println("Ordered JSON Fianl CSV :: "+CDL.toString(jsonArray));

So there is no need to change the JSONObject.java class . Hope it helps somebody .

Answer 4

Another hacky solution using reflect:

JSONObject json = new JSONObject();
Field map = json.getClass().getDeclaredField("map");
map.setAccessible(true);//because the field is private final...
map.set(json, new LinkedHashMap<>());
map.setAccessible(false);//return flag

Answer 5

A more verbose, but broadly applicable solution to this sort of problem is to use a pair of data structures: a list to contain the ordering, and a map to contain the relations.

For Example:

{
    "items":
    [
        {
            "WR":"qwe",
            "QU":"asd",
            "QA":"end",
            "WO":"hasd",
            "NO":"qwer"
        },
    ],
    "itemOrder":
        ["WR", "QU", "QA", "WO", "NO"]
}

You iterate the itemOrder list, and use those to look up the map values. Ordering is preserved, with no kludges.

I have used this method many times.

Answer 6

Apache Wink has OrderedJSONObject. It keeps the order while parsing the String.

Answer 7

Just stumbled upon the same problem, I believe the final solution used by the author consisted in using a custom ContainerFactory:

public static Values parseJSONToMap(String msgData) {
    JSONParser parser = new JSONParser();
    ContainerFactory containerFactory = new ContainerFactory(){
        @Override
        public Map createObjectContainer() {
            return new LinkedHashMap();
        }

        @Override
        public List creatArrayContainer() {
            return null;
        }
    };
    try {
        return (Map<String,Object>)parser.parse(msgData, containerFactory);
    } catch (ParseException e) {
        log.warn("Exception parsing JSON string {}", msgData, e);
    }
    return null;
}  

see http://juliusdavies.ca/json-simple-1.1.1-javadocs/org/json/simple/parser/JSONParser.html#parse(java.io.Reader,org.json.simple.parser.ContainerFactory)

Answer 8

Solved.

I used the JSON.simple library from here https://code.google.com/p/json-simple/ to read the JSON string to keep the order of keys and use JavaCSV library from here http://sourceforge.net/projects/javacsv/ to convert to CSV format.

Answer 9

In the real world, an application will almost always have java bean or domain that is to be serialized/de-serialized to/from JSON. Its already mentioned that JSON Object specification does not guarantee order and any manipulation to that behavior does not justify the requirement. I had the same scenario in my application where I needed to preserve order just for the sack of readability purpose. I used standard jackson way to serialize my java bean to JSON:

Object object = getObject();  //the source java bean that needs conversion
String jsonString = new com.fasterxml.jackson.databind.ObjectMapper().writeValueAsString(object);

In order to make the json with an ordered set of elements I just use JSON property annotation in the the Java bean I used for conversion. An example below:

@JsonInclude(JsonInclude.Include.NON_NULL)
@JsonPropertyOrder({"name","phone","city","id"})
public class SampleBean implements Serializable {
    private int id;
    private String name:
    private String city;
    private String phone;

    //...standard getters and setters
}

the getObject() used above:

public SampleBean getObject(){
    SampleBean bean  = new SampleBean();
    bean.setId("100");
    bean.setName("SomeName");
    bean.setCity("SomeCity");
    bean.setPhone("1234567890");
    return bean;
}

The output shows as per Json property order annotation:

{
    name: "SomeName",
    phone: "1234567890",
    city: "SomeCity",
    id: 100
}

Answer 10

I know this is solved and the question was asked long time ago, but as I'm dealing with a similar problem, I would like to give a totally different approach to this:

For arrays it says "An array is an ordered collection of values." at http://www.json.org/ - but objects ("An object is an unordered set of name/value pairs.") aren't ordered.

I wonder why that object is in an array - that implies an order that's not there.

{
"items":
[
    {
        "WR":"qwe",
        "QU":"asd",
        "QA":"end",
        "WO":"hasd",
        "NO":"qwer"
    },
]
}

So a solution would be to put the keys in a "real" array and add the data as objects to each key like this:

{
"items":
[
    {"WR": {"data": "qwe"}},
    {"QU": {"data": "asd"}},
    {"QA": {"data": "end"}},
    {"WO": {"data": "hasd"}},
    {"NO": {"data": "qwer"}}
]
}

So this is an approach that tries to rethink the original modelling and its intent. But I haven't tested (and I wonder) if all involved tools would preserve the order of that original JSON array.

Answer 11

Underscore-java keeps orders for elements while reading json.

String json = "{\n"
      + "    \"items\":\n"
      + "    [\n"
      + "        {\n"
      + "            \"WR\":\"qwe\",\n"
      + "            \"QU\":\"asd\",\n"
      + "            \"QA\":\"end\",\n"
      + "            \"WO\":\"hasd\",\n"
      + "            \"NO\":\"qwer\"\n"
      + "        }\n"
      + "    ]\n"
      + "}";
System.out.println(U.fromJson(json));

// {items=[{WR=qwe, QU=asd, QA=end, WO=hasd, NO=qwer}]}

Answer 12

patchFor(answer @gary) :

$ git diff JSONObject.java                                                         
diff --git a/JSONObject.java b/JSONObject.java
index e28c9cd..e12b7a0 100755
--- a/JSONObject.java
+++ b/JSONObject.java
@@ -32,7 +32,7 @@ import java.lang.reflect.Method;
 import java.lang.reflect.Modifier;
 import java.util.Collection;
 import java.util.Enumeration;
-import java.util.HashMap;
+import java.util.LinkedHashMap;
 import java.util.Iterator;
 import java.util.Locale;
 import java.util.Map;
@@ -152,7 +152,9 @@ public class JSONObject {
      * Construct an empty JSONObject.
      */
     public JSONObject() {
-        this.map = new HashMap<String, Object>();
+//      this.map = new HashMap<String, Object>();
+        // I want to keep order of the given data:
+        this.map = new LinkedHashMap<String, Object>();
     }

     /**
@@ -243,7 +245,7 @@ public class JSONObject {
      * @throws JSONException
      */
     public JSONObject(Map<String, Object> map) {
-        this.map = new HashMap<String, Object>();
+        this.map = new LinkedHashMap<String, Object>();
         if (map != null) {
             Iterator<Entry<String, Object>> i = map.entrySet().iterator();
             while (i.hasNext()) {

Answer 13

You can use the following code to do custom ORDERED serialization and deserialization of JSON Array (This example assumes you are ordering Strings but can be applied to all types):

Serialization

JSONArray params = new JSONArray();
int paramIndex = 0;

for (String currParam : mParams)
{
    JSONObject paramObject = new JSONObject();
    paramObject.put("index", paramIndex);
    paramObject.put("value", currParam);

    params.put(paramObject);
    ++paramIndex;
}

json.put("orderedArray", params);

Deserialization

JSONArray paramsJsonArray = json.optJSONArray("orderedArray");
if (null != paramsJsonArray)
{
    ArrayList<String> paramsArr = new ArrayList<>();
    for (int i = 0; i < paramsJsonArray.length(); i++)
    {
        JSONObject param = paramsJsonArray.optJSONObject(i);
        if (null != param)
        {
            int paramIndex = param.optInt("index", -1);
            String paramValue = param.optString("value", null);

            if (paramIndex > -1 && null != paramValue)
            {
                paramsArr.add(paramIndex, paramValue);
            }
        }
    }
}

Answer 14

Your example:

{
    "items":
    [
        {
            "WR":"qwe",
            "QU":"asd",
            "QA":"end",
            "WO":"hasd",
            "NO":"qwer"
        },
        ...
    ]
}

add an element "itemorder"

{
    "items":
    [
        {
            "WR":"qwe",
            "QU":"asd",
            "QA":"end",
            "WO":"hasd",
            "NO":"qwer"
        },
        ...
    ],
    "itemorder":["WR","QU","QA","WO","NO"]
}

This code generates the desired output without the column title line:

JSONObject output = new JSONObject(json);
JSONArray docs = output.getJSONArray("data");
JSONArray names = output.getJSONArray("itemOrder");
String csv = CDL.toString(names,docs);

Answer 15

instead of using jsonObject try using CsvSchema its way easier and directly converts object to csv

CsvSchema schema = csvMapper.schemaFor(MyClass.class).withHeader();
        csvMapper.writer(schema).writeValueAsString(myClassList);

and it mentains the order id your pojo has @JsonPropertyOrder in it

Answer 16

You can use

toString(JSONArray names, JSONArray ja)

It Produces a comma delimited text from a JSONArray of JSONObjects using a provided list of names in the same order as json array .

Refer : https://stleary.github.io/JSON-java/org/json/CDL.html#toString-org.json.JSONArray-org.json.JSONArray-

Answer 17

The most safe way is probably overriding keys method that is used to generate output:

new JSONObject(){
  @Override
  public Iterator keys(){
    TreeSet<Object> sortedKeys = new TreeSet<Object>();
    Iterator keys = super.keys();
    while(keys.hasNext()){
      sortedKeys.add(keys.next());
    }
    return sortedKeys.iterator();
  }
};

Answer 18

Tested the wink solution, and working fine:

@Test
public void testJSONObject() {
    JSONObject jsonObject = new JSONObject();
    jsonObject.put("bbb", "xxx");
    jsonObject.put("ccc", "xxx");
    jsonObject.put("aaa", "xxx");
    jsonObject.put("xxx", "xxx");
    System.out.println(jsonObject.toString());
    assertTrue(jsonObject.toString().startsWith("{\"xxx\":"));
}

@Test
public void testWinkJSONObject() throws JSONException {
    org.apache.wink.json4j.JSONObject jsonObject = new OrderedJSONObject();
    jsonObject.put("bbb", "xxx");
    jsonObject.put("ccc", "xxx");
    jsonObject.put("aaa", "xxx");
    jsonObject.put("xxx", "xxx");
    assertEquals("{\"bbb\":\"xxx\",\"ccc\":\"xxx\",\"aaa\":\"xxx\",\"xxx\":\"xxx\"}", jsonObject.toString());
}