本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。
将 JSON Oracle 查询转换至 PostgreSQL 数据库 SQL
由 Pinesh Singal (AWS) 和 Lokesh Gurram (AWS) 编写
环境：PoC 或试点 | 源：数据库：关系 | 目标：Amazon RDS PostgreSQL |
R 类型：重构 | 工作负载：Oracle | 技术：数据库；迁移 |
Amazon Web Services：Amazon Aurora；Amazon RDS |
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
目标架构
使用带有 JSON 函数代码的 AWS SCT 将源代码从 Oracle 转换至 PostgreSQL。
转换生成 PostgreSQL 支持的已迁移的 .sql 文件。
手动将未转换的 Oracle JSON 函数代码转换至 PostgreSQL JSON 函数代码。
在兼容 Aurora PostgreSQL 的目标数据库实例上运行 .sql 文件。
工具
Amazon Web Services
Amazon Aurora 是与 MySQL 和 PostgreSQL 兼容的完全托管式的云端关系数据库引擎。
Amazon Relational Database Service（Amazon RDS）for PostgreSQL 可帮助您在 Amazon Web Services Cloud 中设置、操作和扩展PostgreSQL 关系数据库。
AWS Schema Conversion Tool（AWS SCT）通过自动将源数据库架构和大部分自定义代码转换为与目标数据库兼容的格式来支持异构数据库迁移。
其他服务
Oracle SQL Developer
是一个集成的开发环境，可简化传统部署和基于云的部署中 Oracle 数据库的开发和管理。 pgAdmin 或 DBeaver。pgAdmin
是一种适用于 PostgreSQL 的开源管理工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。DBeaver 是一个通用的数据库工具。
最佳实践
使用 JSON_TABLE 函数时，Oracle 查询的默认类型为 CAST 类型。最佳实践是在 PostgreSQL 中也使用 CAST，使用双倍大于字符 (>>)。
有关更多信息，请参阅其他信息部分中的 Postgres_SQL_Read_JSON。
操作说明
| 任务 | 描述 | 所需技能 |
|---|---|---|
将 JSON 数据存储至 Oracle 数据库中。 | 在 Oracle 数据库中创建表，并在 | 迁移工程师 |
将 JSON 数据存储至 PostgreSQL 数据库中。 | 在 PostgreSQL 数据库中创建一个表，并在 | 迁移工程师 |
| 任务 | 描述 | 所需技能 |
|---|---|---|
转换 Oracle 数据库的 JSON 数据。 | 编写 Oracle SQL 查询，将 JSON 数据读取至 ROW 格式。有关更多详细信息和示例语法，请参阅其他信息部分中的 Oracle_SQL_Read_JSON。 | 迁移工程师 |
转换 PostgreSQL 数据库中的 JSON 数据。 | 编写 PostgreSQL 查询，将 JSON 数据读取至 ROW 格式。有关更多详细信息和示例语法，请参阅其他信息部分中的Postgres_SQL_Read_JSON。 | 迁移工程师 |
| 任务 | 描述 | 所需技能 |
|---|---|---|
对 Oracle SQL 查询执行聚合与验证。 | 若要手动转换 JSON 数据，请对 Oracle SQL 查询执行联接、聚合和验证，并以 JSON 格式报告输出。使用其他信息部分的 Oracle_SQL_JSON_Aggregation_Join 中的代码。
| 迁移工程师 |
对 Postgres SQL 查询执行聚合与验证。 | 若要手动转换 JSON 数据，请对 Postgres SQL 查询执行联接、聚合和验证，并以 JSON 格式报告输出。使用其他信息部分的 Postgres_SQL_JSON_Aggregation_Join 中的代码。
| 迁移工程师 |
| 任务 | 描述 | 所需技能 |
|---|---|---|
将 Oracle 程序中的 JSON 查询转换为行。 | 对于 Oracle 程序示例，使用其他信息部分的 Oracle_procedure_with_JSON_Query 中的 Oracle 查询和代码。 | 迁移工程师 |
将具有 JSON 查询的 PostgreSQL 函数转换至基于行的数据。 | 对于 PostgreSQL 函数示例，使用之前的 PostgreSQL 查询以及其他信息部分的 Postgres_function_with_JSON_Query 中的代码。 | 迁移工程师 |
相关资源
其他信息
要将 JSON 代码从 Oracle 数据库转换至 PostgreSQL 数据库，请按顺序使用以下脚本。
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
以下代码块显示了如何将 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 的信息。
键 | 用于获取值的层次结构或者路径 | 值 |
|
| "P" |
|
| "0100" |
|
| 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_TABLE 函数要求参数使用 JSON 格式。
COLUMNS 中的每个项目都有预定义的 PATH，并且会以行格式返回与给定 KEY 对应的 VALUE。
上一次查询结果
PARENT_ACCOUNT_NUMBER | ACCOUNT_NUMBER | BUSINESS_UNIT_ID | POSITION_ID |
32000 | 42000 | 7 | 0100 |
32001 | 42001 | 6 | 0090 |
4。Postgres_SQL_Read_JSON
查询和语法示例
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_SQL_JSON_Aggregation_Join
示例查询
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_SQL_JSON_Aggregation_Join
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_procedure_with_JSON_Query
此代码将 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_function_with_JSON_Query
示例函数
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 } } ] }
