如何在工作簿中使用 JSONPath 转换 JSON 数据How to use JSONPath to transform JSON data in workbooks

工作簿可以从多个源查询数据。Workbooks is able to query data from many sources. 某些终结点(例如 Azure 资源管理器或自定义终结点)可以返回 JSON 格式的结果。Some endpoints, such as Azure Resource Manager or custom endpoint, can return results in JSON. 如果查询的终结点返回的 JSON 数据未采用所需的格式进行配置,可以使用 JSONPath 来转换结果。If the JSON data returned by the queried endpoint is not configured in a format that you desire, JSONPath can be used to transform the results.

JSONPath 是适用于 JSON 的一种查询语言,类似于 XML 的 XPath。JSONPath is a query language for JSON that is similar to XPath for XML. 与 XPath 类似,JSONPath 允许从 JSON 结构中提取和筛选数据。Like XPath, JSONPath allows for the extraction and filtration of data out of JSON structure.

工作簿作者可以使用 JSONPath 转换将 JSON 转换为表结构。By using JSONPath transformation, workbook authors are able to convert JSON into a table structure. 然后,可以使用该表来绘制工作簿可视化效果The table can then be used to plot workbook visualizations.

使用 JSONPathUsing JSONPath

  1. 单击“编辑”工具栏项,将工作簿切换到编辑模式。Switch the workbook to edit mode by clicking on the Edit toolbar item.
  2. 使用“添加” > “添加查询”链接将查询控件添加到工作簿。 Use the Add > Add query link to add a query control to the workbook.
  3. 选择“JSON”作为数据源。Select the data source as JSON.
  4. 使用 JSON 编辑器输入以下 JSON 片段Use the JSON editor to enter the following JSON snippet
    { "store": {
        "books": [ 
          { "category": "reference",
            "author": "Nigel Rees",
            "title": "Sayings of the Century",
            "price": 8.95
          },
          { "category": "fiction",
            "author": "Evelyn Waugh",
            "title": "Sword of Honour",
            "price": 12.99
          },
          { "category": "fiction",
            "author": "Herman Melville",
            "title": "Moby Dick",
            "isbn": "0-553-21311-3",
            "price": 8.99
          },
          { "category": "fiction",
            "author": "J. R. R. Tolkien",
            "title": "The Lord of the Rings",
            "isbn": "0-395-19395-8",
            "price": 22.99
          }
        ],
        "bicycle": {
          "color": "red",
          "price": 19.95
        }
      }
    }
    

假设以商店库存的表示形式提供了上述 JSON 对象。Let's assume we are given the above JSON object as a representation of a store's inventory. 我们的任务是通过列出书籍的书名、作者和价格,来创建商店供应书籍的表。Our task is to create a table of the store's available books by listing their titles, authors, and prices.

  1. 选择“结果设置”选项卡,并将结果格式切换为“JSON 路径”。 Select the Result Settings tab and switch the result format to JSON Path.

  2. 应用以下 JSON 路径设置:Apply the following JSON Path Settings:

    JSON 路径表:$.store.booksJSON Path Table: $.store.books. 此字段表示表的根路径。This field represents the path of the root of the table. 在本例中,我们关注的是商店的书籍库存。In this case, we care about the store's book inventory. 表路径将按书籍信息筛选 JSON。The table path filters the JSON to the book information.

    列 IDColumn IDs 列 JSON 路径Column JSON Path
    标题Title $.title
    作者Author $.author
    价格Price $.price

    列 ID 将是列标题。Column IDs will be the column headers. “列 JSON 路径”字段表示从表的根到列值的路径。Column JSON paths fields represent the path from the root of the table to the column value.

  3. 单击“运行查询”应用上述设置Apply the above settings by clicking Run Query

 使用 JSON 数据源和 JSON 路径结果格式编辑查询项Editing query item with JSON data source and JSON Path result format

后续步骤Next steps