This week's book giveaway is in the XML and Related Technologies forum.
We're giving away four copies of Java XML & JSON and have Jeff Friesen on-line!
See this thread for details.
Win a copy of Java XML & JSON this week in the XML and Related Technologies forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Devaka Cooray
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

How to preserve order with JSONObject?  RSS feed

 
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When I run the following SQL query (which is used in the code below) in Oracle SQL developer :

SELECT SQLQUERY FROM EMP WHERE id = 6, I get the following result:



As seen above,for the above data returned by the above SQL query, the Java code below is converting it into the following JSON :



As seen above in the JSONResponse, since object is an unordered set of name/value pairts, so JSONObject isn't preserving the order of my object's name/value pairs.
How can I make sure that  order of the SQL columns is preserved.




Few things I tried:

1) I tried to override the keys method like this but this didn't work :



2) I was planning to use array by changing JSONObject obj = new JSONObject(); to JSONArray obj = new JSONArray(); but
on these lines starting with `  obj.put(column_name, rsNew.getArray(column_name));`, Eclipse was complaining and
saying "The method put(int,boolean) in the type JSONArray is not applicable for the arguments (String,Array) " and suggesting me
to either 1) Remove argument to match `put(Object)`
or
2) Change type of `column_name` to int.

I don't think I could go with #1 based on what I have in the code above.

For #2, I tried changing type of `column_name` to int but then Eclipse was asking me to change the type of `column_name` to int on the following line as well which might not be possible for me.



Please let me know if there is any other way to get around this order?

 
Saloon Keeper
Posts: 2321
293
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By design, the object's name/value pairs are unordered.

From json.org:
     - An object is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma).
     - An array is an ordered collection of values. An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by , (comma).


Why does the consumer of the JSON data care if the order is preserved?
 
Bartender
Posts: 20307
110
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And besides, you didn't even explictly order the results of your SQL query!

In SQL, there is no "natural order" to table rows, only the order imposed on a set of SELECTed data. If you don't use ORDER BY, the server is fully entitled to return the rows in a random order. Don't let past experience give you false security. In FoxPro, the order was consistently the order in which the rows had been added to the table, but in other DBMS's I've seen things like whatever order the last ordered request was told, and in the case of PostgreSQL, I saw things that made me suspect it truly did return a different random order each time.

The Java equivalent of a set of name/value pairs is a Map, and most java Map implementations don't have a natural ordering either.
 
Jack Tauson
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Ron McLeod wrote:By design, the object's name/value pairs are unordered.

From json.org:
     - An object is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma).
     - An array is an ordered collection of values. An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by , (comma).


Why does the consumer of the JSON data care if the order is preserved?



I see.The guy who wrote the  SQL hasn't done that and is expecting me to return the name of the columns in the order it returns in the Oracle SQL developer. I am displaying the JSON data in the User Interface in a table and hence the order of column is always in random order and I the guy who wrote the SQL is expecting in the same order
 
Jack Tauson
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:And besides, you didn't even explictly order the results of your SQL query!

In SQL, there is no "natural order" to table rows, only the order imposed on a set of SELECTed data. If you don't use ORDER BY, the server is fully entitled to return the rows in a random order. Don't let past experience give you false security. In FoxPro, the order was consistently the order in which the rows had been added to the table, but in other DBMS's I've seen things like whatever order the last ordered request was told, and in the case of PostgreSQL, I saw things that made me suspect it truly did return a different random order each time.

The Java equivalent of a set of name/value pairs is a Map, and most java Map implementations don't have a natural ordering either.



I see. I should probably ask the guy to put ORDER BY then.
 
Jack Tauson
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Jack Tauson wrote:

Tim Holloway wrote:And besides, you didn't even explictly order the results of your SQL query!

In SQL, there is no "natural order" to table rows, only the order imposed on a set of SELECTed data. If you don't use ORDER BY, the server is fully entitled to return the rows in a random order. Don't let past experience give you false security. In FoxPro, the order was consistently the order in which the rows had been added to the table, but in other DBMS's I've seen things like whatever order the last ordered request was told, and in the case of PostgreSQL, I saw things that made me suspect it truly did return a different random order each time.

The Java equivalent of a set of name/value pairs is a Map, and most java Map implementations don't have a natural ordering either.



I see. I should probably ask the guy to put ORDER BY then.



By the way, even if ORDER BY was used, say for example, ORDER BY  Employee Count, this won't bring the Employee Count column in place of the column where "Employee State of Residence" is there, right? If this is the case, then why the JSONObject is not printing the column Employee Count first and then Employee State of Residence? Same reason of unorder objects here as well? How can I fix it then?
 
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is "SQLQUERY"?

If it's SELECT * for example then there is no order to the columns (well, it's usually the order the columns were created in).
 
Tim Holloway
Bartender
Posts: 20307
110
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:What is "SQLQUERY"?

If it's SELECT * for example then there is no order to the columns (well, it's usually the order the columns were created in).



