JSON APIJSON API

有时,你需要将 RDBMS 或 NOSQL 数据存储中未包含,但可从某些 HTTP API 获取的数据进行可视化处理。Sometimes you need to visualize data not contained in an RDBMS or NOSQL data store, but available from some HTTP API. 针对这些情况,SQL Analytics 提供了 JSON 数据源。For those times, SQL Analytics provides the JSON data source.

SQL Analytics 将来自 JSON 数据源的所有传入数据都视为文本,因此在呈现数据时必须使用表格式SQL Analytics treats all incoming data from the JSON data source as text so you must use table formatting when rendering the data.

JSON 数据源类型JSON data source type

使用 JSON 数据源查询 JSON API。Use the JSON data source to query JSON APIs. 设置很简单,无需进行身份验证。Setup is easy because no authentication is needed. 任何 REST JSON API 都将通过 HTTP 标头处理身份验证。Any REST JSON API will handle authentication through HTTP headers. 新建一个 JSON 类型的外部数据源,并随意命名(“JSON”是一个不错的选择)。Create a new external data source of type JSON and name it whatever you like (“JSON” is a good choice).

SQL Analytics 会检测 JSON 支持的数据类型(如数字、字符串、布尔值),而其他类型(主要是日期/时间戳)将被视为字符串,除非它们以 ISO-8601 格式指定。SQL Analytics detects data types supported by JSON (like numbers, strings, booleans), but others (mostly date/timestamp) are treated as strings (unless specified in ISO-8601 format).

使用情况Usage

此数据源接受 [YAML 格式]的查询。This data source accepts queries in [YAML format]. 下面是使用 GitHub API 的一些示例。Here are some examples using the GitHub API.

从终结点返回对象列表Return a list of objects from an endpoint

url: https://api.github.com/repos/getsql/sql/issues

这将按原样返回上述 API 调用的结果。This will return the result of the above API call as is.

对象的 JSON 列表JSON list of objects

返回一个对象Return a single object

url: https://api.github.com/repos/getsql/sql/issues/3495

上述 API 调用会返回一个对象,此对象将转换为一行。The above API call returns a single object, and this object is being converted to a row.

单个 JSON 对象Single JSON Object

返回特定字段Return specific rields

若要仅从生成的对象中选择特定字段,可传递 fields 选项:To pick only specific fields from the resulting object(s), you can pass the fields option:

url: https://api.github.com/repos/getsql/sql/issues
fields: [number, title]

选择 JSON 字段JSON field select

返回内部对象Return an inner object

许多 JSON API 会返回嵌套对象的数组。Many JSON API’s return arrays of nested objects. 可使用 path 键来访问数组中的对象。You can access an object in an array with the path key.

url: https://api.github.com/repos/getsql/sql/issues/3495
path: assignees

此查询使用 API 结果中的 assignee 对象作为查询结果。This query uses the assignee objects from the API result as the query result.

传递查询字符串参数Pass query string parameters

你可创建自己的 URL,也可传递 params 选项:You can either craft your own URLs, or you can pass the params option:

url: "https://api.github.com/search/issues"
params:
  q: is:open type:pr repo:getsql/sql
  sort: created
  order: desc

此查询相当于:This query is the same as:

url: "https://api.github.com/search/issues?q=+is:open+type:pr+repo:getsql/sql&sort=created&order=desc"

其他 HTTP 选项Additional HTTP options

你可传递其他键来修改各种 HTTP 选项:You can pass additional keys to modify various HTTP options:

  • method - 要使用的 HTTP 方法(默认为 getmethod - the HTTP method to use (default: get)
  • headers - 要与请求一同发送的标头的字典headers - a dictionary of headers to send with the request
  • auth - 基本身份验证用户名和密码(应作为数组传递:[username, password]auth - basic authentication username and password (should be passed as an array: [username, password])
  • params - 要添加到 URL 的查询字符串参数的字典params - a dictionary of query string parameters to add to the URL
  • data - 要用作请求正文的值的字典data - a dictionary of values to use as request body
  • json - 与 data 相同,只不过它将转换为 JSONjson - same as data except that it’s being converted to JSON

使用情况Usage

查询正文应只包含返回数据的 URL,例如:The body of your query should include only the URL that returns data, for example:

http://myserver/path/myquery

必需的数据结构Required data structure

返回的对象必须公开两个键:columnsrowsThe returned object must expose two keys: columns and rows.

  • columns 键应公开一个 Javascript 对象数组,该数组描述要包含在数据集中的列。The columns key should expose an array of Javascript objects describing the columns to be included in your data set. 每个对象包括 3 个键:Each object includes three keys:
    • name
    • type
    • friendly_name
  • rows 应返回表示每行数据的 Javascript 对象的数组。rows should return an array of Javascript objects representing each row of data. 每个对象的键应与 columns 数组中描述的 name 键相匹配。The keys for each object should match the name keys described in your columns array.

列支持以下数据类型:The following data types are supported for columns:

  • 文本text
  • integerinteger
  • floatfloat
  • booleanboolean
  • stringstring
  • datetimedatetime
  • datedate

返回的数据示例:An example of returned data:

{
  "columns": [
    {
      "name": "date",
      "type": "date",
      "friendly_name": "date"
    },
    {
      "name": "day_number",
      "type": "integer",
      "friendly_name": "day_number"
    },
    {
      "name": "value",
      "type": "integer",
      "friendly_name": "value"
    },
    {
      "name": "total",
      "type": "integer",
      "friendly_name": "total"
    }
  ],
  "rows": [
    {
      "value": 40832,
      "total": 53141,
      "day_number": 0,
      "date": "2014-01-30"
    },
    {
      "value": 27296,
      "total": 53141,
      "day_number": 1,
      "date": "2014-01-30"
    },
    {
      "value": 22982,
      "total": 53141,
      "day_number": 2,
      "date": "2014-01-30"
    }
  ]
}