JSON-padexpressie

Van toepassing op: Databricks SQL Databricks Runtime

Een JSON-padexpressie wordt gebruikt om waarden te extraheren uit een JSON-tekenreeks of een VARIANT met behulp van de operator :

Syntaxis

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

De haken rond fielden * index zijn werkelijke haakjes en geven geen optionele syntaxis aan.

Parameters

• id: een id van een JSON-veld. Voor JSON-typen STRING is de id niet hoofdlettergevoelig. Voor VARIANT typen is het hoofdlettergevoelig.
• [ field ]: Een letterlijke letterlijke waarde tussen haakjes die een JSON-veld STRING identificeert.
• [ * ]: alle elementen in een JSON-matrix identificeren. Deze syntaxis wordt niet ondersteund voor VARIANT.
• [ index ]: Een letterlijk geheel getal dat een specifiek element in een JSON-matrix op basis van 0 identificeert.

Retouren

A STRING als de expressie die wordt genavigeerd een STRING. A VARIANT als de expressie die wordt genavigeerd een VARIANT.

Wanneer er een JSON-veld bestaat met een niet-gescheiden null waarde, ontvangt u een SQL-waarde NULL voor die kolom, niet een null tekstwaarde.

U kunt :: operator gebruiken om waarden te casten naar basisgegevenstypen.

Gebruik de functie from_json om geneste resultaten te casten naar complexere gegevenstypen, zoals matrices of structs.

Opmerkingen

U kunt een niet-gescheiden id gebruiken om te verwijzen naar een JSON-veld als de naam geen spaties of speciale tekens bevat en er in een ander geval geen veld in de JSON STRING van dezelfde naam staat.

Gebruik een id met scheidingstekens als er in een ander geval geen veld met dezelfde naam is.

De [ field ] notatie kan altijd worden gebruikt, maar vereist dat u exact overeenkomt met het hoofdlettergebruik van het veld.

Als Azure Databricks een veld niet uniek kan identificeren, wordt er een fout geretourneerd. Als er geen overeenkomst wordt gevonden voor een veld dat Azure Databricks retourneert NULL.

Een NULL waarde kan worden gecodeerd binnen een VARIANT, en die waarde is geen SQL NULL. Daarom, parse_json('null') IS NULL is false, maar is_variant_null(parse_json('null')) is true. Een VARIANT gecodeerde null kan worden geconverteerd naar een SQL NULL door deze naar een bepaald type te casten. Is bijvoorbeeld parse_json('null')::int IS NULL true.

Voorbeelden

In de volgende voorbeelden worden de gegevens gebruikt die zijn gemaakt met de instructie in voorbeeldgegevens.

In deze sectie:

• Extraheren met id's en scheidingstekens
• Geneste velden extraheren
• Waarden extraheren uit matrices
• NULL-gedrag
• Cast-waarden
• VARIANT-expressies
• Voorbeeldgegevens

Extraheren met id's en scheidingstekens

> 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

Geneste velden extraheren

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

Waarden extraheren uit matrices

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

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

Cast-waarden

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

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

Voorbeeldgegevens

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

Gerelateerde functies

• :bediener