查询 JSON 文件

适用于：SQL 分析终结点和 Microsoft Fabric 中的数据仓库✅

本文介绍如何使用 Fabric SQL 查询 JSON 文件，包括 Fabric 数据仓库和 SQL 分析终结点。

JSON（JavaScript 对象表示法）是一种轻型格式，适用于半结构化数据，通常用于传感器流、IoT 配置、日志和地理空间数据（例如 GeoJSON）的大数据。

使用 OPENROWSET 直接查询 JSON 文件

在 Fabric 数据仓库和 Lakehouse 的 SQL 分析终结点中，可以使用函数 OPENROWSET 直接在 Lake 中查询 JSON 文件。

OPENROWSET( BULK '{{filepath}}', [ , <options> ... ])
[ WITH ( <column schema and mappings> ) ];

使用 OPENROWSET 查询 JSON 文件时，首先指定文件路径，可以是面向一个或多个文件的直接 URL 或通配符模式。默认情况下，Fabric 将 JSON 文档中的每个顶级属性投影为结果集中的单独列。对于 JSON 行文件，每行都被视为单个行，因此非常适合流式处理方案。

如果需要更多控制：

使用可选 WITH 子句显式定义架构，并将列映射到特定的 JSON 属性，包括嵌套路径。
使用DATA_SOURCE来引用相对路径的根位置。
配置错误处理参数，例如 MAXERRORS，以优雅地处理解析问题。

常见 JSON 文件用例

可以在 Microsoft Fabric 中处理的常见 JSON 文件类型和用例：

行分隔的 JSON （“JSON Lines”）文件，其中每行都是独立的有效的 JSON 文档（例如，事件、读取或日志条目）。
- 整个文件不一定是单个有效的 JSON 文档，而是用换行符分隔的 JSON 对象序列。
- 此格式的文件通常具有扩展名 .jsonl， .ldjson或 .ndjson。非常适合流媒体和仅追加的场景，作者无需重写文件或破坏结构即可将新事件追加为新行。
具有.json扩展名的单文档 JSON（“经典 JSON”）文件，其中整个文件作为一个有效的 JSON 文档，可能是一个单个对象或一个可能嵌套的对象数组。
- 它通常用于整体导出的配置、快照和数据集。
- 例如，GeoJSON 文件通常存储描述特征及其几何图形的单个 JSON 对象。

使用 OPENROWSET 查询 JSONL 文件

Fabric 数据仓库和 Lakehouse 的 SQL 分析终结点使 SQL 开发人员能够使用 OPENROWSET 函数直接从 Data Lake 查询 JSON Lines（.jsonl、.ldjson、.ndjson）文件。

这些文件每行包含一个有效的 JSON 对象，因此非常适合流媒体和仅追加操作场景。若要读取 JSON Lines 文件，请在参数中 BULK 提供其 URL：

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl'
);

默认情况下， OPENROWSET 使用架构推理，自动发现每个 JSON 对象中的所有顶级属性，并将其作为列返回。

但是，可以显式定义架构以控制返回的属性并重写推断的数据类型：

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl'
) WITH (
    country_region VARCHAR(100),
    confirmed INT,
    date_reported DATE '$.updated'
);

显式架构定义在以下情况下非常有用：

你想要替代默认推断类型（例如，强制日期数据类型而不是 varchar）。
你需要稳定的列名和选择性投影。
你想要将列映射到特定的 JSON 属性，包括嵌套路径。

使用 OPENROWSET 读取复杂（嵌套）JSON 结构

Fabric 数据仓库和 Lakehouse 的 SQL 分析终结点允许 SQL 开发人员使用 OPENROWSET 嵌套对象或子数组直接从数据湖读取 JSON。

{
  "type": "Feature",
  "properties": {
    "shapeName": "Serbia",
    "shapeISO": "SRB",
    "shapeID": "94879208B25563984444888",
    "shapeGroup": "SRB",
    "shapeType": "ADM0"
  }
}

在以下示例中，查询包含示例数据的文件，并使用 WITH 子句显式投影其叶级属性：

SELECT
    *
FROM
  OPENROWSET(
    BULK '/Files/parquet/nested/geojson.jsonl'
  )
  WITH (
    -- Top-level field
    [type]     VARCHAR(50),
    -- Leaf properties from the nested "properties" object
    shapeName  VARCHAR(200) '$.properties.shapeName',
    shapeISO   VARCHAR(50)  '$.properties.shapeISO',
    shapeID    VARCHAR(200) '$.properties.shapeID',
    shapeGroup VARCHAR(50)  '$.properties.shapeGroup',
    shapeType  VARCHAR(50)  '$.properties.shapeType'
  );

注释

此示例使用 没有数据源的相对路径，该路径在 通过 Lakehouse 的 SQL 分析终结点查询文件时有效。在 Fabric 数据仓库中，您必须执行以下操作之一：

使用文件的 绝对路径 ，或
在外部数据源中指定根 URL，并使用OPENROWSET此选项在DATA_SOURCE语句中引用它。

使用 OPENROWSET 扩展嵌套数组（JSON 到行）

Fabric 数据仓库和 Lakehouse 的 SQL 分析终结点允许使用 OPENROWSET嵌套数组读取 JSON 文件。然后，可以使用扩展（展平）这些数组。当顶级文档包含每个元素一行所需的子数组时，此方法非常有用。

在以下简化的示例输入中，类似 GeoJSON 的文档具有特征数组：

{
  "type": "FeatureCollection",
  "crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
  "features": [
    {
      "type": "Feature",
      "properties": {
        "shapeName": "Serbia",
        "shapeISO": "SRB",
        "shapeID": "94879208B25563984444888",
        "shapeGroup": "SRB",
        "shapeType": "ADM0"
      },
      "geometry": {
        "type": "Line",
        "coordinates": [[[19.6679328, 46.1848744], [19.6649294, 46.1870428], [19.6638492, 46.1890231]]]
      }
    }
  ]
}

以下查询：

使用 OPENROWSET 从湖中读取 JSON 文档，并投影顶级类型属性以及原始特征数组。
CROSS APPLY OPENJSON适用于展开特征数组，以便每个元素成为结果集中自己的行。在此扩展中，查询使用 JSON 路径表达式提取嵌套值。值（如shapeName、shapeISO和geometry）及详细信息（如geometry.type和coordinates）现在被转换成扁平化列，便于分析。

SELECT
  r.crs_name,
  f.[type] AS feature_type,
  f.shapeName,
  f.shapeISO,
  f.shapeID,
  f.shapeGroup,
  f.shapeType,
  f.geometry_type,
  f.coordinates
FROM
  OPENROWSET(
      BULK '/Files/parquet/nested/geojson.jsonl'
  )
  WITH (
      crs_name    VARCHAR(100)  '$.crs.properties.name', -- top-level nested property
      features    VARCHAR(MAX)  '$.features'             -- raw JSON array
  ) AS r
CROSS APPLY OPENJSON(r.features)
WITH (
  [type]           VARCHAR(50),
  shapeName        VARCHAR(200)  '$.properties.shapeName',
  shapeISO         VARCHAR(50)   '$.properties.shapeISO',
  shapeID          VARCHAR(200)  '$.properties.shapeID',
  shapeGroup       VARCHAR(50)   '$.properties.shapeGroup',
  shapeType        VARCHAR(50)   '$.properties.shapeType',
  geometry_type    VARCHAR(50)   '$.geometry.type',
  coordinates      VARCHAR(MAX)  '$.geometry.coordinates'
) AS f;

反馈

此页面是否有帮助？

Last updated on 2026-02-13