Contoh subkueri SQL untuk Azure Cosmos DB

BERLAKU UNTUK: SQL API

Subkueri adalah kueri yang disarangkan di dalam kueri lain. Subkueri juga disebut kueri dalam atau pilihan dalam. Pernyataan yang berisi subkueri biasanya disebut kueri luar.

Artikel ini menjelaskan subkueri SQL dan kasus penggunaan umumnya di Azure Cosmos DB. Semua kueri sampel dalam dokumen ini dapat dijalankan dengan kumpulan data nutrisi sampel.

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 SQL di Azure Cosmos DB selalu mengembalikan satu kolom (nilai sederhana atau dokumen kompleks). Oleh karena itu, hanya subkueri multi-nilai dan skalar yang berlaku di Azure Cosmos DB. Anda hanya dapat menggunakan subkueri multi-nilai dalam klausul FROM sebagai ekspresi relasional. Anda dapat menggunakan subkueri skalar sebagai ekspresi skalar dalam klausul SELECT atau WHERE, atau sebagai ekspresi relasional dalam klausul FROM.

Subkueri multi-nilai

Subkueri multi-nilai mengembalikan sekumpulan dokumen dan selalu digunakan dalam klausul FROM. Mereka digunakan untuk:

  • Mengoptimalkan ekspresi JOIN.
  • Mengevaluasi ekspresi mahal sekali dan referensi beberapa kali.

Mengoptimalkan ekspresi JOIN

Subkueri multi-nilai dapat mengoptimalkan ekspresi JOIN dengan mendorong predikat setelah setiap ekspresi select-many daripada setelah semua gabungan silang dalam klausul WHERE.

Pertimbangkan kueri berikut:

SELECT Count(1) AS Count
FROM c
JOIN t IN c.tags
JOIN n IN c.nutrients
JOIN s IN c.servings
WHERE t.name = 'infant formula' AND (n.nutritionValue > 0 
AND n.nutritionValue < 10) AND s.amount > 1

Untuk kueri ini, indeks akan mencocokkan setiap dokumen yang memiliki tag dengan nama "rumus formulasi." Item ini adalah item nutrisi dengan nilai antara 0 dan 10, dan item penyajian dengan jumlah yang lebih besar dari 1. Ekspresi JOIN di sini akan melakukan silang produk dari semua item tag, nutrisi, dan array sajian untuk setiap dokumen yang cocok sebelum filter diterapkan.

Klausul WHERE kemudian akan menerapkan predikat filter pada setiap tuple <c, t, n, s>. Misalnya, jika dokumen yang cocok memiliki 10 item di masing-masing dari tiga array, dokumen tersebut akan berkembang menjadi 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 Count(1) AS Count
FROM c
JOIN (SELECT VALUE t FROM t IN c.tags WHERE t.name = 'infant formula')
JOIN (SELECT VALUE n FROM n IN c.nutrients WHERE n.nutritionValue > 0 AND n.nutritionValue < 10)
JOIN (SELECT VALUE s FROM s IN c.servings WHERE s.amount > 1)

Misalnya hanya satu item dalam array tag yang cocok dengan filter, dan ada lima item untuk nutrisi dan array porsi. Ekspresi JOIN kemudian akan 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 ekspresi JOIN untuk mengevaluasi ekspresi sekali tetapi mereferensikannya berkali-kali.

Kueri berikut menjalankan UDF GetMaxNutritionValue dua kali:

SELECT c.id, udf.GetMaxNutritionValue(c.nutrients) AS MaxNutritionValue
FROM c
WHERE udf.GetMaxNutritionValue(c.nutrients) > 100

Berikut ini kueri yang setara yang menjalankan UDF hanya sekali:

SELECT TOP 1000 c.id, MaxNutritionValue
FROM c
JOIN (SELECT VALUE udf.GetMaxNutritionValue(c.nutrients)) MaxNutritionValue
WHERE MaxNutritionValue > 100

Catatan

Ingat perilaku lintas produk dari ekspresi JOIN. Jika ekspresi UDF dapat mengevaluasi ke tidak terdefinisi, Anda harus memastikan bahwa ekspresi JOIN selalu menghasilkan satu baris dengan mengembalikan objek dari subkueri dan bukan nilai secara langsung.

Berikut ini contoh serupa yang mengembalikan objek daripada nilai:

SELECT TOP 1000 c.id, m.MaxNutritionValue
FROM c
JOIN (SELECT udf.GetMaxNutritionValue(c.nutrients) AS MaxNutritionValue) m
WHERE m.MaxNutritionValue > 100

Pendekatannya tidak terbatas pada UDF. Ini berlaku untuk semua kemungkinan ekspresi yang mahal. Misalnya, Anda dapat mengambil pendekatan yang sama dengan fungsi matematika avg:

SELECT TOP 1000 c.id, AvgNutritionValue
FROM c
JOIN (SELECT VALUE avg(n.nutritionValue) FROM n IN c.nutrients) AvgNutritionValue
WHERE AvgNutritionValue > 80

Meniru JOIN dengan data referensi eksternal

Anda mungkin sering perlu mereferensikan data statis yang jarang berubah, seperti satuan pengukuran atau kode negara. Lebih baik tidak menduplikasi data tersebut untuk setiap dokumen. Menghindari duplikasi ini akan menghemat penyimpanan dan meningkatkan kinerja tulis dengan menjaga ukuran dokumen lebih kecil. Anda dapat menggunakan subkueri untuk meniru semantik gabungan dalam dengan kumpulan data referensi.

Misalnya, pertimbangkan kumpulan data referensi ini:

Unit 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
nJ Nanojoule 1.00E-09 Joule
µJ Mikrojoule 1.00E-06 Joule
mJ Millijoule 1.00E-03 Joule
J Joule 1.00E+00 Joule
kJ Kilojoule 1.00E+03 Joule
MJ Megajoule 1.00E+06 Joule
GJ Gigajoule 1.00E+09 Joule
kal Kalori 1.00E+00 kalori
Kkal Kalori 1.00E+03 kalori
IU Unit internasional

Kueri berikut ini meniru bergabung dengan data ini sehingga Anda menambahkan nama unit ke keluaran:

SELECT TOP 10 n.id, n.description, n.nutritionValue, n.units, r.name
FROM food
JOIN n IN food.nutrients
JOIN r 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'},
        {unit: 'nJ', name: 'nanojoule', multiplier: 0.000000001, baseUnit: 'joule'},
        {unit: 'µJ', name: 'microjoule', multiplier: 0.000001, baseUnit: 'joule'},
        {unit: 'mJ', name: 'millijoule', multiplier: 0.001, baseUnit: 'joule'},
        {unit: 'J', name: 'joule', multiplier: 1, baseUnit: 'joule'},
        {unit: 'kJ', name: 'kilojoule', multiplier: 1000, baseUnit: 'joule'},
        {unit: 'MJ', name: 'megajoule', multiplier: 1000000, baseUnit: 'joule'},
        {unit: 'GJ', name: 'gigajoule', multiplier: 1000000000, baseUnit: 'joule'},
        {unit: 'cal', name: 'calorie', multiplier: 1, baseUnit: 'calorie'},
        {unit: 'kcal', name: 'Calorie', multiplier: 1000, baseUnit: 'calorie'},
        {unit: 'IU', name: 'International units'}
    ]
)
WHERE n.units = r.unit

Subkueri skalar

Ekspresi subkueri skalar adalah subkueri yang dievaluasi ke nilai tunggal. Nilai ekspresi subkueri skalar adalah nilai proyeksi (klausul SELECT) dari 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 klausul SELECT dan WHERE.

Namun, menggunakan subkueri skalar tidak selalu membantu mengoptimalkannya. Misalnya, meneruskan subkueri skalar sebagai argumen ke sistem atau fungsi yang ditentukan pengguna tidak berguna dalam 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 yang berkorelasi yang memiliki klausul SELECT 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.

Berikut beberapa contohnya:

Contoh 1

SELECT 1 AS a, 2 AS b

Anda dapat menulis ulang kueri ini, dengan menggunakan subkueri skalar ekspresi sederhana, untuk:

SELECT (SELECT VALUE 1) AS a, (SELECT VALUE 2) AS b

Kedua kueri menghasilkan keluaran ini:

[
  { "a": 1, "b": 2 }
]

Contoh 2

SELECT TOP 5 Concat('id_', f.id) AS id
FROM food f

Anda dapat menulis ulang kueri ini, dengan menggunakan subkueri skalar ekspresi sederhana, untuk:

