如何用JSON表示表格数据?

I'm writing an API for retrieving data from a JDBC-connected Java Servlet via JSON. I've chosen to use JSON because we'll want to do sorts and other operations on the data in the browser, and we'll be accessing the data from across domains.

我正在编写一个API，用于通过JSON从与jdbc连接的Java Servlet中检索数据。我选择使用JSON是因为我们想要对浏览器中的数据进行排序和其他操作，我们将访问跨域的数据。

Since I'm essentially doing SQL queries in JavaScript, the data that comes back is tabular in nature. I started to write this so that you get back a list of column labels, then arrays of values, for example:

因为我本质上是在JavaScript中执行SQL查询，所以返回的数据本质上是表格式的。我开始写这个，这样你会得到一个列标签列表，然后是数组的值，例如:

{
  "columns": [
    "given_name",
    "surname",
  ],
  "results": [
    [
      "Joe",
      "Schmoe"
    ],
    [
      "Jane",
      "Doe"
    ]
  ]
}

But as I start to write the JavaScript to deal with the returned data, I wonder if it might be better to just output the results with key/value pairs, such as:

但是，当我开始编写JavaScript来处理返回的数据时，我想知道是否最好只输出键/值对的结果，例如:

{
  "results": [
    {
      "given_name": "Joe",
      "surname": "Schmoe"
    },
    {
      "given_name": "Jane",
      "surname" : "Doe"
    }
  ]
}

If you're returning a lot of results, that's a lot of repeated text. But we're going to be transporting gzipped, so I'm not too concerned about bandwidth.

如果你要返回很多结果，那就是很多重复的文本。但是我们要传输gziked，所以我不太关心带宽。

Basically, should I engineer this so that I'm accessing my data with

基本上，我应该设计这个以便访问我的数据

$.getJSON(query, function(data) {
  var columns = data.columns;
  var results = data.results;
  $.each(results, function(key, row) {
    console.log(row[columns.indexOf('surname')]);
  });
});

or the much prettier

或者更漂亮

$.getJSON(query, function(data) {
  var results = data.results;
  $.each(results, function(key, row) {
    console.log(row.surname);
  });
});

吗?

Essentially, I want to know if the potential hit to performance justifies the much cleaner syntax of the latter option.

本质上，我想知道性能的潜在影响是否证明后一种选择的语法更简洁。

Follow up

I did implement it both ways and profile. Profiling was a great idea! The differences in performance were marginal. The differences in data transfer size were substantial, but with Gzip compression, the variance was down to 5-6% between both formats and between very large and very small data sets. So I'm going with the prettier implementation. For this particular application, I can expect all clients to support Gzip/Deflate, so the size doesn't matter, and the computational complexity on both the client and server is similar enough that it doesn't matter.

我实现了它的方式和概要。剖析是一个好主意!性能上的差异是微乎其微的。数据传输大小的差异是巨大的，但是在Gzip压缩下，两种格式之间以及非常大和非常小的数据集之间的差异降低到5-6%。我用更漂亮的实现。对于这个特定的应用程序，我可以期望所有的客户端都支持Gzip/Deflate，因此大小并不重要，而且客户端和服务器上的计算复杂性非常相似，因此也不重要。

For anyone interested, here is my data with graphs!.

对于任何有兴趣的人，这是我的数据与图表!

5 个解决方案

#1

Synthesizing other answers:

综合其他的答案:

Your wire format doesn't have to be the same as your in-memory format.
您的有线格式不必与内存格式相同。
Profile which is better - see if it makes a difference.
纵览哪个更好-看看它是否有不同。
Simpler is usually better to start with.
简单通常是最好的开始。

Further:

进一步指出:

If you just have a page of results, and few users, then the 2nd format may be no worse than the 1st format.
如果你只有一个页面的结果，而且用户很少，那么第二种格式可能不会比第一种格式差。
If your data is quite sparse, the 2nd format may well be better.
如果您的数据相当稀疏，第二种格式可能更好。
If you're sending 1000's or rows of data, and you have millions of users, then it's possible that the size of data you send can start to matter, and perhaps the 1st format may help.
如果你发送1000行数据，而你有数百万用户，那么你发送的数据的大小可能开始起作用，也许第一种格式会有帮助。
You can't guarantee that all user agents support gzip / deflate, so bear this in mind.
您不能保证所有用户代理都支持gzip / deflate，所以请记住这一点。

#2

Profile both. Optimize afterwards.

概要文件。优化之后。

#3

You don't have to tie your code to the more compact, but also more cumbersome format. Just write a simple JS adapter to check the returned structure for the presence of columns. If that's missing you're dealing with a plain array of objects. If it's present you can easily map the cumbersome format to the more convenient format.

您不必将代码绑定到更紧凑的格式，但也需要更复杂的格式。只需编写一个简单的JS适配器，检查返回的结构是否存在列。如果缺少这个，那就是在处理一个普通的对象数组。如果它存在，你可以很容易地将繁琐的格式映射到更方便的格式。

#4

FWIW I'd go for the second option, it lends itself to cleaner JavaScript as you've observed and will also be easier for a human to read and understand. It seems to me that readability trumps whatever little performance gain you get from option 1.

FWIW我选择第二个选项，正如您所观察到的，它可以使JavaScript更简洁，而且对人类来说也更容易阅读和理解。在我看来，可读性胜过任何从选项1获得的性能增益。

I also imagine if you were to add more columns or the order of columns changed someday, with the first option, you'll likely have to rewrite a lot of the JavaScript since you'll be working with the position of the data in the response.

我还设想，如果有一天你要添加更多的列，或者列的顺序发生了变化，有了第一个选项，你可能要重写很多JavaScript，因为你要处理响应中数据的位置。

#5

Just another JSON structure from which I got very nice results:

这是另一个JSON结构，我从中得到了很好的结果:

{
    "recordCount": 2,
    "data": {
        "Id": [1, 2],
        "Title": ["First record", "Second record"]
        "Value": [18192, 18176]
    }
}

Traversing all data:

遍历所有的数据:

for (var i = 0; i < recordSet.recordCount; ++i) {
    console.log("Record " + i.toString() + ":");
    for (var field in recordSet.data)
        console.log("\t" + field + ": " + recordSet.data[field][i].toString());
}

#1