Meneri data bersarang

AWS Clean Roomsmenawarkan akses yang kompatibel dengan SQL ke data relasional dan bersarang.

AWS Clean Roomsmenggunakan notasi putus-putus dan subskrip array untuk navigasi jalur saat mengakses data bersarang. Hal ini juga memungkinkanFROMitem klausa untuk mengulangi array dan digunakan untuk operasi unnest. Topik berikut memberikan deskripsi pola kueri berbeda yang menggabungkan penggunaan tipe data array/struct/map dengan navigasi jalur dan array, unnesting, dan gabungan.

Navigasi

AWS Clean Roomsmemungkinkan navigasi ke dalam array dan struktur menggunakan[...]braket dan notasi titik masing-masing. Selanjutnya, Anda dapat mencampur navigasi ke dalam struktur menggunakan notasi titik dan array menggunakan notasi braket.

SELECT cust.c_orders[0].o_orderkey FROM customer_orders_lineitem AS cust;

Anda dapat menggunakan notasi titik dan tanda kurung di semua jenis kueri, seperti pemfilteran, gabungan, dan agregasi. Anda dapat menggunakan notasi ini dalam kueri di mana biasanya ada referensi kolom.

SELECT count(*) FROM customer_orders_lineitem WHERE c_orders[0].o_orderkey IS NOT NULL;

SELECT c_orders[0].o_orderdate,
       c_orders[0].o_orderstatus,
       count(*)
FROM customer_orders_lineitem
WHERE c_orders[0].o_orderkey IS NOT NULL
GROUP BY c_orders[0].o_orderstatus,
         c_orders[0].o_orderdate
ORDER BY c_orders[0].o_orderdate;

Kueri yang tidak bersarang

Untuk kueri unnest,AWS Clean Roomsmemungkinkan iterasi melalui array. Hal ini dilakukan dengan menavigasi array menggunakan klausa FROM dari query.

SELECT o FROM customer_orders_lineitem c, c.c_orders o;

Sintaks unnesting adalah perpanjangan dari klausa FROM. Dalam SQL standar, klausa FROMx (AS) yberarti bahwayiterasi atas setiap tupel dalam kaitannyax. Dalam hal ini,xmengacu pada hubungan danymengacu pada alias untuk relasix. Demikian pula, sintaks unnesting menggunakan item klausa FROMx (AS) yberarti bahwayiterasi atas setiap nilai dalam ekspresi arrayx. Dalam hal ini,xadalah ekspresi array danyadalah alias untukx.

Operan kiri juga dapat menggunakan notasi titik dan braket untuk navigasi reguler.

SELECT o, l FROM customer_orders_lineitem c, c.c_orders o, o.o_lineitems l;

AWS Clean Roomsjuga mendukung indeks array saat mengulangi array menggunakanATkata kunci. Klausulx AS y AT ziterasi di atas arrayxdan menghasilkan bidangz, yang merupakan indeks array.

SELECT c_name,
       orders.o_orderkey AS orderkey,
       index AS orderkey_index
FROM customer_orders_lineitem c, c.c_orders AS orders AT index 
ORDER BY orderkey_index;
c_name             | orderkey | orderkey_index
-------------------+----------+----------------
Customer#000008251 | 3020007  |        0
Customer#000009452 | 4043971  |        0  (2 rows)

CREATE TABLE bar AS SELECT json_parse('{"scalar_array": [1, 2.3, 45000000]}') AS data;

SELECT index, element FROM bar AS b, b.data.scalar_array AS element AT index;

 index | element
-------+----------
     0 | 1
1 | 2.3
2 | 45000000
(3 rows)

CREATE TABLE foo AS SELECT json_parse('[[1.1, 1.2], [2.1, 2.2], [3.1, 3.2]]') AS multi_level_array;

SELECT array, element FROM foo AS f, f.multi_level_array AS array, array AS element;

   array   | element
-----------+---------
 [1.1,1.2] | 1.1
 [1.1,1.2] | 1.2
 [2.1,2.2] | 2.1
 [2.1,2.2] | 2.2
 [3.1,3.2] | 3.1
 [3.1,3.2] | 3.2
(6 rows)

Semantik longgar

Secara default, operasi navigasi pada nilai data bersarang mengembalikan null alih-alih mengembalikan kesalahan saat navigasi tidak valid. Navigasi objek tidak valid jika nilai data bersarang bukan objek atau jika nilai data bersarang adalah objek tetapi tidak berisi nama atribut yang digunakan dalam kueri.

SELECT c.c_orders.something FROM customer_orders_lineitem c;

Navigasi array mengembalikan null jika nilai data bersarang bukan array atau indeks array di luar batas.

SELECT c.c_orders[1][1] FROM customer_orders_lineitem c;

Jenis introspeksi

Kolom tipe data bersarang mendukung fungsi inspeksi yang mengembalikan tipe dan informasi tipe lainnya tentang nilai. AWS Clean Rooms mendukung fungsi boolean berikut untuk kolom data bersarang:

Semua fungsi ini mengembalikan false jika nilai input adalah null. IS_SCALAR, IS_OBJECT, dan IS_ARRAY saling eksklusif dan mencakup semua nilai yang mungkin kecuali untuk null. Untuk menyimpulkan jenis yang sesuai dengan data,AWS Clean Roomsmenggunakan fungsi JSON_TYPEOF yang mengembalikan tipe (tingkat atas) nilai data bersarang seperti yang ditunjukkan pada contoh berikut:

SELECT JSON_TYPEOF(r_nations) FROM region_nations;
 json_typeof
 ------------- 
array
(1 row)

SELECT JSON_TYPEOF(r_nations[0].n_nationkey) FROM region_nations;
 json_typeof
 -------------
 number