SELECT TOP 5 (SELECT VALUE Concat('id_', f.id)) AS id
FROM food f

Keluaran kueri:

[
  { "id": "id_03226" },
  { "id": "id_03227" },
  { "id": "id_03228" },
  { "id": "id_03229" },
  { "id": "id_03230" }
]

Contoh 3

SELECT TOP 5 f.id, Contains(f.description, 'fruit') = true ? f.description : undefined
FROM food f

Anda dapat menulis ulang kueri ini, dengan menggunakan subkueri skalar ekspresi sederhana, untuk:

SELECT TOP 10 f.id, (SELECT f.description WHERE Contains(f.description, 'fruit')).description
FROM food f

Keluaran kueri:

[
  { "id": "03230" },
  { "id": "03238", "description":"Babyfood, dessert, tropical fruit, junior" },
  { "id": "03229" },
  { "id": "03226", "description":"Babyfood, dessert, fruit pudding, orange, strained" },
  { "id": "03227" }
]

Subkueri skalar agregat

Subkueri skalar agregat adalah subkueri yang memiliki fungsi agregat dalam proyeksi atau filternya yang mengevaluasi ke nilai tunggal.

Contoh 1:

Berikut adalah subkueri dengan satu ekspresi fungsi agregat dalam proyeksinya:

SELECT TOP 5 
    f.id, 
    (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg'
) AS count_mg
FROM food f

Keluaran kueri:

[
  { "id": "03230", "count_mg": 13 },
  { "id": "03238", "count_mg": 14 },
  { "id": "03229", "count_mg": 13 },
  { "id": "03226", "count_mg": 15 },
  { "id": "03227", "count_mg": 19 }
]

Contoh 2

Berikut adalah subkueri dengan beberapa ekspresi fungsi agregat:

SELECT TOP 5 f.id, (
    SELECT Count(1) AS count, Sum(n.nutritionValue) AS sum 
    FROM n IN f.nutrients 
    WHERE n.units = 'mg'
) AS unit_mg
FROM food f

Keluaran kueri:

[
  { "id": "03230","unit_mg": { "count": 13,"sum": 147.072 } },
  { "id": "03238","unit_mg": { "count": 14,"sum": 107.385 } },
  { "id": "03229","unit_mg": { "count": 13,"sum": 141.579 } },
  { "id": "03226","unit_mg": { "count": 15,"sum": 183.91399999999996 } },
  { "id": "03227","unit_mg": { "count": 19,"sum": 94.788999999999987 } }
]

Contoh 3

Berikut ini kueri dengan subkueri agregat dalam proyeksi dan filter:

SELECT TOP 5 
    f.id, 
	(SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') AS count_mg
FROM food f
WHERE (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') > 20

Keluaran kueri:

[
  { "id": "03235", "count_mg": 27 },
  { "id": "03246", "count_mg": 21 },
  { "id": "03267", "count_mg": 21 },
  { "id": "03269", "count_mg": 21 },
  { "id": "03274", "count_mg": 21 }
]

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 TOP 5 f.id, count_mg
FROM food f
JOIN (SELECT VALUE Count(1) FROM n IN f.nutrients WHERE n.units = 'mg') AS count_mg
WHERE count_mg > 20

ekspresi EXISTS

Azure Cosmos DB mendukung ekspresi EXISTS. Ini adalah subkueri skalar agregat yang dibangun ke dalam Azure Cosmos DB SQL API. EXISTS adalah ekspresi Boolean yang mengambil ekspresi subkueri dan mengembalikan true jika subkueri mengembalikan baris apa pun. Jika tidak, akan kembali palsu.

Karena Azure Cosmos DB SQL API tidak membedakan antara ekspresi Boolean dan ekspresi skalar lainnya, Anda dapat menggunakan EXISTS dalam klausul SELECT dan WHERE. Ini tidak seperti T-SQL, saat ekspresi Boolean (misalnya, EXISTS, BETWEEN, dan IN) dibatasi filter.

Jika subkueri EXISTS mengembalikan nilai tunggal yang tidak terdefinisi, EXISTS akan mengevaluasi ke false. Misalnya, pertimbangkan kueri berikut yang dievaluasi ke false:

SELECT EXISTS (SELECT VALUE undefined)

Jika kata kunci VALUE di subkueri sebelumnya dihilangkan, kueri akan mengevaluasi ke true:

SELECT EXISTS (SELECT undefined) 

Subkueri akan mengapit daftar nilai dalam daftar yang dipilih dalam objek. Jika daftar yang dipilih tidak memiliki nilai, subkueri akan mengembalikan nilai tunggal ‘{}’. Nilai ini ditentukan, sehingga EXISTS akan mengevaluasi ke true.

Contoh: Menulis ulang ARRAY_CONTAINS dan JOIN sebagai EXISTS

Kasus penggunaan umum ARRAY_CONTAINS adalah memfilter dokumen dengan keberadaan item dalam array. Dalam hal ini, kami sedang memeriksa untuk melihat apakah array tag berisi item bernama "oranye".

SELECT TOP 5 f.id, f.tags
FROM food f
WHERE ARRAY_CONTAINS(f.tags, {name: 'orange'})

Anda bisa menulis ulang kueri yang sama untuk menggunakan EXISTS:

SELECT TOP 5 f.id, f.tags
FROM food f
WHERE EXISTS(SELECT VALUE t FROM t IN f.tags WHERE t.name = 'orange')

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.

Pertimbangkan kueri berikut yang memfilter berdasarkan unit dan properti nutritionValue dalam array:

SELECT VALUE c.description
FROM c
JOIN n IN c.nutrients
WHERE n.units= "mg" AND n.nutritionValue > 0

Untuk setiap dokumen dalam koleksi, produk silang dilakukan dengan elemen array-nya. Operasi JOIN ini memungkinkan penerapan filter pada properti di dalam array. Namun, konsumsi RU kueri ini akan signifikan. Misalnya, jika 1.000 dokumen memiliki 100 item di setiap array, maka akan berkembang menjadi 1.000 x 100 (yaitu, 100.000) tuple.

Menggunakan EXISTS dapat membantu mencegah lintas produk yang mahal ini:

SELECT VALUE c.description
FROM c
WHERE EXISTS(
    SELECT VALUE n
    FROM n IN c.nutrients
    WHERE n.units = "mg" AND n.nutritionValue > 0
)

Dalam hal ini, Anda memfilter elemen array dalam subkueri EXISTS. Jika elemen array cocok dengan filter, maka Anda memproyeksikannya dan EXISTS akan mengevaluasi ke true.

Anda juga dapat menyamarkan EXISTS dan mereferensikannya dalam proyeksi:

SELECT TOP 1 c.description, EXISTS(
    SELECT VALUE n
    FROM n IN c.nutrients
    WHERE n.units = "mg" AND n.nutritionValue > 0) as a
FROM c

Keluaran kueri:

[
    {
        "description": "Babyfood, dessert, fruit pudding, orange, strained",
        "a": true
    }
]

ekspresi array

Anda bisa menggunakan ekspresi ARRAY untuk memproyeksikan hasil kueri sebagai array. Anda hanya bisa menggunakan ekspresi ini di dalam klausul SELECT kueri.

SELECT TOP 1   f.id, ARRAY(SELECT VALUE t.name FROM t in f.tags) AS tagNames
FROM  food f

Keluaran kueri:

[
    {
        "id": "03238",
        "tagNames": [
            "babyfood",
            "dessert",
            "tropical fruit",
            "junior"
        ]
    }
]

Seperti halnya subkueri lainnya, filter dengan ekspresi ARRAY dimungkinkan.

SELECT TOP 1 c.id, ARRAY(SELECT VALUE t FROM t in c.tags WHERE t.name != 'infant formula') AS tagNames
FROM c

Keluaran kueri:

[
    {
        "id": "03226",
        "tagNames": [
            {
                "name": "babyfood"
            },
            {
                "name": "dessert"
            },
            {
                "name": "fruit pudding"
            },
            {
                "name": "orange"
            },
            {
                "name": "strained"
            }
        ]
    }
]

Ekspresi array juga dapat muncul setelah klausul FROM dalam subkueri.

SELECT TOP 1 c.id, ARRAY(SELECT VALUE t.name FROM t in c.tags) as tagNames
FROM c
JOIN n IN (SELECT VALUE ARRAY(SELECT t FROM t in c.tags WHERE t.name != 'infant formula'))

Keluaran kueri:

[
    {
        "id": "03238",
        "tagNames": [
            "babyfood",
            "dessert",
            "tropical fruit",
            "junior"
        ]
    }
]

Langkah berikutnya