Ringkasan Prasyarat dan batasan Arsitektur Alat Praktik terbaik Epik Sumber daya terkait Informasi tambahan

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 sepertiJSON_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

Gunakan AWS SCT dengan kode fungsi JSON untuk mengonversi kode sumber dari Oracle ke PostgreSQL.
Konversi menghasilkan file.sql bermigrasi yang didukung PostgreSQL.
Secara manual mengonversi kode fungsi Oracle JSON yang tidak dikonversi ke kode fungsi PostgreSQL JSON.
Jalankan file.sql pada instans DB yang kompatibel dengan Aurora PostgreSQL target.

Alat

Layanan AWS

Amazon Aurora 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 membantu Anda mengatur, mengoperasikan, dan menskalakan database relasional PostgreSQL di AWS Cloud.
AWS Schema Conversion Tool (AWS SCT) 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 adalah lingkungan pengembangan terintegrasi yang menyederhanakan pengembangan dan pengelolaan database Oracle baik dalam penerapan tradisional maupun berbasis cloud.
pgAdmin atau. DBeaver pgAdmin adalah alat manajemen sumber terbuka untuk PostgreSQL. Ini menyediakan antarmuka grafis yang membantu Anda membuat, memelihara, dan menggunakan objek database. DBeaveradalah 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_SQL_READ_JSON di bagian Informasi tambahan.

Epik

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_Table_Creation_Insert_Script 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_Table_Creation_Insert_Script yang ada di bagian Informasi* tambahan.*	Insinyur migrasi

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_SQL_READ_JSON* 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_SQL_READ_JSON* di bagian Informasi tambahan.*	Insinyur migrasi

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_SQL_JSON_AGGREGATION_JOIN di bagian Informasi tambahan. JOIN - Data berformat JSON diteruskan sebagai parameter input ke kueri. Gabungan batin dibuat antara data statis ini dan data JSON di tabel Oracle DB. `aws_test_table` Agregasi dengan validasi — Data JSON memiliki `KEY` dan `VALUE` parameter dengan nilai-nilai seperti`accountNumber`,, `businessUnitId` dan `parentAccountNumberpositionId`, yang digunakan untuk `SUM` dan agregasi. `COUNT` Format JSON — Setelah bergabung dan agregasi, data dilaporkan dalam format JSON dengan menggunakan dan. `JSON_OBJECT` `JSON_ARRAYAGG`	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_SQL_JSON_AGGREGATION_JOIN* di bagian Informasi tambahan.* JOIN - Data berformat JSON (`tab1`) dilewatkan sebagai parameter input ke kueri klausa. `WITH` JOIN dibuat antara data statis ini dan data JSON, yang ada di `tab` tabel. JOIN juga dibuat dengan `WITH` klausa, yang memiliki data JSON dalam tabel. `aws_test_pg_table` Agregasi — Data JSON memiliki `KEY` dan `VALUE` parameter dengan nilai-nilai seperti`accountNumber`,,`parentAccountNumber`, dan `businessUnitIdpositionId`, yang digunakan untuk `SUM` dan `COUNT` agregasi. Format JSON — Setelah bergabung dan agregasi, data dilaporkan dalam format JSON dengan menggunakan dan. `JSON_BUILD_OBJECT` `JSON_AGG`	Insinyur migrasi

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_procedure _with_json_query* 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_Function_With_JSON_QUERY di bagian Informasi tambahan.	Insinyur migrasi

Sumber daya terkait

Informasi tambahan

Untuk mengonversi kode JSON dari database Oracle ke database PostgreSQL, gunakan skrip berikut, secara berurutan.

1. Oracle_Table_Creation_Insert_Script


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_Table_Creation_Insert_Script


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_SQL_READ_JSON

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 metadataKEY. KEYpositionIdItu adalah VALUE dari dataKEY. KEYaccountNumberItu adalah VALUE dari accountKEY, dan itu account KEY adalah VALUE dari dataKEY.

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_TABLE mengharapkan parameter dalam format JSON.

Setiap item di COLUMNS memiliki standarPATH, dan ada yang sesuai VALUE untuk yang KEY diberikan dikembalikan dalam format baris.

Hasil dari query sebelumnya

PARENT_ACCOUNT_NUMBER	ACCOUNT_NUMBER	BUSINESS_UNIT_ID	POSISI_ID
32000	42000	7	0100
32001	42001	6	0090

4. PostgreS_SQL_READ_JSON

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 danVALUE. 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 mengetikCAST, 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, danVALUE:"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 tipeCAST, 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, danVALUE:"0100"). Menggunakan double greater-than operator (>>) akan mengembalikan nilai aktual yang ditentukan untuk itu KEY (misalnya,KEY:, danVALUE: positionId0100, tanpa tanda kutip ganda).

Dalam kasus sebelumnya, parentAccountNumber adalah type CAST toINT, accountNumber is type CAST toINT, businessUnitId is type CAST toINT, 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_SQL_JSON_AGGREGATION_BERGABUNG

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 sepertiJSON_OBJECT,,, JSON_ARRAY dan. JSON_OBJECTAGG JSON_ARRAYAGG

JSON_OBJECTmenerima dua parameter: KEY danVALUE. KEYParameter harus di-hardcode atau statis. VALUEParameter ini berasal dari output tabel.
JSON_ARRAYAGGmenerima 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_SQL_JSON_AGGREGATION_BERGABUNG

PostgreSQL built-in JSON_BUILD_OBJECT berfungsi JSON_AGG dan mengkonversi data tingkat baris ke dalam format JSON. JSON_BUILD_OBJECTPostgreSQL 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_PROCEDURE_with_JSON_QUERY

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_Function_with_JSON_QUERY

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
      }
    }
  ]
}

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Connect dengan menggunakan terowongan SSH di pgAdmin

Salin tabel Amazon DynamoDB di seluruh akun