将 JSON Oracle 查询转换为 PostgreSQL 数据库 |
您所在的位置：网站首页 › 数据库数据转换为json格式 › 将 JSON Oracle 查询转换为 PostgreSQL 数据库 |
|
本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。 将 JSON Oracle 查询转换为 PostgreSQL 数据库由 Pinesh Singal (AWS) 和 Lokesh Gurram (AWS) 创建 环境：PoC 或试点 来源：数据库：关系型 目标：亚马逊 RDS PostgreSQL R 类型：重新架构 工作负载：甲骨文 技术：数据库；迁移 AWS 服务：Amazon Aurora；亚马逊 RDS 摘要此从本地迁移到Amazon Web Services (AWS) 云的迁移过程使用 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 相关的 Oracle 查询JSON_TABLE从 Oracle 数据库手动转换为 PostgreSQL 数据库。 先决条件和限制先决条件 活跃的 AWS 账户 本地 Oracle 数据库实例（安装和传输） Amazon Relational Database PostgreSQL 或 Amazon Amazon Relational Database SQL 或 Amazon Relational 限制 与 JSON 相关的查询需要固定的KEY和格式。VALUE不使用该格式会返回错误的结果。 如果 JSON 结构的任何更改在结果节中添加了新的KEY和VALUE对，则必须在 SQL 查询中更改相应的过程或函数。 早期版本的 Oracle 和 PostgreSQL 支持一些与 JSON 相关的函数，但功能较少。 产品版本 Oracle Database 和传输 适用于 PostgreSQL 或与 Aurora postgreSQL 兼容 9.5 及更高版本的亚马逊 AWS SCT 最新版本（使用版本 1.0.664 进行测试） 架构源技术堆栈 版本为 19c 的 Oracle 数据库实例 目标技术堆栈 版本 13 的Amazon RDS for PostgreSQL 或 Aurora 的 PostgreSQL 兼容数据库实例 目标架构 将 AWS SCT 与 JSON 函数代码一起使用，将源代码从 Oracle 转换为 PostgreSQL。 转换会生成支持 PostgreSQL 的已迁移的.sql 文件。 手动将未转换的 Oracle JSON 函数代码转换为 PostgreSQL JSON 函数代码。 在目标 Aurora PostgreSQL 兼容的数据库实例上运行.sql 文件。 工具AWS 服务 Amazon Aurora A batabase PostgreSQL。 适用的 Amazon Relational Datab ase PostgreSQL 或扩展关系数据库。 AWS Schema Conversion Tool (AWS SCT) 通过自动将源数据库架构和大部分自定义代码转换为与目标数据库兼容的格式来支持异构数据库迁移。 其他服务 Oracle SQL Developer 是一个集成的开发环境，可简化传统部署和基于云的部署中 Oracle 数据库的开发和管理。 pgAdmin 或 dBeaver。 pgAdmin gre SQL 和 PostgreSQL 的工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。dBeaver 是一个通用的数据库工具。 最佳实践使用该JSON_TABLE函数时，Oracle 查询将类型CAST作为默认值。最佳做法是在 PostgreSQL CAST 中也使用，使用双大于字符 ()。>> 有关更多信息，请参阅 “其他信息” 部分中的 postgres_sql_read_ JSON。 史诗任务描述所需技能将 JSON 数据存储在 Oracle 数据库中。 在 Oracle 数据库中创建一个表，然后将 JSON 数据存储在CLOB列中。 使用 “其他信息” 部分中的 Oracle_Table_Creation_Insert_Scri pt。 迁迁迁迁迁将 JSON 数据存储在 PostgreSQL 数据库中。 在 PostgreSQL 数据库中创建一个表，然后将 JSON 数据存储在该列中。TEXT使用 “其他信息” 部分中的 Postgres_Table_Creation_Insert_Scri pt。 迁迁迁迁迁 任务描述所需技能转换 Oracle 数据库上的 JSON 数据。 编写 Oracle SQL 查询，将 JSON 数据读取为 ROW 格式。有关更多详细信息和示例语法，请参阅 “其他信息” 部分中的 oracle_sql_read_ JSON。 迁迁迁迁迁转换 PostgreSQL 数据库上的 JSON 数据。 编写 PostgreSQL 查询，将 JSON 数据读取为 ROW 格式。有关更多详细信息和示例语法，请参阅 “其他信息” 部分中的 postgres_sql_read_JS ON。 迁迁迁迁迁 任务描述所需技能对 Oracle SQL 查询执行聚合和验证。 要手动转换 JSON 数据，请对 Oracle SQL 查询执行联接、聚合和验证，并以 JSON 格式报告输出。使用 “其他信息” 部分中 oracle_sql_json_Aggregation_Join 下的代码。 JOIN — JSON 格式的数据作为输入参数传递给查询。在此静态数据和 Oracle 数据库表中的 JSON 数据之间建立了内部连接aws_test_table。 带验证的聚合-JSON 数据具有KEYVALUE参数值，如accountNumberparentAccountNumberpositionId、businessUnitId和，用于SUM和COUNT聚合。 JSON 格式-在连接和聚合之后，使用JSON_OBJECT和以 JSON 格式报告数据JSON_ARRAYAGG。 迁迁迁迁迁对 Postgres SQL 查询进行聚合和验证。 要手动转换 JSON 数据，请对 PostgreSQL 查询执行联接、聚合和验证，并以 JSON 格式报告输出。使用 “其他信息” 部分中 po stgres_sql_json_Aggregation_J oin 下的代码。 JOIN — JSON 格式的数据 (tab1) 作为输入参数传递给WITH子句查询。在此静态数据和tab表中的 JSON 数据之间建立 JOIN。还使用子句生成 JOIN，该WITH子句在aws_test_pg_table表中包含 JSON 数据。 聚合-JSON 数据具有KEY和VALUE参数accountNumber，parentAccountNumber其值如positionId、、和，用于SUM和COUNT聚合。businessUnitId JSON 格式-在连接和聚合之后，使用JSON_BUILD_OBJECT和以 JSON 格式报告数据JSON_AGG。 迁迁迁迁迁 任务描述所需技能将 Oracle 过程中的 JSON 查询转换为行。 在 Oracle 过程示例中，使用之前的 Oracle 查询和 “其他信息” 部分中 oracle_procedure_with_json_ Query 下的代码。 迁迁迁迁迁将具有 JSON 查询的 PostgreSQL 函数转换为基于行的数据。 在 PostgreSQL 函数示例中，使用之前的 PostgreSQL 查询以及其他信息部分中 postgres_Function_with_json _ Query 下的代码。 迁迁迁迁迁相关资源甲骨文 JSON PostgreSQL JSO 甲骨 JSON 函数示例 PostgreSQL JSON 函数 AWS Schema Conversion Tool 其他信息要将 JSON 代码从 Oracle 数据库转换为 PostgreSQL 数据库，请按顺序使用以下脚本。 1。oracle_Table_creat_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" : "[email protected]", "profileType" : "P" }, "data" : { "onlineContactId" : "032323323", "displayName" : "Abc, Xyz", "firstName" : "Xyz", "lastName" : "Abc", "emailAddress" : "[email protected]", "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" : "[email protected]", "profileType" : "P" }, "data" : { "onlineContactId" : "54534343", "displayName" : "Xyz, pqr", "firstName" : "pqr", "lastName" : "Xyz", "emailAddress" : "[email protected]", "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_creat_insert_Scri 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" : "[email protected]", "profileType" : "P" }, "data" : { "onlineContactId" : "032323323", "displayName" : "Abc, Xyz", "firstName" : "Xyz", "lastName" : "Abc", "emailAddress" : "[email protected]", "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" : "[email protected]", "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成对。下表提供了有关VALUE从JSSon 文档中读与传输。 密钥 用于获取值的层次结构或 PATH 价值 profileType metadata -> profileType “P” positionId data -> positionId “0100" accountNumber data-> 账户-> accountNumber 42000 在前面的表格中，KEYprofileType是VALUE的metadataKEY。KEYpositionId是VALUE其中之一 dataKEY. KEYaccountNumber是VALUE其中的 accountKEY，accountKEY也是VALUE的 data 0。KEY JSON 文档示例 { "metadata" : { "upperLastNameFirstName" : "ABC XYZ", "upperEmailAddress" : "[email protected]", "profileType" : "P" }, "data" : { "onlineContactId" : "032323323", "displayName" : "Abc, Xyz", "firstName" : "Xyz", "lastName" : "Abc", "emailAddress" : "[email protected]", "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 函数需要使用 JSSON 格式的参数。 中的每个项目COLUMNS都有预定义的PATH，并且以行格式返回VALUEKEY适合给定值的项目。 上一次查询的结果 家长账户号码 账号_号码 业务单位_ID 位置_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从 JSON 读取。以下示例中使用了下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 ;使用单个大于运算符 (>) 将返回为此VALUE定义的运算符。KEY例如positionId，KEY: 和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: 和VALUE:positionId0100，不带双引号）。 在前面的例子中，parentAccountNumber是键入CAST到INT，accountNumber是键入CAST到INT，businessUnitId是键入CAST到INT，0 是键入 1 到 2。positionIdCASTVARCHAR 下表显示的查询结果解释了单个大于运算符 (>) 和双大于运算符 () 的作用。>> 在第一个表中，查询使用单个大于运算符 () >。每列都是 JSON 类型，无法转换为另一种数据类型。 parentAccountNumber 账号 businessUnitId 位置 ID 2003565430 2003564830 7 “0100” 2005284042 2005284042 6 “0090” 2000272719 2000272719 1 “0100” 在第二个表中，查询使用双大于运算符 () >>。每列都支持CAST基于列值的类型。例如，INTEGER在这种情况下。 parentAccountNumber 账号 businessUnitId 位置 ID 2003565430 2003564830 7 0100 2005284042 2005284042 6 0090 2000272719 2000272719 1 0100 5。oracle_sql_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 格式，Oracle 内置了诸如JSON_OBJECT、JSON_ARRAYJSON_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_聚合_加入 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; /运行程序 以下代码块解释了如何使用该过程的示例 JSON 输入来运行先前创建的 Oracle 过程。它还为您提供此过程的结果或输出。 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_Quer 示例函数 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 } } ] } |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |