Subkueri di Azure Cosmos DB untuk NoSQL
BERLAKU UNTUK: NoSQL
Subkueri adalah kueri yang ditumpuk dalam kueri lain dalam Azure Cosmos DB for NoSQL. Subkueri juga disebut kueri dalam atau dalam SELECT
. Pernyataan yang berisi subkueri biasanya disebut kueri luar.
Jenis subkueri
Ada dua jenis utama subkueri:
- Berkorelasi: Subkueri yang mereferensikan nilai dari kueri luar. Subkueri ini dievaluasi sekali untuk setiap baris yang diproses kueri luar.
- Non-berkorelasi: Subkueri yang independen dari kueri luar. Subkueri ini dapat berjalan sendiri tanpa mengandalkan kueri luar.
Catatan
Azure Cosmos DB hanya mendukung subkueri yang berkorelasi.
Subkueri dapat diklasifikasikan lebih lanjut berdasarkan jumlah baris dan kolom yang mereka kembalikan. Terdapat tiga jenis:
- Tabel: Mengembalikan beberapa baris dan beberapa kolom.
- Multi-nilai: Mengembalikan beberapa baris dan satu kolom.
- Skalar: Mengembalikan satu baris dan satu kolom.
Kueri di Azure Cosmos DB untuk NoSQL selalu mengembalikan satu kolom (baik nilai sederhana atau item kompleks). Oleh karena itu, hanya subkueri multinilai dan skalar yang berlaku. Anda dapat menggunakan subkueri multinilai hanya dalam FROM
klausa sebagai ekspresi relasional. Anda dapat menggunakan subkueri skalar sebagai ekspresi skalar dalam SELECT
klausa atau WHERE
, atau sebagai ekspresi relasional dalam FROM
klausa.
Subkueri multi-nilai
Subkueri multinilai mengembalikan sekumpulan item dan selalu digunakan dalam FROM
klausa. Mereka digunakan untuk:
- Mengoptimalkan
JOIN
ekspresi (gabungan mandiri). - Mengevaluasi ekspresi mahal sekali dan referensi beberapa kali.
Mengoptimalkan ekspresi gabungan mandiri
Subkueri multinilai dapat mengoptimalkan JOIN
ekspresi dengan mendorong predikat setelah setiap ekspresi pilih-banyak daripada setelah semua gabungan silang dalam WHERE
klausa.
Pertimbangkan kueri berikut:
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
t in p.tags
JOIN
q in p.onHandQuantities
JOIN
s in p.warehouseStock
WHERE
t.name IN ("winter", "fall") AND
(q.quantity BETWEEN 0 AND 10) AND
NOT s.backstock
Untuk kueri ini, indeks cocok dengan item apa pun yang memiliki tag dengan name
"musim dingin" atau "jatuh", setidaknya satu quantity
antara nol dan sepuluh, dan setidaknya satu gudang di mana backstock
adalah false
. Ekspresi JOIN
di sini melakukan lintas produk dari semua item tags
, , onHandQuantities
dan warehouseStock
array untuk setiap item yang cocok sebelum filter apa pun diterapkan.
Klausul WHERE
kemudian menerapkan predikat filter pada setiap <c, t, n, s>
tuple. Misalnya, jika item yang cocok memiliki sepuluh item di masing-masing dari tiga array, item akan diperluas ke 1 x 10 x 10 x 10
(yaitu, 1.000) tuple. Menggunakan subkueri di sini dapat membantu dalam memfilter item array gabungan sebelum bergabung dengan ekspresi berikutnya.
Kueri ini setara dengan yang sebelumnya tetapi menggunakan subkueri:
SELECT VALUE
COUNT(1)
FROM
products p
JOIN
(SELECT VALUE t FROM t IN p.tags WHERE t.name IN ("winter", "fall"))
JOIN
(SELECT VALUE q FROM q IN p.onHandQuantities WHERE q.quantity BETWEEN 0 AND 10)
JOIN
(SELECT VALUE s FROM s IN p.warehouseStock WHERE NOT s.backstock)
Asumsikan bahwa hanya satu item dalam array tag yang cocok dengan filter, dan ada lima item untuk array kuantitas dan stok. Ekspresi JOIN
kemudian diperluas ke 1 x 1 x 5 x 5
(25) item, dibandingkan dengan 1.000 item dalam kueri pertama.
Mengevaluasi sekali dan mereferensi berkali-kali
Subkueri dapat membantu mengoptimalkan kueri dengan ekspresi mahal seperti fungsi yang ditentukan pengguna (UW), string kompleks, atau ekspresi aritmatika. Anda dapat menggunakan subkueri bersama dengan JOIN
ekspresi untuk mengevaluasi ekspresi sekali tetapi mereferensikannya berkali-kali.
Mari kita asumsikan bahwa Anda memiliki UDF (getTotalWithTax
) berikut yang ditentukan.
function getTotalWithTax(subTotal){
return subTotal * 1.25;
}
Kueri berikut menjalankan UDF getTotalWithTax
beberapa kali:
SELECT VALUE {
subtotal: p.price,
total: udf.getTotalWithTax(p.price)
}
FROM
products p
WHERE
udf.getTotalWithTax(p.price) < 22.25
Berikut ini kueri yang setara yang menjalankan UDF hanya sekali:
SELECT VALUE {
subtotal: p.price,
total: totalPrice
}
FROM
products p
JOIN
(SELECT VALUE udf.getTotalWithTax(p.price)) totalPrice
WHERE
totalPrice < 22.25
Tip
Perlu diingat perilaku JOIN
ekspresi lintas produk. Jika ekspresi UDF dapat mengevaluasi ke undefined
, Anda harus memastikan bahwa JOIN
ekspresi selalu menghasilkan satu baris dengan mengembalikan objek dari subkueri daripada nilai secara langsung.
Meniru JOIN dengan data referensi eksternal
Anda mungkin sering perlu mereferensikan data statis yang jarang berubah, seperti satuan pengukuran. Sangat ideal untuk tidak menduplikasi data statis untuk setiap item dalam kueri. Menghindari duplikasi ini menghemat penyimpanan dan meningkatkan performa tulis dengan menjaga ukuran item individual lebih kecil. Anda dapat menggunakan subkueri untuk meniru semantik gabungan dalam dengan kumpulan data referensi statis.
Misalnya, pertimbangkan serangkaian pengukuran ini:
Nama | Pengali | Satuan dasar | |
---|---|---|---|
ng |
Nanogram | 1.00E-09 |
Gram |
µg |
Mikrogram | 1.00E-06 |
Gram |
mg |
Miligram | 1.00E-03 |
Gram |
g |
Gram | 1.00E+00 |
Gram |
kg |
Kilogram | 1.00E+03 |
Gram |
Mg |
Megagram | 1.00E+06 |
Gram |
Gg |
Gigagram | 1.00E+09 |
Gram |
Kueri berikut ini meniru bergabung dengan data ini sehingga Anda menambahkan nama unit ke keluaran:
SELECT
s.id,
(s.weight.quantity * m.multiplier) AS calculatedWeight,
m.unit AS unitOfWeight
FROM
shipments s
JOIN m IN (
SELECT VALUE [
{unit: 'ng', name: 'nanogram', multiplier: 0.000000001, baseUnit: 'gram'},
{unit: 'µg', name: 'microgram', multiplier: 0.000001, baseUnit: 'gram'},
{unit: 'mg', name: 'milligram', multiplier: 0.001, baseUnit: 'gram'},
{unit: 'g', name: 'gram', multiplier: 1, baseUnit: 'gram'},
{unit: 'kg', name: 'kilogram', multiplier: 1000, baseUnit: 'gram'},
{unit: 'Mg', name: 'megagram', multiplier: 1000000, baseUnit: 'gram'},
{unit: 'Gg', name: 'gigagram', multiplier: 1000000000, baseUnit: 'gram'}
]
)
WHERE
s.weight.units = m.unit
Subkueri skalar
Ekspresi subkueri skalar adalah subkueri yang dievaluasi ke nilai tunggal. Nilai ekspresi subkueri skalar adalah nilai proyeksi (SELECT
klausa) subkueri. Anda dapat menggunakan ekspresi subkueri skalar di banyak tempat ekspresi skalar bersifat valid. Misalnya, Anda dapat menggunakan subkueri skalar dalam ekspresi apa pun dalam SELECT
klausa dan WHERE
.
Menggunakan subkueri skalar tidak selalu membantu mengoptimalkan kueri Anda. Misalnya, meneruskan subkueri skalar sebagai argumen ke fungsi sistem atau yang ditentukan pengguna tidak memberikan manfaat dalam mengurangi konsumsi atau latensi unit sumber daya (RU).
Subkueri skalar dapat diklasifikasikan lebih lanjut sebagai:
- Subkueri skalar ekspresi sederhana
- Subkueri skalar agregat
Subkueri skalar ekspresi sederhana
Subkueri skalar ekspresi sederhana adalah subkueri berkorelasi yang memiliki SELECT
klausa yang tidak berisi ekspresi agregat apa pun. Subkueri ini tidak memberikan manfaat pengoptimalan karena pengompilasi mengubahnya menjadi satu ekspresi sederhana yang lebih besar. Tidak ada konteks yang berkorelasi antara kueri dalam dan kueri luar.
Sebagai contoh pertama, pertimbangkan kueri sepele ini.
SELECT
1 AS a,
2 AS b
Anda dapat menulis ulang kueri ini, dengan menggunakan subkueri skalar ekspresi sederhana.
SELECT
(SELECT VALUE 1) AS a,
(SELECT VALUE 2) AS b
Kedua kueri menghasilkan output yang sama.
[
{
"a": 1,
"b": 2
}
]
Contoh kueri berikutnya ini menggabungkan pengidentifikasi unik dengan awalan sebagai subkueri skalar ekspresi sederhana.
SELECT
(SELECT VALUE Concat('ID-', p.id)) AS internalId
FROM
products p
Contoh ini menggunakan subkueri skalar ekspresi sederhana untuk hanya mengembalikan bidang yang relevan untuk setiap item. Kueri menghasilkan sesuatu untuk setiap item, tetapi hanya menyertakan bidang yang diproyeksikan jika memenuhi filter dalam subkueri.
SELECT
p.id,
(SELECT p.name WHERE CONTAINS(p.name, "glove")).name
FROM
products p
[
{
"id": "aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb",
"name": "Winter glove"
},
{
"id": "bbbbbbbb-1111-2222-3333-cccccccccccc"
},
{
"id": "cccccccc-2222-3333-4444-dddddddddddd"
}
]
Subkueri skalar agregat
Subkueri skalar agregat adalah subkueri yang memiliki fungsi agregat dalam proyeksi atau filternya yang mengevaluasi ke nilai tunggal.
Sebagai contoh pertama, pertimbangkan item dengan bidang berikut.
{
"name": "Snow coat",
"inventory": [
{
"location": "Redmond, WA",
"quantity": 50
},
{
"location": "Seattle, WA",
"quantity": 30
},
{
"location": "Washington, DC",
"quantity": 25
}
]
}
Berikut adalah subkueri dengan ekspresi fungsi agregat tunggal dalam proyeksinya. Kueri ini menghitung semua tag untuk setiap item.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM i IN p.inventory) AS locationCount
FROM
products p
[
{
"name": "Snow coat",
"locationCount": 3
}
]
Berikut adalah subkueri yang sama dengan filter.
SELECT
p.name,
(SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE ENDSWITH(i.location, "WA")) AS washingtonLocationCount
FROM
products p
[
{
"name": "Snow coat",
"washingtonLocationCount": 2
}
]
Berikut adalah subkueri lain dengan beberapa ekspresi fungsi agregat:
SELECT
p.name,
(SELECT
COUNT(1) AS locationCount,
SUM(i.quantity) AS totalQuantity
FROM i IN p.inventory) AS inventoryData
FROM
products p
[
{
"name": "Snow coat",
"inventoryData": {
"locationCount": 2,
"totalQuantity": 75
}
}
]
Terakhir, berikut adalah kueri dengan subkueri agregat dalam proyeksi dan filter:
SELECT
p.name,
(SELECT VALUE AVG(q.quantity) FROM q IN p.inventory WHERE q.quantity > 10) AS averageInventory
FROM
products p
WHERE
(SELECT VALUE COUNT(1) FROM i IN p.inventory WHERE i.quantity > 10) >= 1
[
{
"name": "Snow coat",
"averageInventory": 35
}
]
Cara yang lebih optimal untuk menulis kueri ini adalah dengan bergabung pada subkueri dan mereferensikan alias subkueri dalam klausul SELECT dan WHERE. Kueri ini lebih efisien karena Anda perlu menjalankan subkueri hanya dalam pernyataan gabungan, dan bukan dalam proyeksi dan filter.
SELECT
p.name,
inventoryData.inventoryAverage
FROM
products p
JOIN
(SELECT
COUNT(1) AS inventoryCount,
AVG(i.quantity) as inventoryAverage
FROM i IN p.inventory
WHERE i.quantity > 10) AS inventoryData
WHERE
inventoryData.inventoryCount >= 1
ekspresi EXISTS
Mesin kueri Azure Cosmos DB for NoSQL mendukung EXISTS
ekspresi. Ekspresi ini adalah subkueri skalar agregat yang dibangun ke dalam Azure Cosmos DB untuk NoSQL. EXISTS
mengambil ekspresi subkueri dan mengembalikan true
jika subkueri mengembalikan baris apa pun. Jika tidak, ia kembali false
.
Karena mesin kueri tidak membedakan antara ekspresi boolean dan ekspresi skalar lainnya, Anda dapat menggunakan EXISTS
dalam klausa SELECT
dan WHERE
. Perilaku ini tidak seperti T-SQL, di mana ekspresi boolean dibatasi hanya untuk filter.
EXISTS
Jika subkueri mengembalikan nilai tunggal yaitu undefined
, EXISTS
mengevaluasi ke false. Misalnya, pertimbangkan kueri berikut yang tidak mengembalikan apa pun.
SELECT VALUE
undefined
Jika Anda menggunakan EXISTS
ekspresi dan kueri sebelumnya sebagai subkueri, ekspresi mengembalikan false
.
SELECT
EXISTS (SELECT VALUE undefined)
[
{
"$1": false
}
]
Jika kata kunci VALUE dalam subkueri sebelumnya dihilangkan, subkueri mengevaluasi ke array dengan satu objek kosong.
SELECT
undefined
[
{}
]
Pada titik itu EXISTS
, ekspresi mengevaluasi karena true
objek ({}
) secara teknis keluar.
SELECT
EXISTS (SELECT undefined)
[
{
"$1": true
}
]
Kasus ARRAY_CONTAINS
penggunaan umum adalah memfilter item berdasarkan keberadaan item dalam array. Dalam hal ini, kami sedang memeriksa apakah tags
array berisi item bernama "outerwear."
SELECT
p.name,
p.tags
FROM
products p
WHERE
ARRAY_CONTAINS(p.tags, "outerwear")
Kueri yang sama dapat digunakan EXISTS
sebagai opsi alternatif.
SELECT
p.name,
p.tags
FROM
products p
WHERE
EXISTS (SELECT VALUE t FROM t IN p.tags WHERE t = "outerwear")
Selain itu, ARRAY_CONTAINS
hanya dapat memeriksa apakah nilai sama dengan elemen apa pun dalam array. Jika Anda memerlukan filter yang lebih kompleks pada properti array, gunakan JOIN
sebagai gantinya.
Pertimbangkan item contoh ini dalam satu set dengan beberapa item yang masing-masing berisi accessories
array.
{
"name": "Unobtani road bike",
"accessories": [
{
"name": "Front/rear tire",
"type": "tire",
"quantityOnHand": 5
},
{
"name": "9-speed chain",
"type": "chains",
"quantityOnHand": 25
},
{
"name": "Clip-in pedals",
"type": "pedals",
"quantityOnHand": 15
}
]
}
Sekarang, pertimbangkan kueri berikut yang memfilter berdasarkan type
properti dan quantityOnHand
dalam array dalam setiap item.
SELECT
p.name,
a.name AS accessoryName
FROM
products p
JOIN
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10
[
{
"name": "Unobtani road bike",
"accessoryName": "9-speed chain"
}
]
Untuk setiap item dalam koleksi, lintas produk dilakukan dengan elemen array-nya. Operasi ini JOIN
memungkinkan untuk memfilter properti dalam array. Namun, konsumsi RU kueri ini signifikan. Misalnya, jika 1.000 item memiliki 100 item di setiap array, item akan diperluas ke 1,000 x 100
tuple (yaitu, 100.000).
Menggunakan EXISTS
dapat membantu menghindari produk silang yang mahal ini. Dalam contoh berikutnya ini, kueri memfilter pada elemen array dalam subkueri EXISTS
. Jika elemen array cocok dengan filter, maka Anda memproyeksikannya dan EXISTS
mengevaluasinya ke true.
SELECT VALUE
p.name
FROM
products p
WHERE
EXISTS (SELECT VALUE
a
FROM
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10)
[
"Unobtani road bike"
]
Kueri juga dapat alias EXISTS
dan mereferensikan alias dalam proyeksi:
SELECT
p.name,
EXISTS (SELECT VALUE
a
FROM
a IN p.accessories
WHERE
a.type = "chains" AND
a.quantityOnHand >= 10) AS chainAccessoryAvailable
FROM
products p
[
{
"name": "Unobtani road bike",
"chainAccessoryAvailable": true
}
]
ekspresi array
Anda bisa menggunakan ARRAY
ekspresi untuk memproyeksikan hasil kueri sebagai array. Anda hanya dapat menggunakan ekspresi ini dalam SELECT
klausa kueri.
Untuk contoh ini, mari kita asumsikan ada kontainer dengan setidaknya item ini.
{
"name": "Radimer mountain bike",
"tags": [
{
"name": "road"
},
{
"name": "bike"
},
{
"name": "competitive"
}
]
}
Dalam contoh pertama ini, ekspresi digunakan dalam SELECT
klausa.
SELECT
p.name,
ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames
FROM
products p
[
{
"name": "Radimer mountain bike",
"tagNames": [
"road",
"bike",
"competitive"
]
}
]
Seperti halnya subkueri lainnya, filter dengan ekspresi dimungkinkan ARRAY
.
SELECT
p.name,
ARRAY (SELECT VALUE t.name FROM t in p.tags) AS tagNames,
ARRAY (SELECT VALUE t.name FROM t in p.tags WHERE CONTAINS(t.name, "bike")) AS bikeTagNames
FROM
products p
[
{
"name": "Radimer mountain bike",
"tagNames": [
"road",
"bike",
"competitive"
],
"bikeTagNames": [
"bike"
]
}
]
Ekspresi array juga dapat datang setelah FROM
klausul dalam subkueri.
SELECT
p.name,
n.t.name AS nonBikeTagName
FROM
products p
JOIN
n IN (SELECT VALUE ARRAY(SELECT t FROM t in p.tags WHERE t.name NOT LIKE "%bike%"))
[
{
"name": "Radimer mountain bike",
"nonBikeTagName": "road"
},
{
"name": "Radimer mountain bike",
"nonBikeTagName": "competitive"
}
]