在 SQL 中查询半结构化数据

备注

适用于 Databricks Runtime 8.1 及更高版本。

本文介绍 Databricks SQL 运算符,该运算符可用于查询和转换以 JSON 形式存储的半结构化数据。

备注

利用此功能,可读取半结构化数据,而无需平展文件。 但是,为了获得最佳的读取查询性能,Databricks 建议提取具有正确数据类型的嵌套列。

语法

可以使用语法 <column-name>:<extraction-path> 从包含 JSON 字符串的字段中提取列,其中 <column-name> 是字符串列名,而 <extraction-path> 是要提取的字段的路径。 返回的结果为字符串。

示例

下面的示例使用在示例数据中通过语句创建的数据。

本节内容:

提取顶级列

若要提取列,请在提取路径中指定 JSON 字段的名称。

可以在括号中提供列名。 括号内引用的列区分大小写。 列名的引用也可不区分大小写。

SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy   | amy   |
+-------+-------+
-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy              | null           |
+------------------+----------------+

使用反引号转义空格和特殊字符。 字段名称匹配(不区分大小写)。

-- Use backticks to escape special characters. References are case insensitive when you use backticks.
-- Use brackets to make them case sensitive.
SELECT raw:`zip code`, raw:`Zip Code`, raw:['fb:testid'] FROM store_data
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025    | 94025    | 1234      |
+----------+----------+-----------+

备注

如果 JSON 记录包含多个由于不区分大小写而与提取路径匹配的列,则会出现要求使用方括号这一错误。 如果行之间的列匹配,则不会遇到任何错误。 {"foo":"bar", "Foo":"bar"} 会引发错误,但以下内容将不会引发错误:

{"foo":"bar"}
{"Foo":"bar"}

提取嵌套字段

可以通过点表示法或使用方括号指定嵌套字段。 使用方括号时,各列区分大小写。

-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle          |
+------------------+
| {                |
|   "price":19.95, |
|   "color":"red"  |
| }                |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle          | BICYCLE |
+------------------+---------+
| {                | null    |
|   "price":19.95, |         |
|   "color":"red"  |         |
| }                |         |
+------------------+---------+

从数组中提取值

可以使用方括号对数组中的元素进行索引。 索引从 0 开始。 可以使用星号 (*),后跟点或括号表示法来从数组中的所有元素中提取子字段。

-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit            | fruit           |
+------------------+-----------------+
| {                | {               |
|   "weight":8,    |   "weight":9,   |
|   "type":"apple" |   "type":"pear" |
| }                | }               |
+------------------+-----------------+
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn               |
+--------------------+
| [                  |
|   null,            |
|   "0-553-21311-3", |
|   "0-395-19395-8"  |
| ]                  |
+--------------------+
-- Access arrays within arrays or structs within arrays
SELECT
    raw:store.basket[*],
    raw:store.basket[*][0] first_of_baskets,
    raw:store.basket[0][*] first_basket,
    raw:store.basket[*][*] all_elements_flattened,
    raw:store.basket[0][2].b subfield
FROM store_data
+----------------------------+------------------+---------------------+---------------------------------+----------+
| basket                     | first_of_baskets | first_basket        | all_elements_flattened          | subfield |
+----------------------------+------------------+---------------------+---------------------------------+----------+
| [                          | [                | [                   | [1,2,{"b":"y","a":"x"},3,4,5,6] | y        |
|   [1,2,{"b":"y","a":"x"}], |   1,             |   1,                |                                 |          |
|   [3,4],                   |   3,             |   2,                |                                 |          |
|   [5,6]                    |   5              |   {"b":"y","a":"x"} |                                 |          |
| ]                          | ]                | ]                   |                                 |          |
+----------------------------+------------------+---------------------+---------------------------------+----------+

强制转换值

可以使用 :: 将值转换为基本数据类型。 使用 from_json 方法将嵌套结果强制转换为更复杂的数据类型,例如数组或结构。

-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price            |
+------------------+
| 19.95            |
+------------------+
-- use from_json to cast into more complex types
SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
-- the column returned is a struct containing the columns price and color
+------------------+
| bicycle          |
+------------------+
| {                |
|   "price":19.95, |
|   "color":"red"  |
| }                |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays
+------------------------------------------+
| basket                                   |
+------------------------------------------+
| [                                        |
|   ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
|   ["3","4"],                             |
|   ["5","6"]                              |
| ]                                        |
+------------------------------------------+

示例数据

CREATE TABLE store_data AS SELECT
'{
   "store":{
      "fruit": [
        {"weight":8,"type":"apple"},
        {"weight":9,"type":"pear"}
      ],
      "basket":[
        [1,2,{"b":"y","a":"x"}],
        [3,4],
        [5,6]
      ],
      "book":[
        {
          "author":"Nigel Rees",
          "title":"Sayings of the Century",
          "category":"reference",
          "price":8.95
        },
        {
          "author":"Herman Melville",
          "title":"Moby Dick",
          "category":"fiction",
          "price":8.99,
          "isbn":"0-553-21311-3"
        },
        {
          "author":"J. R. R. Tolkien",
          "title":"The Lord of the Rings",
          "category":"fiction",
          "reader":[
            {"age":25,"name":"bob"},
            {"age":26,"name":"jack"}
          ],
          "price":22.99,
          "isbn":"0-395-19395-8"
        }
      ],
      "bicycle":{
        "price":19.95,
        "color":"red"
      }
    },
    "owner":"amy",
    "zip code":"94025",
    "fb:testid":"1234"
 }' as raw

NULL 行为

如果存在具有 null 值的 JSON 字段,则会收到该列的 SQL null 值,而不是 null 文本值。

select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null    | text_null |
+-------------+-----------+
| true        | null      |
+-------------+-----------+