Mengkueri string JSON
Artikel ini menjelaskan operator Databricks SQL yang dapat Anda gunakan untuk mengkueri dan mengubah data semi terstruktur yang disimpan sebagai string JSON.
Catatan
Fitur ini memungkinkan Anda membaca data semi-terstruktur tanpa meratakan file. Namun, untuk performa kueri baca yang optimal, Databricks merekomendasikan agar Anda mengekstrak kolom bertumpuk dengan tipe data yang benar.
Anda mengekstrak kolom dari bidang yang berisi string JSON menggunakan sintaksis <column-name>:<extraction-path>, yang mana <column-name> adalah nama kolom string dan <extraction-path> merupakan jalur ke bidang untuk mengekstrak. Hasil yang dikembalikan adalah string.
Membuat tabel dengan data yang sangat berlapis
Jalankan kueri berikut untuk membuat tabel dengan data yang sangat berlapis. Contoh dalam artikel ini semuanya mereferensikan tabel ini.
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
Mengekstrak kolom tingkat atas
Untuk mengekstrak kolom, tentukan nama bidang JSON di jalur ekstraksi Anda.
Anda dapat memberikan nama kolom dalam tanda kurung. Kolom yang direferensikan di dalam tanda kurung peka huruf besar/kecil yang cocok. Nama kolom juga direferensikan tidak peka huruf besar/kecil yang cocok.
SELECT raw:owner, RAW:owner FROM store_data
+-------+-------+
| owner | owner |
+-------+-------+
| amy | amy |
+-------+-------+
-- References are case sensitive when you use brackets
SELECT raw:OWNER case_insensitive, raw:['OWNER'] case_sensitive FROM store_data
+------------------+----------------+
| case_insensitive | case_sensitive |
+------------------+----------------+
| amy | null |
+------------------+----------------+
Gunakan backtick untuk spasi escape dan karakter khusus. Nama bidang tidak peka huruf besar/kecil.
-- 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
+----------+----------+-----------+
| zip code | Zip Code | fb:testid |
+----------+----------+-----------+
| 94025 | 94025 | 1234 |
+----------+----------+-----------+
Catatan
Jika catatan JSON berisi beberapa kolom yang dapat mencocokkan jalur ekstraksi Anda karena pencocokan tidak peka huruf besar/kecil, Anda akan menerima kesalahan yang meminta Anda untuk menggunakan tanda kurung. Jika memiliki kecocokan kolom di seluruh baris, Anda tidak akan menerima kesalahan apa pun. Berikut ini akan menimbulkan kesalahan: {"foo":"bar", "Foo":"bar"}, dan berikut ini tidak akan menimbulkan kesalahan:
{"foo":"bar"}
{"Foo":"bar"}
Mengekstrak bidang bersarang
Anda menentukan bidang bersarang melalui notasi titik atau menggunakan tanda kurung. Saat Anda menggunakan tanda kurung, kolom peka huruf besar/kecil yang cocok.
-- Use dot notation
SELECT raw:store.bicycle FROM store_data
-- the column returned is a string
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
-- Use brackets
SELECT raw:store['bicycle'], raw:store['BICYCLE'] FROM store_data
+------------------+---------+
| bicycle | BICYCLE |
+------------------+---------+
| { | null |
| "price":19.95, | |
| "color":"red" | |
| } | |
+------------------+---------+
Mengekstrak nilai dari array
Anda mengindeks elemen dalam array dengan tanda kurung. Indeks berbasis 0. Anda dapat menggunakan tanda bintang (*) diikuti dengan notasi titik atau kurung untuk mengekstrak subbidang dari semua elemen dalam array.
-- Index elements
SELECT raw:store.fruit[0], raw:store.fruit[1] FROM store_data
+------------------+-----------------+
| fruit | fruit |
+------------------+-----------------+
| { | { |
| "weight":8, | "weight":9, |
| "type":"apple" | "type":"pear" |
| } | } |
+------------------+-----------------+
-- Extract subfields from arrays
SELECT raw:store.book[*].isbn FROM store_data
+--------------------+
| isbn |
+--------------------+
| [ |
| 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"} | | |
| ] | ] | ] | | |
+----------------------------+------------------+---------------------+---------------------------------+----------+
Mentransmisikan nilai
Anda dapat menggunakan :: untuk mentransmisikan nilai ke jenis data dasar. Gunakan fungsi from_json untuk mentransmisikan hasil bersarang ke jenis data yang lebih kompleks, seperti array atau struct.
-- price is returned as a double, not a string
SELECT raw:store.bicycle.price::double FROM store_data
+------------------+
| price |
+------------------+
| 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
-- the column returned is a struct containing the columns price and color
+------------------+
| bicycle |
+------------------+
| { |
| "price":19.95, |
| "color":"red" |
| } |
+------------------+
SELECT from_json(raw:store.basket[*], 'array<array<string>>') baskets FROM store_data
-- the column returned is an array of string arrays
+------------------------------------------+
| basket |
+------------------------------------------+
| [ |
| ["1","2","{\"b\":\"y\",\"a\":\"x\"}]", |
| ["3","4"], |
| ["5","6"] |
| ] |
+------------------------------------------+
Perilaku NULL
Saat ada bidang JSON dengan nilai null yang tidak dibatasi, Anda akan menerima nilai SQL null untuk kolom tersebut, bukan nilai teks null.
select '{"key":null}':key is null sql_null, '{"key":null}':key == 'null' text_null
+-------------+-----------+
| sql_null | text_null |
+-------------+-----------+
| true | null |
+-------------+-----------+
Mengubah data berlapis menggunakan operator Spark SQL
Apache Spark memiliki sejumlah fungsi bawaan untuk bekerja dengan data yang kompleks dan berlapis. Buku catatan berikut berisi contoh.
Selain itu, fungsi urutan yang lebih tinggi menyediakan banyak opsi tambahan saat operator Spark bawaan tidak tersedia untuk mengubah data seperti yang Anda inginkan.
Notebook data berlapis yang kompleks
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk