JSON-sökvägsuttryck

Gäller för: Databricks SQL Databricks Runtime

Ett JSON-sökvägsuttryck används för att extrahera values från en JSON-sträng eller en VARIANT med operatorn :

Syntax

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

Hakparenteserna som omger field, * och index är faktiska hakparenteser och anger inte en valfri syntax.

Parameters

• identifier: En identifier för ett JSON-fält. För JSON STRING typ är identifier skiftlägesokänslig. För VARIANT typer är den skiftlägeskänslig.
• [ field ]: En skiftlägeskänslig STRING literal som identifierar ett JSON-fält.
• [ * ]: Identifiera alla element i en JSON-matris. Den här syntaxen stöds inte för VARIANT.
• [ index ]: En heltalsliteral som identifierar ett specifikt element i en 0-baserad JSON-matris.

Returer

A STRING om uttrycket som navigeras är en STRING. A VARIANT om uttrycket som navigeras är en VARIANT.

När ett JSON-fält finns med ett icke avgränsat null-värde får du ett SQL-NULL-värde för det column, inte ett null textvärde.

Du kan använda :: operatorn för att omvandla values till grundläggande datatyper.

Använd funktionen from_json för att omvandla kapslade resultat till mer komplexa datatyper, till exempel matriser eller structs.

Kommentar

Du kan använda en icke avgränsad identifier för att referera till ett JSON-fält om namnet inte innehåller blanksteg eller specialtecken, och det inte finns något fält i JSON-STRING med samma namn i ett annat fall.

Använd en avgränsad identifier om det inte finns något fält med samma namn i ett annat fall.

Notationen [ field ] kan alltid användas, men kräver att du exakt matchar fallet med fältet.

Om Azure Databricks inte unikt kan identifiera ett fält returneras ett fel. Om ingen matchning hittas för något fält returnerar NULLAzure Databricks .

Ett NULL värde kan kodas i en VARIANT, och det värdet är inte en SQL NULL. parse_json('null') IS NULL Därför är false, men is_variant_null(parse_json('null')) är true. En VARIANT kodad null kan konverteras till en SQL NULL genom att omvandla den till någon typ. Till exempel parse_json('null')::int IS NULL är true.

Exempel

I följande exempel används data som skapats med -instruktionen i Exempeldata.

I detta avsnitt:

• Extrahera med identifier och avgränsare
• Extrahera kapslade fält
• Extrahera values från matriser
• NULL-beteende
• Cast values
• VARIANT-uttryck
• Exempeldata

Extrahera med identifier och avgränsare

> 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

Extrahera kapslade fält

-- 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" }'

Extrahera values från matriser

-- 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-beteende

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

Cast values

-- price is returned as a double, not a string
> SELECT raw:store.bicycle.price::double FROM store_data
  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
  '{ "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-uttryck

-- 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
  19.95

Exempeldata

> 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;

Relaterade funktioner

• :operatör