在工作簿中使用 JSONPath 转换 JSON 数据

工作簿可以从多个源查询数据。 某些终结点(例如 Azure 资源管理器或自定义终结点)可以返回 JSON 格式的结果。 如果查询终结点返回的 JSON 数据并非采用所需的格式,则可以使用 JSONPath 转换将 JSON 转换为表结构。 然后,可以使用该表来绘制工作簿可视化效果

JSONPath 是适用于 JSON 的一种查询语言,类似于 XML 的 XPath。 与 XPath 类似,JSONPath 允许从 JSON 结构中提取和筛选数据。

使用 JSONPath

在本例中,JSON 对象表示商店的库存。 我们将通过列出书籍的书名、作者和价格来创建商店供应书籍的表。

  1. 选择“编辑”,将工作簿切换到编辑模式。

  2. 使用“添加”>“添加查询”链接将查询控件添加到工作簿。

  3. 选择“JSON”作为数据源。

  4. 使用 JSON 编辑器输入以下 JSON 片段:

    { "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
        }
      }
    }
    
  5. 选择“结果设置”选项卡,并将结果格式切换为“JSON 路径”。

  6. 应用以下 JSON 路径设置:

    • JSON 路径表:$.store.books。 此字段表示表的根路径。 在本例中,我们关注的是商店的书籍库存。 表路径将按书籍信息筛选 JSON。

      列 ID 列 JSON 路径
      标题 $.title
      作者 $.author
      价格 $.price

    列 ID 将是列标题。 “列 JSON 路径”字段表示从表的根到列值的路径。

  7. 选择“运行查询”。

    Screenshot that shows editing a query item with JSON data source and JSON path result format.

使用正则表达式以转换值

可能有一些并非采用标准格式的数据。 为了有效地使用这些数据,需要将它们转换为标准格式。

在本例中,发布日期采用 YYYMMMDD 格式。 代码会将此值解释为数值而非文本,从而生成右对齐的数字,而不是日期。

可以使用结果设置中的“类型”、“正则表达式匹配”和“替换为”字段将结果转换为真实日期。

结果设置字段 说明
类型 允许显式更改 API 返回的值的类型。 此字段通常未设置,但可以使用该字段强制将值设置为其他类型。
正则表达式匹配 允许输入正则表达式以获取 API 返回值的一部分,而不是整个值。 此字段通常与“替换为”字段组合使用。
替换为 使用此字段可以创建新值和正则表达式。 如果此值为空,则默认值为 $&,这是表达式的匹配结果。 请参阅 string.replace 文档,以了解可用于生成其他输出的其他值。

若要将 YYYMMDD 格式转换为 YYYY-MM-DD 格式,请执行以下操作:

  1. 在网格中选择“已发布”行。

  2. 在“类型”字段中,选择“日期/时间”,以便该列在图表中可用。

  3. 在“正则表达式匹配”字段中,使用以下正则表达式:([0-9]{4})([0-9]{2})([0-9]{2})。 此正则表达式:

    • 匹配一个四位数,然后是一个两位数,接着是另一个两位数。
    • 要在下一步中使用的括号窗体捕获组。
  4. 在“替换为”中,使用以下正则表达式:$1-$2-$3。 此表达式将为每个捕获的组创建一个新字符串,它们之间有一个连字符,从而将“12345678”转换为“1234-56-78”。

  5. 再次运行查询。

    Screenshot that shows JSONpath converted to date-time format.

后续步骤