Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris. # Mengkonversi kueri JSON Oracle ke database PostgreSQL SQL *Pinesh Singal dan Lokesh Gurram, Amazon Web Services* ## Ringkasan Proses migrasi untuk berpindah dari lokal ke Amazon Web Services (AWS) Cloud menggunakan AWS Schema Conversion Tool (AWS SCT) untuk mengonversi kode dari database Oracle menjadi database PostgreSQL. Sebagian besar kode secara otomatis dikonversi oleh AWS SCT. Namun, kueri Oracle terkait JSON tidak dikonversi secara otomatis. Mulai dari versi Oracle 12.2, Oracle Database mendukung berbagai fungsi JSON yang membantu dalam mengubah data berbasis JSON menjadi data berbasis Row. Namun, AWS SCT tidak secara otomatis mengonversi data berbasis JSON ke dalam bahasa yang didukung oleh PostgreSQL. Pola migrasi ini terutama berfokus pada mengonversi kueri Oracle terkait JSON secara manual dengan fungsi seperti`JSON_OBJECT`,`JSON_ARRAYAGG`, dan `JSON_TABLE` dari database Oracle ke database PostgreSQL. ## Prasyarat dan batasan **Prasyarat** + Akun AWS yang aktif + Instans database Oracle lokal (aktif dan berjalan) + Amazon Relational Database Service (Amazon RDS) untuk instans database PostgreSQL atau Amazon Aurora PostgreSQL Edisi yang kompatibel dengan PostgreSQL (aktif dan berjalan) **Batasan** + Kueri terkait JSON memerlukan format dan tetap. `KEY` `VALUE` Tidak menggunakan format itu mengembalikan hasil yang salah. + Jika ada perubahan dalam struktur JSON menambahkan baru `KEY` dan `VALUE` pasangan di bagian hasil, prosedur atau fungsi yang sesuai harus diubah dalam kueri SQL. + Beberapa fungsi terkait JSON didukung di versi Oracle dan PostgreSQL sebelumnya tetapi dengan kemampuan yang lebih sedikit. **Versi produk** + Oracle Database versi 12.2 dan yang lebih baru + Amazon RDS untuk PostgreSQL atau Aurora PostgreSQL yang kompatibel dengan versi 9.5 dan yang lebih baru + AWS SCT versi terbaru (diuji menggunakan versi 1.0.664) ## Arsitektur **Tumpukan teknologi sumber** + Sebuah instance database Oracle dengan versi 19c **Tumpukan teknologi target** + Instans database yang kompatibel dengan Amazon RDS for PostgreSQL atau Aurora PostgreSQL dengan versi 13 **Arsitektur target** ![\[Deskripsi mengikuti diagram.\]](http://docs.aws.amazon.com/id_id/prescriptive-guidance/latest/patterns/images/pattern-img/5e2c3b07-9ef5-417f-b049-bcea58f2c3ec/images/2ff8b00b-8849-4ef1-9be1-579f7b51be10.png) 1. Gunakan AWS SCT dengan kode fungsi JSON untuk mengonversi kode sumber dari Oracle ke PostgreSQL. 1. Konversi menghasilkan file.sql bermigrasi yang didukung PostgreSQL. 1. Secara manual mengonversi kode fungsi Oracle JSON yang tidak dikonversi ke kode fungsi PostgreSQL JSON. 1. Jalankan file.sql pada instans DB yang kompatibel dengan Aurora PostgreSQL target. ## Alat **Layanan AWS** + [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) adalah mesin database relasional yang dikelola sepenuhnya yang dibangun untuk cloud dan kompatibel dengan MySQL dan PostgreSQL. + [Amazon Relational Database Service (Amazon RDS) untuk PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) membantu Anda mengatur, mengoperasikan, dan menskalakan database relasional PostgreSQL di AWS Cloud. + [AWS Schema Conversion Tool (AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)) mendukung migrasi database heterogen dengan secara otomatis mengonversi skema basis data sumber dan sebagian besar kode kustom ke format yang kompatibel dengan database target. **Layanan lainnya** + [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) adalah lingkungan pengembangan terintegrasi yang menyederhanakan pengembangan dan pengelolaan database Oracle baik dalam penerapan tradisional maupun berbasis cloud. + pgAdmin atau. DBeaver [pgAdmin](https://www.pgadmin.org/) adalah alat manajemen sumber terbuka untuk PostgreSQL. Ini menyediakan antarmuka grafis yang membantu Anda membuat, memelihara, dan menggunakan objek database. [DBeaver](https://dbeaver.io/)adalah alat database universal. ## Praktik terbaik Kueri Oracle memiliki tipe `CAST` sebagai default saat menggunakan `JSON_TABLE` fungsi. Praktik terbaik adalah menggunakan `CAST` PostgreSQL juga, menggunakan double greater-than characters (). `>>` *Untuk informasi selengkapnya, lihat *PostgreS\$1SQL\$1READ\$1JSON* di bagian Informasi tambahan.* ## Epik ### Menghasilkan data JSON dalam database Oracle dan PostgreSQL | Tugas | Deskripsi | Keterampilan yang dibutuhkan | | --- | --- | --- | | Simpan data JSON dalam database Oracle. | Buat tabel di database Oracle, dan simpan data JSON di kolom. `CLOB`  *Gunakan *Oracle\$1Table\$1Creation\$1Insert\$1Script yang ada di bagian Informasi tambahan*.* | Insinyur migrasi | | Simpan data JSON dalam database PostgreSQL. | Buat tabel di database PostgreSQL, dan simpan data JSON di kolom. `TEXT` *Gunakan *Postgres\$1Table\$1Creation\$1Insert\$1Script yang ada di bagian Informasi* tambahan.* | Insinyur migrasi | ### Ubah JSON menjadi format ROW | Tugas | Deskripsi | Keterampilan yang dibutuhkan | | --- | --- | --- | | Konversi data JSON pada database Oracle. | Tulis query Oracle SQL untuk membaca data JSON ke dalam format ROW. *Untuk detail selengkapnya dan contoh sintaks, lihat *Oracle\$1SQL\$1READ\$1JSON* di bagian Informasi tambahan.* | Insinyur migrasi | | Konversi data JSON pada database PostgreSQL. | Tulis kueri PostgreSQL untuk membaca data JSON ke dalam format ROW. *Untuk detail selengkapnya dan contoh sintaks, lihat *PostgreS\$1SQL\$1READ\$1JSON* di bagian Informasi tambahan.* | Insinyur migrasi | ### Secara manual mengkonversi data JSON menggunakan query SQL dan melaporkan output dalam format JSON | Tugas | Deskripsi | Keterampilan yang dibutuhkan | | --- | --- | --- | | Lakukan agregasi dan validasi pada query Oracle SQL. | Untuk mengonversi data JSON secara manual, lakukan gabungan, agregasi, dan validasi pada kueri Oracle SQL, dan laporkan output dalam format JSON. *Gunakan kode di bawah *Oracle\$1SQL\$1JSON\$1AGGREGATION\$1JOIN di bagian Informasi tambahan*.*[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/id_id/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Insinyur migrasi | | Lakukan agregasi dan validasi pada query Postgres SQL. | Untuk mengonversi data JSON secara manual, lakukan gabungan, agregasi, dan validasi pada kueri PostgreSQL, dan laporkan output dalam format JSON. *Gunakan kode di bawah *Postgres\$1SQL\$1JSON\$1AGGREGATION\$1JOIN* di bagian Informasi tambahan.*[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/id_id/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Insinyur migrasi | ### Ubah prosedur Oracle menjadi fungsi PostgreSQL yang berisi kueri JSON | Tugas | Deskripsi | Keterampilan yang dibutuhkan | | --- | --- | --- | | Ubah kueri JSON dalam prosedur Oracle menjadi baris. | *Untuk contoh prosedur Oracle, gunakan kueri Oracle sebelumnya dan kode di bawah *Oracle\$1procedure \$1with\$1json\$1query* di bagian Informasi tambahan.* | Insinyur migrasi | | Ubah fungsi PostgreSQL yang memiliki kueri JSON menjadi data berbasis baris. | **Untuk contoh fungsi PostgreSQL, gunakan kueri PostgreSQL sebelumnya dan kode yang ada di bawah PostgreS\$1Function\$1With\$1JSON\$1QUERY di bagian Informasi tambahan.** | Insinyur migrasi | ## Sumber daya terkait + [Fungsi Oracle JSON](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html) + [Fungsi PostgreSQL JSON](https://www.postgresql.org/docs/13/functions-json.html) + [Contoh Fungsi Oracle JSON](https://oracle-base.com/articles/12c/sql-json-functions-12cr2) + [Contoh fungsi PostgreSQL JSON](https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg) + [Alat Konversi Skema AWS](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) ## Informasi tambahan Untuk mengonversi kode JSON dari database Oracle ke database PostgreSQL, gunakan skrip berikut, secara berurutan. **1. Oracle\$1Table\$1Creation\$1Insert\$1Script** ``` create table aws_test_table(id number,created_on date default sysdate,modified_on date,json_doc clob); REM INSERTING into EXPORT_TABLE SET DEFINE OFF; Insert into aws_test_table (ID,CREATED_ON,MODIFIED_ON,json_doc) values (1,to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),TO_CLOB(q'[{ "metadata" : { "upperLastNameFirstName" : "ABC XYZ", "upperEmailAddress" : "abc@gmail.com", "profileType" : "P" }, "data" : { "onlineContactId" : "032323323", "displayName" : "Abc, Xyz", "firstName" : "Xyz", "lastName" : "Abc", "emailAddress" : "abc@gmail.com", "productRegistrationStatus" : "Not registered", "positionId" : "0100", "arrayPattern" : " -'", "a]') || TO_CLOB(q'[ccount" : { "companyId" : "SMGE", "businessUnitId" : 7, "accountNumber" : 42000, "parentAccountNumber" : 32000, "firstName" : "john", "lastName" : "doe", "street1" : "retOdertcaShr ", "city" : "new york", "postalcode" : "XY ABC", "country" : "United States" }, "products" : [ { "appUserGuid" : "i0acc4450000001823fbad478e2eab8a0", "id" : "0000000046", ]') || TO_CLOB(q'[ "name" : "ProView", "domain" : "EREADER", "registrationStatus" : false, "status" : "11" } ] } }]')); Insert into aws_test_table (ID,CREATED_ON,MODIFIED_ON,json_doc) values (2,to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),TO_CLOB(q'[{ "metadata" : { "upperLastNameFirstName" : "PQR XYZ", "upperEmailAddress" : "pqr@gmail.com", "profileType" : "P" }, "data" : { "onlineContactId" : "54534343", "displayName" : "Xyz, pqr", "firstName" : "pqr", "lastName" : "Xyz", "emailAddress" : "pqr@gmail.com", "productRegistrationStatus" : "Not registered", "positionId" : "0090", "arrayPattern" : " -'", "account" : { "companyId" : "CARS", "busin]') || TO_CLOB(q'[essUnitId" : 6, "accountNumber" : 42001, "parentAccountNumber" : 32001, "firstName" : "terry", "lastName" : "whitlock", "street1" : "UO 123", "city" : "TOTORON", "region" : "NO", "postalcode" : "LKM 111", "country" : "Canada" }, "products" : [ { "appUserGuid" : "ia744d7790000016899f8cf3f417d6df6", "id" : "0000000014", "name" : "ProView eLooseleaf", ]') || TO_CLOB(q'[ "domain" : "EREADER", "registrationStatus" : false, "status" : "11" } ] } }]')); commit; ``` **2. Postgres\$1Table\$1Creation\$1Insert\$1Script** ``` create table aws_test_pg_table(id int,created_on date ,modified_on date,json_doc text); insert into aws_test_pg_table(id,created_on,modified_on,json_doc) values(1,now(),now(),'{ "metadata" : { "upperLastNameFirstName" : "ABC XYZ", "upperEmailAddress" : "abc@gmail.com", "profileType" : "P" }, "data" : { "onlineContactId" : "032323323", "displayName" : "Abc, Xyz", "firstName" : "Xyz", "lastName" : "Abc", "emailAddress" : "abc@gmail.com", "productRegistrationStatus" : "Not registered", "positionId" : "0100", "arrayPattern" : " -", "account" : { "companyId" : "SMGE", "businessUnitId" : 7, "accountNumber" : 42000, "parentAccountNumber" : 32000, "firstName" : "john", "lastName" : "doe", "street1" : "retOdertcaShr ", "city" : "new york", "postalcode" : "XY ABC", "country" : "United States" }, "products" : [ { "appUserGuid" : "i0acc4450000001823fbad478e2eab8a0", "id" : "0000000046", "name" : "ProView", "domain" : "EREADER", "registrationStatus" : false, "status" : "11" } ] } }'); insert into aws_test_pg_table(id,created_on,modified_on,json_doc) values(2,now(),now(),'{ "metadata" : { "upperLastNameFirstName" : "PQR XYZ", "upperEmailAddress" : "pqr@gmail.com", "profileType" : "P" }, "data" : { "onlineContactId" : "54534343", "displayName" : "Xyz, pqr", "firstName" : "pqr", "lastName" : "Xyz", "emailAddress" : "a*b**@h**.k**", "productRegistrationStatus" : "Not registered", "positionId" : "0090", "arrayPattern" : " -", "account" : { "companyId" : "CARS", "businessUnitId" : 6, "accountNumber" : 42001, "parentAccountNumber" : 32001, "firstName" : "terry", "lastName" : "whitlock", "street1" : "UO 123", "city" : "TOTORON", "region" : "NO", "postalcode" : "LKM 111", "country" : "Canada" }, "products" : [ { "appUserGuid" : "ia744d7790000016899f8cf3f417d6df6", "id" : "0000000014", "name" : "ProView eLooseleaf", "domain" : "EREADER", "registrationStatus" : false, "status" : "11" } ] } }'); ``` **3. Oracle\$1SQL\$1READ\$1JSON** Blok kode berikut menunjukkan bagaimana mengkonversi data Oracle JSON ke dalam format baris. *Contoh query dan sintaks* ``` SELECT JSON_OBJECT( 'accountCounts' VALUE JSON_ARRAYAGG( JSON_OBJECT( 'businessUnitId' VALUE business_unit_id, 'parentAccountNumber' VALUE parent_account_number, 'accountNumber' VALUE account_number, 'totalOnlineContactsCount' VALUE online_contacts_count, 'countByPosition' VALUE JSON_OBJECT( 'taxProfessionalCount' VALUE tax_count, 'attorneyCount' VALUE attorney_count, 'nonAttorneyCount' VALUE non_attorney_count, 'clerkCount' VALUE clerk_count ) ) ) ) FROM (SELECT tab_data.business_unit_id, tab_data.parent_account_number, tab_data.account_number, SUM(1) online_contacts_count, SUM(CASE WHEN tab_data.position_id = '0095' THEN 1 ELSE 0 END) tax_count, SUM(CASE WHEN tab_data.position_id = '0100' THEN 1 ELSE 0 END) attorney_count, SUM(CASE WHEN tab_data.position_id = '0090' THEN 1 ELSE 0 END) non_attorney_count, SUM(CASE WHEN tab_data.position_id = '0050' THEN 1 ELSE 0 END) clerk_count FROM aws_test_table scco,JSON_TABLE ( json_doc, '$' ERROR ON ERROR COLUMNS ( parent_account_number NUMBER PATH '$.data.account.parentAccountNumber', account_number NUMBER PATH '$.data.account.accountNumber', business_unit_id NUMBER PATH '$.data.account.businessUnitId', position_id VARCHAR2 ( 4 ) PATH '$.data.positionId' ) ) AS tab_data INNER JOIN JSON_TABLE ( '{ "accounts": [{ "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }, { "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }] }', '$.accounts[*]' ERROR ON ERROR COLUMNS ( parent_account_number PATH '$.parentAccountNumber', account_number PATH '$.accountNumber', business_unit_id PATH '$.businessUnitId') ) static_data ON ( static_data.parent_account_number = tab_data.parent_account_number AND static_data.account_number = tab_data.account_number AND static_data.business_unit_id = tab_data.business_unit_id ) GROUP BY tab_data.business_unit_id, tab_data.parent_account_number, tab_data.account_number ); ``` Dokumen JSON menyimpan data sebagai koleksi. Setiap koleksi dapat memiliki `KEY` dan `VALUE` berpasangan. Setiap `VALUE` bisa bersarang `KEY` dan `VALUE` berpasangan. Tabel berikut memberikan informasi tentang membaca spesifik `VALUE` dari dokumen JSON. | | | KUNCI | HIERARKI atau PATH yang akan digunakan untuk mendapatkan NILAI | NILAI | | --- |--- |--- | | `profileType` | `metadata` -> `profileType` | “P” | | `positionId` | `data` -> `positionId` | “0100" | | `accountNumber` | `data`-> akun -> `accountNumber` | 42000 | Pada tabel sebelumnya, `KEY` `profileType` adalah a `VALUE` dari `metadata``KEY`. `KEY``positionId`Itu adalah `VALUE` dari `data``KEY`. `KEY``accountNumber`Itu adalah `VALUE` dari `account``KEY`, dan itu `account` `KEY` adalah `VALUE` dari `data``KEY`. *Contoh dokumen JSON* ``` { "metadata" : { "upperLastNameFirstName" : "ABC XYZ", "upperEmailAddress" : "abc@gmail.com", "profileType" : "P" }, "data" : { "onlineContactId" : "032323323", "displayName" : "Abc, Xyz", "firstName" : "Xyz", "lastName" : "Abc", "emailAddress" : "abc@gmail.com", "productRegistrationStatus" : "Not registered", "positionId" : "0100", "arrayPattern" : " -", "account" : { "companyId" : "SMGE", "businessUnitId" : 7, "accountNumber" : 42000, "parentAccountNumber" : 32000, "firstName" : "john", "lastName" : "doe", "street1" : "retOdertcaShr ", "city" : "new york", "postalcode" : "XY ABC", "country" : "United States" }, "products" : [ { "appUserGuid" : "i0acc4450000001823fbad478e2eab8a0", "id" : "0000000046", "name" : "ProView", "domain" : "EREADER", "registrationStatus" : false, "status" : "11" } ] } } ``` *Kueri SQL yang digunakan untuk mendapatkan bidang yang dipilih dari dokumen JSON* ``` select parent_account_number,account_number,business_unit_id,position_id from aws_test_table aws,JSON_TABLE ( json_doc, '$' ERROR ON ERROR COLUMNS ( parent_account_number NUMBER PATH '$.data.account.parentAccountNumber', account_number NUMBER PATH '$.data.account.accountNumber', business_unit_id NUMBER PATH '$.data.account.businessUnitId', position_id VARCHAR2 ( 4 ) PATH '$.data.positionId' )) as sc ``` Dalam query sebelumnya, `JSON_TABLE` adalah fungsi built-in di Oracle yang mengubah data JSON ke dalam format baris. Fungsi JSON\$1TABLE mengharapkan parameter dalam format JSON. Setiap item di `COLUMNS` memiliki standar`PATH`, dan ada yang sesuai `VALUE` untuk yang `KEY` diberikan dikembalikan dalam format baris. *Hasil dari query sebelumnya* | | | PARENT\$1ACCOUNT\$1NUMBER | ACCOUNT\$1NUMBER | BUSINESS\$1UNIT\$1ID | POSISI\$1ID | | --- |--- |--- |--- | | 32000 | 42000 | 7 | 0100 | | 32001 | 42001 | 6 | 0090 | **4. PostgreS\$1SQL\$1READ\$1JSON** *****Contoh query dan sintaks* ``` select * from ( select (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER as parentAccountNumber, (json_doc::json->'data'->'account'->>'accountNumber')::INTEGER as accountNumber, (json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER as businessUnitId, (json_doc::json->'data'->>'positionId')::VARCHAR as positionId from aws_test_pg_table) d ; ``` Di Oracle, `PATH` digunakan untuk mengidentifikasi spesifik `KEY` dan`VALUE`. Namun, PostgreSQL menggunakan model untuk membaca dan dari `HIERARCHY` JSON. `KEY` `VALUE` Data JSON yang sama yang disebutkan di bawah `Oracle_SQL_Read_JSON` digunakan dalam contoh berikut. *Kueri SQL dengan tipe CAST tidak diizinkan* (Jika Anda memaksa mengetik`CAST`, kueri gagal dengan kesalahan sintaks.) ``` select * from ( select (json_doc::json->'data'->'account'->'parentAccountNumber') as parentAccountNumber, (json_doc::json->'data'->'account'->'accountNumber')as accountNumber, (json_doc::json->'data'->'account'->'businessUnitId') as businessUnitId, (json_doc::json->'data'->'positionId')as positionId from aws_test_pg_table) d ; ``` Menggunakan satu operator yang lebih besar dari (`>`) akan mengembalikan yang `VALUE` ditentukan untuk itu. `KEY` Misalnya,`KEY`:`positionId`, dan`VALUE`:`"0100"`. Jenis tidak `CAST` diperbolehkan saat Anda menggunakan operator tunggal yang lebih besar dari ()`>`. *Kueri SQL dengan tipe CAST diperbolehkan* ``` select * from ( select (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER as parentAccountNumber, (json_doc::json->'data'->'account'->>'accountNumber')::INTEGER as accountNumber, (json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER as businessUnitId, (json_doc::json->'data'->>'positionId')::varchar as positionId from aws_test_pg_table) d ; ``` Untuk menggunakan tipe`CAST`, Anda harus menggunakan operator ganda yang lebih besar dari pada. Jika Anda menggunakan operator tunggal yang lebih besar dari pada, kueri mengembalikan yang `VALUE` ditentukan (misalnya,`KEY`:`positionId`, dan`VALUE`:`"0100"`). Menggunakan double greater-than operator (`>>`) akan mengembalikan nilai aktual yang ditentukan untuk itu `KEY` (misalnya,`KEY`:, dan`VALUE`: `positionId``0100`, tanpa tanda kutip ganda). Dalam kasus sebelumnya, `parentAccountNumber` adalah type `CAST` to`INT`, `accountNumber` is type `CAST` to`INT`, `businessUnitId` is type `CAST` to`INT`, dan `positionId` type `CAST` to. `VARCHAR` Tabel berikut menunjukkan hasil kueri yang menjelaskan peran operator tunggal yang lebih besar dari (`>`) dan operator ganda yang lebih besar dari (). `>>` Pada tabel tabel pertama, kueri menggunakan single greater-than operator ()`>`. Setiap kolom dalam tipe JSON dan tidak dapat diubah menjadi tipe data lain. | | | parentAccountNumber | accountNumber | businessUnitId | PositionId | | --- |--- |--- |--- | | 2003565430 | 2003564830 | 7 | “0100" | | 2005284042 | 2005284042 | 6 | “0090" | | 2000272719 | 2000272719 | 1 | “0100" | Pada tabel kedua, query menggunakan double greater-than operator ()`>>`. Setiap kolom mendukung jenis `CAST` berdasarkan nilai kolom. Misalnya, `INTEGER` dalam konteks ini. | | | parentAccountNumber | accountNumber | businessUnitId | PositionId | | --- |--- |--- |--- | | 2003565430 | 2003564830 | 7 | 0100 | | 2005284042 | 2005284042 | 6 | 0090 | | 2000272719 | 2000272719 | 1 | 0100 | **5. ORACLE\$1SQL\$1JSON\$1AGGREGATION\$1BERGABUNG** *Contoh kueri* ``` SELECT JSON_OBJECT( 'accountCounts' VALUE JSON_ARRAYAGG( JSON_OBJECT( 'businessUnitId' VALUE business_unit_id, 'parentAccountNumber' VALUE parent_account_number, 'accountNumber' VALUE account_number, 'totalOnlineContactsCount' VALUE online_contacts_count, 'countByPosition' VALUE JSON_OBJECT( 'taxProfessionalCount' VALUE tax_count, 'attorneyCount' VALUE attorney_count, 'nonAttorneyCount' VALUE non_attorney_count, 'clerkCount' VALUE clerk_count ) ) ) ) FROM (SELECT tab_data.business_unit_id, tab_data.parent_account_number, tab_data.account_number, SUM(1) online_contacts_count, SUM(CASE WHEN tab_data.position_id = '0095' THEN 1 ELSE 0 END) tax_count, SUM(CASE WHEN tab_data.position_id = '0100' THEN 1 ELSE 0 END) attorney_count, SUM(CASE WHEN tab_data.position_id = '0090' THEN 1 ELSE 0 END) non_attorney_count, SUM(CASE WHEN tab_data.position_id = '0050' THEN 1 ELSE 0 END) clerk_count FROM aws_test_table scco,JSON_TABLE ( json_doc, '$' ERROR ON ERROR COLUMNS ( parent_account_number NUMBER PATH '$.data.account.parentAccountNumber', account_number NUMBER PATH '$.data.account.accountNumber', business_unit_id NUMBER PATH '$.data.account.businessUnitId', position_id VARCHAR2 ( 4 ) PATH '$.data.positionId' ) ) AS tab_data INNER JOIN JSON_TABLE ( '{ "accounts": [{ "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }, { "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }] }', '$.accounts[*]' ERROR ON ERROR COLUMNS ( parent_account_number PATH '$.parentAccountNumber', account_number PATH '$.accountNumber', business_unit_id PATH '$.businessUnitId') ) static_data ON ( static_data.parent_account_number = tab_data.parent_account_number AND static_data.account_number = tab_data.account_number AND static_data.business_unit_id = tab_data.business_unit_id ) GROUP BY tab_data.business_unit_id, tab_data.parent_account_number, tab_data.account_number ); ``` Untuk mengonversi data tingkat baris ke dalam format JSON, Oracle memiliki fungsi bawaan seperti`JSON_OBJECT`,,, `JSON_ARRAY` dan. `JSON_OBJECTAGG` `JSON_ARRAYAGG` + `JSON_OBJECT`menerima dua parameter: `KEY` dan`VALUE`. `KEY`Parameter harus di-hardcode atau statis. `VALUE`Parameter ini berasal dari output tabel. + `JSON_ARRAYAGG`menerima `JSON_OBJECT` sebagai parameter. Ini membantu dalam mengelompokkan kumpulan `JSON_OBJECT` elemen sebagai daftar. Misalnya, jika Anda memiliki `JSON_OBJECT` elemen yang memiliki beberapa catatan (beberapa `KEY` dan `VALUE` pasangan dalam kumpulan data), `JSON_ARRAYAGG` tambahkan kumpulan data dan buat daftar. Menurut bahasa Struktur Data, `LIST` adalah sekelompok elemen. Dalam konteks ini, `LIST` adalah sekelompok `JSON_OBJECT` elemen. Contoh berikut menunjukkan satu `JSON_OBJECT` elemen. ``` { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } ``` Contoh berikutnya menunjukkan dua `JSON_OBJECT` elemen, dengan `LIST` ditunjukkan oleh kawat gigi persegi (`[ ]`). ``` [ { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } , { "taxProfessionalCount": 2, "attorneyCount": 1, "nonAttorneyCount": 3, "clerkCount":4 } ] ``` *Contoh query SQL* ``` SELECT JSON_OBJECT( 'accountCounts' VALUE JSON_ARRAYAGG( JSON_OBJECT( 'businessUnitId' VALUE business_unit_id, 'parentAccountNumber' VALUE parent_account_number, 'accountNumber' VALUE account_number, 'totalOnlineContactsCount' VALUE online_contacts_count, 'countByPosition' VALUE JSON_OBJECT( 'taxProfessionalCount' VALUE tax_count, 'attorneyCount' VALUE attorney_count, 'nonAttorneyCount' VALUE non_attorney_count, 'clerkCount' VALUE clerk_count ) ) ) ) FROM (SELECT tab_data.business_unit_id, tab_data.parent_account_number, tab_data.account_number, SUM(1) online_contacts_count, SUM(CASE WHEN tab_data.position_id = '0095' THEN 1 ELSE 0 END ) tax_count, SUM(CASE WHEN tab_data.position_id = '0100' THEN 1 ELSE 0 END ) attorney_count, SUM(CASE WHEN tab_data.position_id = '0090' THEN 1 ELSE 0 END ) non_attorney_count, SUM(CASE WHEN tab_data.position_id = '0050' THEN 1 ELSE 0 END ) clerk_count FROM aws_test_table scco, JSON_TABLE ( json_doc, '$' ERROR ON ERROR COLUMNS ( parent_account_number NUMBER PATH '$.data.account.parentAccountNumber', account_number NUMBER PATH '$.data.account.accountNumber', business_unit_id NUMBER PATH '$.data.account.businessUnitId', position_id VARCHAR2 ( 4 ) PATH '$.data.positionId' ) ) AS tab_data INNER JOIN JSON_TABLE ( '{ "accounts": [{ "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }, { "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }] }', '$.accounts[*]' ERROR ON ERROR COLUMNS ( parent_account_number PATH '$.parentAccountNumber', account_number PATH '$.accountNumber', business_unit_id PATH '$.businessUnitId') ) static_data ON ( static_data.parent_account_number = tab_data.parent_account_number AND static_data.account_number = tab_data.account_number AND static_data.business_unit_id = tab_data.business_unit_id ) GROUP BY tab_data.business_unit_id, tab_data.parent_account_number, tab_data.account_number ); ``` *Contoh output dari query SQL sebelumnya* ``` { "accountCounts": [ { "businessUnitId": 6, "parentAccountNumber": 32001, "accountNumber": 42001, "totalOnlineContactsCount": 1, "countByPosition": { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } }, { "businessUnitId": 7, "parentAccountNumber": 32000, "accountNumber": 42000, "totalOnlineContactsCount": 1, "countByPosition": { "taxProfessionalCount": 0, "attorneyCount": 1, "nonAttorneyCount": 0, "clerkCount": 0 } } ] } ``` **6.  PostgreS\$1SQL\$1JSON\$1AGGREGATION\$1BERGABUNG** PostgreSQL built-in `JSON_BUILD_OBJECT` berfungsi `JSON_AGG` dan mengkonversi data tingkat baris ke dalam format JSON.  `JSON_BUILD_OBJECT`PostgreSQL `JSON_AGG` dan setara dengan Oracle dan. `JSON_OBJECT` `JSON_ARRAYAGG` *Contoh kueri* ``` select JSON_BUILD_OBJECT ('accountCounts', JSON_AGG( JSON_BUILD_OBJECT ('businessUnitId',businessUnitId ,'parentAccountNumber',parentAccountNumber ,'accountNumber',accountNumber ,'totalOnlineContactsCount',online_contacts_count, 'countByPosition', JSON_BUILD_OBJECT ( 'taxProfessionalCount',tax_professional_count ,'attorneyCount',attorney_count ,'nonAttorneyCount',non_attorney_count ,'clerkCount',clerk_count ) ) ) ) from ( with tab as (select * from ( select (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER as parentAccountNumber, (json_doc::json->'data'->'account'->>'accountNumber')::INTEGER as accountNumber, (json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER as businessUnitId, (json_doc::json->'data'->>'positionId')::varchar as positionId from aws_test_pg_table) a ) , tab1 as ( select (json_array_elements(b.jc -> 'accounts') ->> 'accountNumber')::integer accountNumber, (json_array_elements(b.jc -> 'accounts') ->> 'businessUnitId')::integer businessUnitId, (json_array_elements(b.jc -> 'accounts') ->> 'parentAccountNumber')::integer parentAccountNumber from ( select '{ "accounts": [{ "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }, { "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }] }'::json as jc) b) select tab.businessUnitId::text, tab.parentAccountNumber::text, tab.accountNumber::text, SUM(1) online_contacts_count, SUM(CASE WHEN tab.positionId::text = '0095' THEN 1 ELSE 0 END) tax_professional_count, SUM(CASE WHEN tab.positionId::text = '0100' THEN 1 ELSE 0 END) attorney_count, SUM(CASE WHEN tab.positionId::text = '0090' THEN 1 ELSE 0 END) non_attorney_count, SUM(CASE WHEN tab.positionId::text = '0050' THEN 1 ELSE 0 END) clerk_count from tab1,tab where tab.parentAccountNumber::INTEGER=tab1.parentAccountNumber::INTEGER and tab.accountNumber::INTEGER=tab1.accountNumber::INTEGER and tab.businessUnitId::INTEGER=tab1.businessUnitId::INTEGER GROUP BY tab.businessUnitId::text, tab.parentAccountNumber::text, tab.accountNumber::text) a; ``` *Contoh output dari query sebelumnya* Output dari Oracle dan PostgreSQL persis sama. ``` { "accountCounts": [ { "businessUnitId": 6, "parentAccountNumber": 32001, "accountNumber": 42001, "totalOnlineContactsCount": 1, "countByPosition": { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } }, { "businessUnitId": 7, "parentAccountNumber": 32000, "accountNumber": 42000, "totalOnlineContactsCount": 1, "countByPosition": { "taxProfessionalCount": 0, "attorneyCount": 1, "nonAttorneyCount": 0, "clerkCount": 0 } } ] } ``` **7.ORACLE\$1PROCEDURE\$1with\$1JSON\$1QUERY** Kode ini mengubah prosedur Oracle menjadi fungsi PostgreSQL yang memiliki query JSON SQL. Ini menunjukkan bagaimana query mentransposisi JSON menjadi baris dan sebaliknya. ``` CREATE OR REPLACE PROCEDURE p_json_test(p_in_accounts_json IN varchar2, p_out_accunts_json OUT varchar2) IS BEGIN /* p_in_accounts_json paramter should have following format: { "accounts": [{ "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }, { "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }] } */ SELECT JSON_OBJECT( 'accountCounts' VALUE JSON_ARRAYAGG( JSON_OBJECT( 'businessUnitId' VALUE business_unit_id, 'parentAccountNumber' VALUE parent_account_number, 'accountNumber' VALUE account_number, 'totalOnlineContactsCount' VALUE online_contacts_count, 'countByPosition' VALUE JSON_OBJECT( 'taxProfessionalCount' VALUE tax_count, 'attorneyCount' VALUE attorney_count, 'nonAttorneyCount' VALUE non_attorney_count, 'clerkCount' VALUE clerk_count ) ) ) ) into p_out_accunts_json FROM (SELECT tab_data.business_unit_id, tab_data.parent_account_number, tab_data.account_number, SUM(1) online_contacts_count, SUM(CASE WHEN tab_data.position_id = '0095' THEN 1 ELSE 0 END) tax_count, SUM(CASE WHEN tab_data.position_id = '0100' THEN 1 ELSE 0 END) attorney_count, SUM(CASE WHEN tab_data.position_id = '0090' THEN 1 ELSE 0 END) non_attorney_count, SUM(CASE WHEN tab_data.position_id = '0050' THEN 1 ELSE 0 END) clerk_count FROM aws_test_table scco,JSON_TABLE ( json_doc, '$' ERROR ON ERROR COLUMNS ( parent_account_number NUMBER PATH '$.data.account.parentAccountNumber', account_number NUMBER PATH '$.data.account.accountNumber', business_unit_id NUMBER PATH '$.data.account.businessUnitId', position_id VARCHAR2 ( 4 ) PATH '$.data.positionId' ) ) AS tab_data INNER JOIN JSON_TABLE ( p_in_accounts_json, '$.accounts[*]' ERROR ON ERROR COLUMNS ( parent_account_number PATH '$.parentAccountNumber', account_number PATH '$.accountNumber', business_unit_id PATH '$.businessUnitId') ) static_data ON ( static_data.parent_account_number = tab_data.parent_account_number AND static_data.account_number = tab_data.account_number AND static_data.business_unit_id = tab_data.business_unit_id ) GROUP BY tab_data.business_unit_id, tab_data.parent_account_number, tab_data.account_number ); EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'Error while running the JSON query'); END; / ``` *Menjalankan prosedur* Blok kode berikut menjelaskan bagaimana Anda dapat menjalankan prosedur Oracle yang dibuat sebelumnya dengan contoh input JSON ke prosedur. Ini juga memberi Anda hasil atau output dari prosedur ini. ``` set serveroutput on; declare v_out varchar2(30000); v_in varchar2(30000):= '{ "accounts": [{ "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }, { "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }] }'; begin p_json_test(v_in,v_out); dbms_output.put_line(v_out); end; / ``` *Keluaran prosedur* ``` { "accountCounts": [ { "businessUnitId": 6, "parentAccountNumber": 32001, "accountNumber": 42001, "totalOnlineContactsCount": 1, "countByPosition": { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } }, { "businessUnitId": 7, "parentAccountNumber": 32000, "accountNumber": 42000, "totalOnlineContactsCount": 1, "countByPosition": { "taxProfessionalCount": 0, "attorneyCount": 1, "nonAttorneyCount": 0, "clerkCount": 0 } } ] } ``` **8.PostgreS\$1Function\$1with\$1JSON\$1QUERY** *Contoh fungsi* ``` CREATE OR REPLACE FUNCTION f_pg_json_test(p_in_accounts_json text) RETURNS text LANGUAGE plpgsql AS $$ DECLARE v_out_accunts_json text; BEGIN SELECT JSON_BUILD_OBJECT ('accountCounts', JSON_AGG( JSON_BUILD_OBJECT ('businessUnitId',businessUnitId ,'parentAccountNumber',parentAccountNumber ,'accountNumber',accountNumber ,'totalOnlineContactsCount',online_contacts_count, 'countByPosition', JSON_BUILD_OBJECT ( 'taxProfessionalCount',tax_professional_count ,'attorneyCount',attorney_count ,'nonAttorneyCount',non_attorney_count ,'clerkCount',clerk_count )))) INTO v_out_accunts_json FROM ( WITH tab AS (SELECT * FROM ( SELECT (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER AS parentAccountNumber, (json_doc::json->'data'->'account'->>'accountNumber')::INTEGER AS accountNumber, (json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER AS businessUnitId, (json_doc::json->'data'->>'positionId')::varchar AS positionId FROM aws_test_pg_table) a ) , tab1 AS ( SELECT (json_array_elements(b.jc -> 'accounts') ->> 'accountNumber')::integer accountNumber, (json_array_elements(b.jc -> 'accounts') ->> 'businessUnitId')::integer businessUnitId, (json_array_elements(b.jc -> 'accounts') ->> 'parentAccountNumber')::integer parentAccountNumber FROM ( SELECT p_in_accounts_json::json AS jc) b) SELECT tab.businessUnitId::text, tab.parentAccountNumber::text, tab.accountNumber::text, SUM(1) online_contacts_count, SUM(CASE WHEN tab.positionId::text = '0095' THEN 1 ELSE 0 END) tax_professional_count, SUM(CASE WHEN tab.positionId::text = '0100' THEN 1 ELSE 0 END) attorney_count, SUM(CASE WHEN tab.positionId::text = '0090' THEN 1 ELSE 0 END) non_attorney_count, SUM(CASE WHEN tab.positionId::text = '0050' THEN 1 ELSE 0 END) clerk_count FROM tab1,tab WHERE tab.parentAccountNumber::INTEGER=tab1.parentAccountNumber::INTEGER AND tab.accountNumber::INTEGER=tab1.accountNumber::INTEGER AND tab.businessUnitId::INTEGER=tab1.businessUnitId::INTEGER GROUP BY tab.businessUnitId::text, tab.parentAccountNumber::text, tab.accountNumber::text) a; RETURN v_out_accunts_json; END; $$; ``` *Menjalankan fungsi* ``` select f_pg_json_test('{ "accounts": [{ "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }, { "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }] }') ; ``` *Output fungsi* Output berikut ini mirip dengan output prosedur Oracle. Perbedaannya adalah bahwa output ini dalam format Teks. ``` { "accountCounts": [ { "businessUnitId": "6", "parentAccountNumber": "32001", "accountNumber": "42001", "totalOnlineContactsCount": 1, "countByPosition": { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } }, { "businessUnitId": "7", "parentAccountNumber": "32000", "accountNumber": "42000", "totalOnlineContactsCount": 1, "countByPosition": { "taxProfessionalCount": 0, "attorneyCount": 1, "nonAttorneyCount": 0, "clerkCount": 0 } } ] } ```