本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。 # 将 JSON Oracle 查询转换至 PostgreSQL 数据库 SQL *Pinesh Singal 和 Lokesh Gurram，Amazon Web Services* ## Summary 从本地迁移至 Amazon Web Services (AWS) Cloud 的迁移过程使用 AWS Schema Conversion Tool (AWS SCT) 将代码从 Oracle 数据库转换为 PostgreSQL 数据库。大部分代码价格 AWS SCT 自动转换。但是，与 JSON 相关的 Oracle 查询不自动转换。 从 Oracle 12.2 版开始，Oracle 数据库支持各种 JSON 函数，这些函数有助于将基于 JSON 的数据转换为基于行的数据。但是，AWS SCT 不会自动将基于 JSON 的数据转换至 PostgreSQL 支持的语言。 这种迁移模式主要侧重于手动使用 `JSON_OBJECT`、`JSON_ARRAYAGG` 和 `JSON_TABLE` 等函数将 JSON 相关的 Oracle 查询从 Oracle 数据库转换为 PostgreSQL 数据库。 ## 先决条件和限制 **先决条件** + 一个有效的 Amazon Web Services account + 本地 Oracle 数据库实例（已启动并正在运行） + Amazon Relational Database Service (Amazon RDS) for PostgreSQL 或Amazon Aurora PostgreSQL-Compatible Edition 数据库实例（已启动并运行） **限制** + 与 JSON 相关的查询需要固定的 `KEY` 和 `VALUE` 格式 不使用此格式会返回错误的结果。 + 如果 JSON 结构的任何更改在结果节中添加了新的 `KEY` 和 `VALUE` 对，则必须在 SQL 查询中更改相应的过程或函数。 + 早期版本的 Oracle 和 PostgreSQL 支持部分与 JSON 相关的函数，但功能较少。 **产品版本** + Oracle 数据库版本 12.2 及更高版本 + Amazon RDS for PostgreSQL 或 Aurora PostgreSQL-Compatible 版本 9.5 和更高版本 + AWS SCT 最新版本（使用版本 1.0.664 进行了测试） ## 架构 **源技术堆栈** + 19c 版本的 Oracle 数据库实例 **目标技术堆栈** + Amazon RDS for PostgreSQL 或 Aurora PostgreSQL-Compatible 数据库实例，版本 13 **目标架构** ![\[描述如图所示。\]](http://docs.aws.amazon.com/zh_cn/prescriptive-guidance/latest/patterns/images/pattern-img/5e2c3b07-9ef5-417f-b049-bcea58f2c3ec/images/2ff8b00b-8849-4ef1-9be1-579f7b51be10.png) 1. 使用带有 JSON 函数代码的 AWS SCT 将源代码从 Oracle 转换至 PostgreSQL。 1. 转换生成 PostgreSQL 支持的已迁移的 .sql 文件。 1. 手动将未转换的 Oracle JSON 函数代码转换至 PostgreSQL JSON 函数代码。 1. 在兼容 Aurora PostgreSQL 的目标数据库实例上运行 .sql 文件。 ## 工具 **Amazon Web Services** + [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) 是与 MySQL 和 PostgreSQL 兼容的完全托管式的云端关系数据库引擎。 + [Amazon Relational Database Service（Amazon RDS）for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) 可帮助您在 Amazon Web Services Cloud 中设置、操作和扩展PostgreSQL 关系数据库。 + [AWS Schema Conversion Tool（AWS SCT）](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)通过自动将源数据库架构和大部分自定义代码转换为与目标数据库兼容的格式来支持异构数据库迁移。 **其他服务** + [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) 是一个集成的开发环境，可简化传统部署和基于云的部署中 Oracle 数据库的开发和管理。 + pgadmin 或 DBeaver。 [pgadmin](https://www.pgadmin.org/) 是一款适用于 PostgreSQL 的开源管理工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。 [DBeaver](https://dbeaver.io/)是一个通用的数据库工具。 ## 最佳实践 使用 `JSON_TABLE` 函数时，Oracle 查询的默认类型为 `CAST` 类型。最佳实践是在 PostgreSQL 中也使用 `CAST`，使用双倍大于字符 (`>>`)。 有关更多信息，请参阅*其他信息*部分中的 *Postgres\$1SQL\$1Read\$1JSON*。 ## 操作说明 ### 在 Oracle 和 PostgreSQL 数据库生成 JSON 数据 | Task | 说明 | 所需技能 | | --- | --- | --- | | 将 JSON 数据存储至 Oracle 数据库中。 | 在 Oracle 数据库中创建表，并在 `CLOB` 列中存储 JSON 数据。 使用*其他信息*部分的 *Oracle\$1Table\$1Creation\$1Insert\$1Script*。 | 迁移工程师 | | 将 JSON 数据存储至 PostgreSQL 数据库中。 | 在 PostgreSQL 数据库中创建一个表，并在`TEXT`列中存储 JSON 数据。使用*其他信息*部分的 *Postgres\$1Table\$1Creation\$1Insert\$1Script*。 | 迁移工程师 | ### 将 JSON 转换为 ROW 格式 | Task | 说明 | 所需技能 | | --- | --- | --- | | 转换 Oracle 数据库的 JSON 数据。 | 编写 Oracle SQL 查询，将 JSON 数据读取至 ROW 格式。有关更多详细信息和示例语法，请参阅*其他信息*部分中的 *Oracle\$1SQL\$1Read\$1JSON*。 | 迁移工程师 | | 转换 PostgreSQL 数据库中的 JSON 数据。 | 编写 PostgreSQL 查询，将 JSON 数据读取至 ROW 格式。有关更多详细信息和示例语法，请参阅*其他信息*部分中的*Postgres\$1SQL\$1Read\$1JSON*。 | 迁移工程师 | ### 使用 SQL 查询手动转换 JSON 数据，并以 JSON 格式报告输出 | Task | 说明 | 所需技能 | | --- | --- | --- | | 对 Oracle SQL 查询执行聚合与验证。 | 若要手动转换 JSON 数据，请对 Oracle SQL 查询执行联接、聚合和验证，并以 JSON 格式报告输出。使用*其他信息*部分的 *Oracle\$1SQL\$1JSON\$1Aggregation\$1Join* 中的代码。[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | 迁移工程师 | | 对 Postgres SQL 查询执行聚合与验证。 | 若要手动转换 JSON 数据，请对 Postgres SQL 查询执行联接、聚合和验证，并以 JSON 格式报告输出。使用*其他信息*部分的 *Postgres\$1SQL\$1JSON\$1Aggregation\$1Join* 中的代码。[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_cn/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | 迁移工程师 | ### 将 Oracle 程序转换为包含 JSON 查询的 PostgreSQL 函数 | Task | 说明 | 所需技能 | | --- | --- | --- | | 将 Oracle 程序中的 JSON 查询转换为行。 | 对于 Oracle 程序示例，使用*其他信息*部分的 *Oracle\$1procedure\$1with\$1JSON\$1Query* 中的 Oracle 查询和代码。 | 迁移工程师 | | 将具有 JSON 查询的 PostgreSQL 函数转换至基于行的数据。 | 对于 PostgreSQL 函数示例，使用之前的 PostgreSQL 查询以及*其他信息*部分的 *Postgres\$1function\$1with\$1JSON\$1Query* 中的代码。 | 迁移工程师 | ## 相关资源 + [Oracle JSON 函数](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html) + [PostgreSQL JSON 函数](https://www.postgresql.org/docs/13/functions-json.html) + [甲骨文 JSON 函数示例](https://oracle-base.com/articles/12c/sql-json-functions-12cr2) + [PostgreSQL JSON 函数示例](https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg) + [AWS Schema Conversion Tool‭](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) ## 附加信息 要将 JSON 代码从 Oracle 数据库转换至 PostgreSQL 数据库，请按顺序使用以下脚本。 **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** 以下代码块显示了如何将 Oracle JSON 数据转换为行格式。 *查询和语法示例* ``` 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 ); ``` JSON 文档将数据存储为集合。每个集合可以有 `KEY` 和 `VALUE` 对。每个 `VALUE` 都可以有嵌套 `KEY` 和 `VALUE` 对。下表提供了有关从 JSON 文档中读取特定 `VALUE` 的信息。 | | | 键 | 用于获取值的层次结构或者路径 | 值 | | --- |--- |--- | | `profileType` | `metadata` -> `profileType` | "P" | | `positionId` | `data` -> `positionId` | "0100" | | `accountNumber` | `data` -> 账户 -> `accountNumber` | 42000 | 在上表中，`KEY` `profileType` 是 `metadata` `KEY` 中的 `VALUE`。`KEY` `positionId` 是 `data` `KEY` 中的 `VALUE`。`KEY` `accountNumber` 是 `account` `KEY` 中的 `VALUE`，`account` `KEY` 是 `data` `KEY` 中的 `VALUE`。 *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" } ] } } ``` *用于从 JSON 文档中获取选定字段的 SQL 查询* ``` 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 ``` 在前面的查询中，`JSON_TABLE` 是 Oracle 中的一个内置函数，用于将 JSON 数据转换为行格式。JSON\$1TABLE 函数要求参数使用 JSON 格式。 `COLUMNS` 中的每个项目都有预定义的 `PATH`，并且会以行格式返回与给定 `KEY` 对应的 `VALUE`。 *上一次查询结果* | | | PARENT\$1ACCOUNT\$1NUMBER | ACCOUNT\$1NUMBER | BUSINESS\$1UNIT\$1ID | POSITION\$1ID | | --- |--- |--- |--- | | 32000 | 42000 | 7 | 0100 | | 32001 | 42001 | 6 | 0090 | **4。Postgres\$1SQL\$1Read\$1JSON** *****查询和语法示例* ``` 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 ; ``` 在 Oracle 中，`PATH` 用于标识特定 `KEY` 和 `VALUE`。但是，PostgreSQL 使用 `HIERARCHY` 模型从 JSON 读取 `KEY` 和 `VALUE`。以下示例中使用了 `Oracle_SQL_Read_JSON` 下所述的相同的 JSON 数据。 *不允许使用 CAST 类型 SQL 查询* (如果您强制输入 `CAST`，则查询会因语法错误而失败。) ``` 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 ; ``` 使用单个大于运算符 (`>`) 将返回为此 `KEY` 定义的 `VALUE`。例如，`KEY`: `positionId` 和 `VALUE`: `"0100"`。 当您使用单个大于号运算符 (`>`) 时，不允许使用类型 `CAST`。 *允许使用 CAST 类型 SQL 查询* ``` 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 ; ``` 要使用类型 `CAST`，必须使用双精度大于运算符。如果您使用单个大于运算符，则查询将返回为此定义的 `VALUE`（例如：和 `KEY`: `positionId` 和 `VALUE`: `"0100"`）。使用双精度大于运算符 (`>>`) 将返回为此定义的 `KEY` 实际值（例如 `KEY`:`positionId` 和 `VALUE`:`0100`，不带双引号）。 在前述例子中，`parentAccountNumber` 是类型 `CAST` 到 `INT` ，`accountNumber` 是类型 `CAST` 到 `INT`，`businessUnitId` 是类型 `CAST` 到`INT`，`positionId` 是类型 `CAST` 到 `VARCHAR`。 下表显示的查询结果解释了单个大于运算符 (`>`) 和双大于运算符 (`>>`) 的作用。 在第一个表中，查询使用单个大于运算符 (`>`) 。每列均采用 JSON 类型，无法转换至其他数据类型。 | | | parentAccountNumber | accountNumber | businessUnitId | positionId | | --- |--- |--- |--- | | 2003565430 | 2003564830 | 7 | "0100" | | 2005284042 | 2005284042 | 6 | "0090" | | 2000272719 | 2000272719 | 1 | "0100" | 在第二个表中，查询使用双精度大于运算符 (`>>`) 。每列都支持基于列值的 `CAST` 类型。例如，在此上下文中的 `INTEGER`。 | | | parentAccountNumber | accountNumber | businessUnitId | positionId | | --- |--- |--- |--- | | 2003565430 | 2003564830 | 7 | 0100 | | 2005284042 | 2005284042 | 6 | 0090 | | 2000272719 | 2000272719 | 1 | 0100 | **5。Oracle\$1SQL\$1JSON\$1Aggregation\$1Join** *示例查询* ``` 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 ); ``` 为了将行级数据转换至 JSON 格式，Oracle 内置了函数，例如`JSON_OBJECT`、`JSON_ARRAY`、`JSON_OBJECTAGG` 和 `JSON_ARRAYAGG`。 + `JSON_OBJECT` 接受两个参数：`KEY` 和 `VALUE`。该 `KEY` 参数本质上应该是硬编码的或静态的。该 `VALUE` 参数源自表输出。 + `JSON_ARRAYAGG` 接受 `JSON_OBJECT` 作为参数。这有助于将一组 `JSON_OBJECT` 元素分组为一个列表。例如，如果 `JSON_OBJECT` 元素有多条记录 (数据集中有多条 `KEY` 和 `VALUE` 对)，`JSON_ARRAYAGG` 会追加数据集并创建一个列表。根据数据结构语言，`LIST` 是一组元素。在此上下文中，`LIST` 是一组 `JSON_OBJECT` 元素。 以下示例显示了一个 `JSON_OBJECT` 元素。 ``` { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } ``` 下一个示例显示两个 `JSON_OBJECT` 元素，用方括号 (`[ ]`) 表示 `LIST`。 ``` [ { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } , { "taxProfessionalCount": 2, "attorneyCount": 1, "nonAttorneyCount": 3, "clerkCount":4 } ] ``` *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 ); ``` *上一个 SQL 查询输出示例* ``` { "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\$1Join** PostgreSQL 内置函数 `JSON_BUILD_OBJECT` 和 `JSON_AGG` 将行级数据转换为 JSON 格式。 PostgreSQL `JSON_BUILD_OBJECT` 和 `JSON_AGG` 等同于 Oracle `JSON_OBJECT` 和 `JSON_ARRAYAGG` *示例查询* ``` 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; ``` *前述查询的输出示例* Oracle 和 PostgreSQL 输出完全相同。 ``` { "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** 此代码将 Oracle 程序转换为具有 JSON SQL 查询的 PostgreSQL 函数。它显示了查询如何将 JSON 转换至行，反之亦然。 ``` 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; / ``` *运行程序* 以下代码块介绍了如何运行前面创建的 Oracle 程序，并将示例 JSON 输入用于该程序。它还会为您提供此程序的结果或输出。 ``` 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; / ``` *程序输出* ``` { "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** *示例函数* ``` 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; $$; ``` *运行函数* ``` select f_pg_json_test('{ "accounts": [{ "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }, { "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }] }') ; ``` *函数输出* 以下输出与 Oracle 程序输出类似。不同之处在于此输出为文本格式。 ``` { "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 } } ] } ```