Ekspresi jalur JSON

Artikel
03/01/2024

Berlaku untuk: check ditandai ya pemeriksaan Databricks SQL Databricks Runtime

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

Sintaks

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

Tanda kurung siku mengelilingi field, * dan index merupakan tanda kurung aktual dan tidak menunjukkan sintaks opsional.

Parameter

pengidentifikasi: Pengidentifikasi kasus yang tidak sensitif dari bidang JSON.
[ field ]: STRING sensitif huruf besar/kecil yang dikurung yang mengidentifikasi bidang JSON.
[ * ]: Mengidentifikasi semua elemen dalam array JSON.
[ index ]: Literal bilangan bulat yang mengidentifikasi elemen tertentu dalam array JSON berbasis 0.

Kembali

A STRING.

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

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

Gunakan fungsi from_json untuk melemparkan hasil berlapis ke dalam jenis data yang lebih kompleks, seperti array atau struktur.

Catatan

Anda dapat menggunakan pengidentifikasi yang tidak dibatasi untuk merujuk ke bidang JSON jika namanya tidak berisi spasi, atau karakter khusus, dan tidak ada bidang 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 mengharuskan Anda untuk sama persis dengan kasus bidang.

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

Contoh

Contoh berikut menggunakan data yang dibuat dengan pernyataan dalam Contoh data.

Di bagian ini:

Mengekstrak menggunakan pengidentifikasi dan pemisah
Mengekstrak bidang berlapis
Mengekstrak nilai dari array
Perilaku NULL
Nilai transmisi
Contoh data

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

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

Nilai transmisi

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

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

:Operator