Выражение пути для JSON

Статья
04/01/2025

Область применения: отмечено Databricks SQL Databricks Runtime

Выражение пути JSON используется для извлечения значений из JSON-строки или VARIANT с помощью оператора :

Синтаксис

{ { identifier | [ field ] | [ * ] | [ index ] }
  [ . identifier | [ field ] | [ * ] | [ index ] ] [...] }

Квадратные скобки, в которых заключены field, * и index, — это фактические квадратные скобки, не указывающие на необязательный синтаксис.

Параметры

идентификатор: идентификатор поля JSON. Для типов JSON STRING идентификатор не чувствителен к регистру. Для VARIANT типов чувствителен к регистру.
[ field ]: литерал в квадратных скобках, учитывающий регистр, идентифицирующий поле JSON.
[ * ]: определение всех элементов в массиве JSON. Этот синтаксис не поддерживается для VARIANT.
[ index ]: целочисленный литерал, определяющий конкретный элемент в массиве JSON на основе 0.

Возвраты

Значение STRING, если перемещаемое выражение является STRING. Значение VARIANT, если перемещаемое выражение — это VARIANT.

Если поле JSON существует со значением без разделителя null, вы получите значение SQL NULL для этого столбца, а не текстовое значение null.

Можно использовать оператор ::, чтобы приводить значения к базовым типам данных.

Используйте функцию from_json для приведения вложенных результатов в более сложные типы данных, например массивы или структуры.

Примечания.

Идентификатор без разделителя можно использовать для ссылки на поле JSON, если имя не содержит пробелов или специальных символов, а поле в формате JSON STRING одного и того же имени в другом случае отсутствует.

Используйте идентификатор с разделителями, если в другом случае нет поля того же имени.

Нотацию [ field ] можно использовать всегда, но необходимо точное соответствие регистру поля.

Если Azure Databricks не может однозначно определить поле, возвращается ошибка. Если совпадение не найдено ни для одного из полей Azure Databricks, возвращается NULL.

Значение NULL может быть закодировано в VARIANT, и это значение не является SQL NULL. Таким образом, parse_json('null') IS NULL — это false, но is_variant_null(parse_json('null')) — это true. Кодированный VARIANT null можно преобразовать в SQL NULL , приведя его к определенному типу. Например, parse_json('null')::int IS NULL - это true.

Примеры

В следующих примерах используются данные, созданные с помощью инструкции из раздела Пример данных.

В этом разделе рассматриваются следующие вопросы.

Извлечь с помощью идентификаторов и разделителей
Извлечение вложенных полей
Извлечение значений из массивов
Поведение NULL
значения приведения
Выражения варьянтов
Примеры данных

Извлечение с помощью идентификаторов и разделителей

> SELECT raw:owner, raw:OWNER, raw:['owner'], raw:['OWNER'] FROM store_data;
  amy  amy  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;
  94025      94025      1234

Извлечение вложенных полей

-- Use dot notation
> SELECT raw:store.bicycle FROM store_data;
  '{ "price":19.95, "color":"red" }'

-- Use brackets
> SELECT raw:['store']['bicycle'] FROM store_data;
  '{ "price":19.95, "color":"red" }'

Извлечение значений из массивов

-- Index elements
> SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data;
  '{ "weight":8, "type":"apple" }'  '{ "weight":9, "type":"pear" }'

-- Extract subfields from arrays
> SELECT raw:store.book[*].isbn FROM store_data;
  '[ 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"}
  ]                            ]                  ]

Поведение при NULL

> SELECT '{"key":null}':key IS NULL sql_null, '{"key":"null"}':key IS NULL;
  true          false

Приведение значений

-- price is returned as a double, not a string
> SELECT raw:store.bicycle.price::double FROM store_data
  1.95

-- use from_json to cast into more complex types
> SELECT from_json(raw:store.bicycle, 'price double, color string') bicycle FROM store_data
  '{ "price":19.95, "color":"red" }'

-- the column returned is an array of string arrays
> SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
  '[
    ["1","2","{\"b\":\"y\",\"a\":\"x\"}]",
    ["3","4"],
    ["5","6"]
  ]'

Выражения VARIANT

-- Using JSON paths for VARIANT
> SELECT raw:store.bicycle FROM store_data_variant;
  '{ "price":19.95, "color":"red" }'

-- Extracting from VARIANT arrays
> SELECT raw:store.fruit[0], raw_variant:store.fruit[1] FROM store_data_variant;
  '{ "weight":8, "type":"apple" }'  '{ "weight":9, "type":"pear" }'

-- SQL NULL behavior of VARIANT NULL values
> SELECT
  parse_json(NULL) IS NULL AS sql_null,
  parse_json('null') IS NULL AS variant_null,
  parse_json('{ "field_a": null }'):field_a IS NULL AS variant_null_value,
  parse_json('{ "field_a": null }'):missing IS NULL AS missing_sql_value_null
  true  false  false  true

-- price is returned as a double, not a VARIANT
> SELECT raw:store.bicycle.price::double FROM store_data_variant
  1.95

Демонстрационные данные

> 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

 > CREATE TABLE store_data_variant AS SELECT parse_json(raw) FROM store_data;

: оператор

Поделиться через

Выражение пути для JSON

Синтаксис

Параметры

Возвраты

Примечания.

Примеры

В этом разделе рассматриваются следующие вопросы.

Извлечение с помощью идентификаторов и разделителей

Извлечение вложенных полей

Извлечение значений из массивов

Поведение при NULL

Приведение значений

Выражения VARIANT

Демонстрационные данные

Обратная связь

Дополнительные ресурсы

Поделиться через

Выражение пути для JSON

Синтаксис

Параметры

Возвраты

Примечания.

Примеры

В этом разделе рассматриваются следующие вопросы.

Извлечение с помощью идентификаторов и разделителей

Извлечение вложенных полей

Извлечение значений из массивов

Поведение при NULL

Приведение значений

Выражения VARIANT

Демонстрационные данные

Связанные функции

Обратная связь

Дополнительные ресурсы