Actually, columns do have order. You can even query that order using JDBC ResultSet metadata.

However, that's a good question. "SQLQUERY" isn't the name of a column, which is what the syntax would imply. And I don't think you can called a canned query (stored procedure) that way in Oracle.
 
Jack Tauson
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:What is "SQLQUERY"?

If it's SELECT * for example then there is no order to the columns (well, it's usually the order the columns were created in).



Sorry about the confusion. SQLQuery is just SELECT * .  This would list the columns in some order I am guessing. However, the JSONObject would still decide its own random order, right? I was wondering how to preserve this order, if at all possible. Thanks
 
Tim Holloway
Bartender
Posts: 20307
110
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's likely, but not guaranteed, that the columns would be output in collating order. However, since you're looking at a dictionary, order is immaterial - what counts is the name/value. What you actually have is an array of dictionaries, so the row order is guaranteed, but the column order is insignificant.

Speaking of which, properly, each row should contain name/value pairs, and what you are showing look more like label/value pairs. I sincerely hope that your database column names aren't really "Employee Count" and "Employee State of Residence". Aside from having spaces, the mixed upper/lower case names would be a recipe for trouble in its own right. Also, JSON is intended primarily for machine-to-machine data transport, so really a long descriptive key is mostly a waste of bandwidth.
 
Dave Tolls
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Out of curiosity, what JSONObject is this?
The capitalisation means it's not the Oracle one.

The fact it uses put implies it's backed by a Map (the Android one is, for example), which means it's more than likely unordered.  Well, ordered via the hashes anyway (the Android one uses a HashMap).

There are other JSON libs that retain the order stuff is added to them.
 
Ron McLeod
Saloon Keeper
Posts: 2321
293
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Out of curiosity, what JSONObject is this?  .. The fact it uses put implies it's backed by a Map



It may be org.json.JSONObject, which does use a HashMap<String, Object>

Source code here.
 
Jack Tauson
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Tim Holloway wrote:It's likely, but not guaranteed, that the columns would be output in collating order. However, since you're looking at a dictionary, order is immaterial - what counts is the name/value. What you actually have is an array of dictionaries, so the row order is guaranteed, but the column order is insignificant.

Speaking of which, properly, each row should contain name/value pairs, and what you are showing look more like label/value pairs. I sincerely hope that your database column names aren't really "Employee Count" and "Employee State of Residence". Aside from having spaces, the mixed upper/lower case names would be a recipe for trouble in its own right. Also, JSON is intended primarily for machine-to-machine data transport, so really a long descriptive key is mostly a waste of bandwidth.



I see. Actually, there are column names like "Employee Count" and "Employee State of Residence".

So,do you think that there is no alternative to this, if I want to have columns ordered?
 
Dave Tolls
Master Rancher
Posts: 3887
45
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you want to have them ordered then you need to use a JSON library that handles that.
The one you are currently using, which looks like it's backed by a HashMap, is not going to work for you.
 
Ron McLeod
Saloon Keeper
Posts: 2321
293
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Jack Tauson wrote:So,do you think that there is no alternative to this, if I want to have columns ordered?



You could have a structure equivalent to List<List<Map<String, String>>> where the Map would only have one entry, but it would be a hack:
Why does the client-side/consumer doing with the data?  Why does it need the object entries ordered?
 
Tim Holloway
Bartender
Posts: 20307
110
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Jack Tauson wrote:So,do you think that there is no alternative to this, if I want to have columns ordered?



Those are really horrible column names, and as I said, they're setting the stage for all sorts of trouble. Column names like "employee_residence_state" and "employee_count" may not be as pretty, but they're a lot safer.

The columns will always be returned in the order given in the table's create statement, however that ordering is usually lost the second you put the name/value pairs into a map/dictionary. As we said, there's no natural order that determines what sequence they come out of the dictionary if you simply enumerate the name/value pairs.

But this is all very questionable. People don't want to read JSON in their reports or web pages. JSON is intended to pass data to whatever mechanisms will actually be preparing the data for printing or viewing. And in that case, it's up to the print/view logic to determine the column ordering. In cases where the ultimate consumer isn't human, such as when you intend the data to be stored in some other database, you might not even care about column ordering (since, for example, a SQL INSERT could supply the column names itself).

Note that although your column ordering is not guaranteed, the row ordering is. That's because in your JSON, the data is an array of dictionaries, with each dictionary being an array element corresponding to one returned row from the SQL query. So if you make your SQL query ORDER BY employee residence state ASC, then the rows will be guaranteed to be ordered by employee residence state. Because, while dictionaries/maps have no guaranteed order, arrays always do.
 
All of the world's problems can be solved in a garden - Geoff Lawton. Tiny ad:
RavenDB is an Open Source NoSQL Database that’s fully transactional (ACID) across your database
https://coderanch.com/t/704633/RavenDB-Open-Source-NoSQL-Database
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!