Bagikan melalui

Ekspresi jalur JSON

Berlaku untuk:centang ditandai ya Databricks SQL centang ditandai ya Databricks Runtime

Ekspresi jalur JSON digunakan untuk mengekstrak nilai dari string JSON atau VARIANT menggunakan operator :

Sintaks

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

Tanda kurung di sekitar field, *, dan index merupakan tanda kurung aktual dan tidak mengindikasikan sintaks opsional.

Parameter

  • pengidentifikasi: Pengidentifikasi bidang JSON. Untuk jenis JSON STRING , pengidentifikasi tidak peka huruf besar/kecil. Untuk jenis VARIANT, ini memperhatikan aksara besar dan kecil.
  • [ field ]: Literal yang peka terhadap huruf besar/kecil STRING yang diapit tanda kurung yang mengidentifikasi bidang JSON.
  • [ * ]: Mengidentifikasi semua elemen dalam array JSON. Sintaks ini tidak didukung untuk VARIANT.
  • [ index ]: Literal bilangan bulat yang mengidentifikasi elemen tertentu dalam larik JSON berbasis 0.

Pengembalian

Jika STRING ekspresi yang dinavigasi adalah STRING. Jika VARIANT ekspresi yang dinavigasi adalah VARIANT.

Ketika ada bidang JSON dengan nilai null yang tidak dibatasi, Anda akan menerima nilai SQL NULL untuk kolom tersebut, bukan nilai teks null.

Anda dapat menggunakan :: operator untuk mentransmisikan nilai ke jenis data dasar.

Gunakan fungsi from_json untuk mentransmisikan hasil berlapis ke jenis data yang lebih kompleks, seperti array atau struct.

Catatan

Anda dapat menggunakan pengidentifikasi yang tidak dibatasi untuk merujuk ke bidang JSON jika nama tidak berisi spasi atau karakter khusus, dan tidak ada bidang dalam JSON STRING dengan nama yang sama dalam kasus yang berbeda.

Gunakan pengidentifikasi yang dibatasi jika tidak ada bidang dengan nama yang sama dalam kasus yang berbeda.

Notasi [ field ] selalu dapat digunakan, tetapi harus sama persis dengan kasus bidang.

Jika Azure Databricks tidak dapat mengidentifikasi bidang secara unik, kesalahan dikembalikan. Jika tidak ada kecocokan yang ditemukan untuk bidang apa pun, Azure Databricks mengembalikan NULL.

Nilai NULL dapat dikodekan dalam VARIANT, dan nilai tersebut bukan SQL NULL. Oleh karena itu, parse_json('null') IS NULL adalah false, tetapi is_variant_null(parse_json('null')) adalah true. Null VARIANT yang dikodekan dapat dikonversi ke SQL NULL dengan mengubahnya ke tipe tertentu. Misalnya, parse_json('null')::int IS NULL adalah true.

Contoh

Contoh berikut menggunakan data yang dibuat dengan pernyataan di Data contoh.

Di bagian ini:

Ekstraksi menggunakan pengidentifikasi dan pemisah

> 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

Mengekstrak bidang bersarang

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

Mengekstrak nilai dari array

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

Perilaku NULL

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

Mentransmisikan nilai

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

Ekspresi Varian

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

Contoh data

> 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;
  • Last updated on