将 Oracle 数据库中的 JSON SQL 查询转换为 PostgreSQL 数据库 数据库博客

---

从 Oracle 数据库转换 JSON SQL 查询到 PostgreSQL 数据库

关键要点在本文中，我们介绍了一种可帮助您将 Oracle 数据库中的 JSON SQL 查询转换为 PostgreSQL 数据库的解决方案，特别关注 JSON 相关数据的格式转换。

在现代业务应用中，客户常常使用半结构化或非结构化数据存储，具有无模式和灵活的特性。其中之一的半结构化数据类型是JavaScript对象表示法JSON，它以 KEY、VALUE、LIST 和 ARRAY 格式存储数据。

Oracle 数据库将 JSON 对象数据存储在CLOB 数据类型 列中。而 PostgreSQL 则提供 JSON文本和 JSONB以二进制格式存储列来存储JSON 数据，如果 JSON 数据不符合 RFC 8259 标准，也可以选择将其存储在 TEXT 列中。

本文提供了一种解决方案，帮助用户在将 Oracle 或 Amazon RDS for Oracle 迁移到 Amazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQL 兼容版 时转换与 JSON 相关的数据。

解决方案概述

该解决方案适用于 122 版本及以上的 Oracle 数据库，目标是 RDS for PostgreSQL 或 Aurora PostgreSQL 数据库实例，版本为 95 及以上。

在 JSON 查询转换中，我们使用 Oracle 的以下函数：

JSONTABLE 将 JSON 数据转换为表格格式JSONARRAYAGG 聚合 JSON 格式数据JSONOBJECT 将表格数据转换为 JSON 格式

在 PostgreSQL 中，我们使用以下函数：

JSONBBUILDOBJECT 根据一系列交替的键值对创建 JSON 对象并返回JSONBAGG 聚合函数，返回包含所有值的 JSON 数组JSONBARRAYELEMENTS 将顶层 JSON 数组扩展为一组 JSON 值

请注意，SQL 标准中的 JSONARRAYAGG 和 JSONOBJECT 函数从 PostgreSQL 16 版本开始可用并受到支持。

接下来，我们将逐步展示如何将基于 JSON 的 SQL 查询从 Oracle 转换为 PostgreSQL。

我们在示例中使用以下 JSON 数据：

json{ metadata { upperLastNameFirstName ABC XYZ upperEmailAddress abc@examplecom profileType P } data { onlineContactId 032323323 displayName Abc Xyz firstName Xyz lastName Abc emailAddress abc@examplecom productRegistrationStatus Not registered positionId 0100 arrayPattern account { companyId SMGE businessUnitId 7 accountNumber 42000 parentAccountNumber 32000 firstName harry lastName poter street1 retOdertcaShr city new york postalcode XY ABC country United States } products [ { appUserGuid i0acc4450000001823fbad478e2eab8a0 id 0000000046 name ProView domain EREADER registrationStatus false status 11 } ] }}

前提条件

要跟随本帖，您需要以下前提条件：

一份有效的 AWS 账户一台正在运行的本地 Oracle DB 实例源数据库，版本为 122 及以上一台正在运行的 RDS for PostgreSQL 或 Aurora PostgreSQL DB 实例目标数据库，版本为 95 及以上

我们使用AWS 架构转换工具AWS SCT将源 Oracle 数据库功能转换为 PostgreSQL。转换会生成支持 PostgreSQL 的迁移SQL 文件。您可以在 PostgreSQL 数据库实例上运行这些 SQL 文件。

将 JSON 数据转换为表格格式

在本节中，我们讨论两种将 JSON 数据转换为表格格式的选项：

转换存在于表中的 JSON 数据转换传递给 JSON 表函数的静态 JSON 数据

转换 Oracle 表中存在的 JSON 数据

SQL/JSON 函数 JSONTABLE 为 JSON 数据创建一个关系视图。它将 JSON 数据评估的结果映射到关系行和列。您可以使用 SQL 查询该函数返回的结果，作为一个虚拟关系表。JSONTABLE 的主要目的是为 JSON 数组中的每个对象创建一行关系数据，并将该对象中的 JSON 值输出为单独的 SQL 列值。

可以在 SELECT 语句的 FROM 子句中指定 JSONTABLE。该函数首先对提供的 JSON 数据应用 JSON 路径表达式，称为 行路径表达式。与该行路径表达式匹配的 JSON 值称为 行源，生成一行关系数据。COLUMNS 子句评估行源，查找行源中的特定 JSON 值，并将这些 JSON 值返回为单独列的 SQL 值。

JSONTABLE 具有以下参数：

表达式 传入包含 JSON 内容的 JSON 文档或列JSON 路径表达式 JSON 文档的路径或层次错误子句 当相应的 JSON 字段不存在时的错误处理列列表 映射 JSON 对象中 JSON 字段的列列表

下表展示了如何使用我们的示例数据读取表格数据的 JSONTABLE。

关键获取值使用的层次或路径值profileTypemetadata gt profileType“P”positionIddata gt positionId“0100”accountNumberdata gt account gt accountNumber42000

从此表中我们可以理解：

要获取 profileType 值，使用的层次或路径是 metadata gt profileType要获取 positionId 值，使用的层次或路径是 data gt positionId要获取 accountNumber 值，使用的层次或路径是 data gt account gt accountNumber

现在，我们将样本 JSON 数据插入到名为 awstesttable 的表中，其中的列类型为 CLOB。使用提供的 SQL 查询将所选 JSON 字段转换为表格格式。

使用 SQL Developer 或其他所需工具连接到源 Oracle 数据库。运行以下命令创建测试表：

sqlCREATE TABLE awstesttable (id NUMBER createdon DATE DEFAULT SYSDATE modifiedon DATE jsondoc CLOB)

运行以下命令将样本 JSON 数据插入到表中：

sqlREM INSERTING INTO EXPORTTABLESET DEFINE OFFINSERT INTO awstesttable (IDCREATEDONMODIFIEDONjsondoc) VALUES (1 todate(02AUG2022 123014DDMONYYYY HH24MISS) todate(02AUG2022 123014DDMONYYYY HH24MISS) TOCLOB(q[{ metadata { upperLastNameFirstName ABC XYZ upperEmailAddress abc@examplecom profileType P } data { onlineContactId 032323323 displayName Abc Xyz firstName Xyz lastName Abc emailAddress abc@examplecom productRegistrationStatus Not registered positionId 0100 arrayPattern account { companyId SMGE businessUnitId 7 accountNumber 42000 parentAccountNumber 32000 firstName harry lastName poter street1 retOdertcaShr city new york postalcode XY ABC country United States } products [ { appUserGuid i0acc4450000001823fbad478e2eab8a0 id 0000000046 name ProView domain EREADER registrationStatus false status 11 } ] }}]))

运行以下 SQL 查询将数据转换为行和列：

sqlSELECT parentaccountnumber accountnumber businessunitid positionid FROM awstesttable aws JSONTABLE ( jsondoc ERROR ON ERROR COLUMNS ( parentaccountnumber NUMBER PATH dataaccountparentAccountNumber accountnumber NUMBER PATH dataaccountaccountNumber businessunitid NUMBER PATH dataaccountbusinessUnitId positionid VARCHAR2 (4) PATH datapositionId ) ) AS sc

从前面的查询中，我们可以理解 JSONTABLE 函数具有以下参数：

JSONDOC 这是包含 JSON 内容的 jsondoc 列。 路径被定义为 。默认情况下， 指示 JSON 文档中的所有字段。例如，account[] 表示所有字段在 account 键下请参考示例数据。metadata[] 表示所有字段在 metadata 键下。ERROR ON ERROR 这是错误处理机制，有三种变体：NULL ON ERROR 发生错误时返回 null。这是默认值。ERROR ON ERROR 发生错误时返回相应的 Oracle 错误。DEFAULT literal ON ERROR 发生错误时返回字面量。如果该函数返回的值数据类型为 VARCHAR2，则必须指定文本常量；如果数据类型为 NUMBER，则必须指定数值常量。COLUMNS 字段的名称列表以及数据类型和路径。如果 JSON 中存在相应的路径，则返回与该字段关联的值。如果不存在，则会处理错误。

下图展示了前面查询的结果。

转换 PostgreSQL 中存在的 JSON 数据

Oracle 具有用于将 JSON 数据转换为表格格式的 JSONTABLE 函数。然而，PostgreSQL 当前没有 SQL/JSON JSONTABLE 函数，因此我们必须直接从 JSON 文档中读取数据以将其转换为表格。为此，我们使用以下示例 JSON 数据。

使用 pgAdmin 或其他所需工具连接到目标 PostgreSQL 数据库。运行以下命令创建测试表：

sqlCREATE TABLE awstestpgtable(id INT createdon DATE modifiedon DATE jsondoc JSONB)

运行以下命令将 JSON 数据插入到测试表中：

sqlINSERT INTO awstestpgtable(id createdon modifiedon jsondoc)VALUES(1 now() now(){ metadata { upperLastNameFirstName ABC XYZ upperEmailAddress abc@examplecom profileType P } data { onlineContactId 032323323 displayName Abc Xyz firstName Xyz lastName Abc emailAddress abc@examplecom productRegistrationStatus Not registered positionId 0100 arrayPattern account { companyId SMGE businessUnitId 7 accountNumber 42000 parentAccountNumber 32000 firstName harry lastName poter street1 retOdertcaShr city new york postalcode XY ABC country United States } products [ { appUserGuid i0acc4450000001823fbad478e2eab8a0 id 0000000046 name ProView domain EREADER registrationStatus false status 11 } ] }})

运行以下 SQL 查询将数据转换为行和列：

sqlSELECT FROM ( SELECT (jsondocjsonbgtdatagtaccountgtparentAccountNumber) AS parentAccountNumber (jsondocjsonbgtdatagtaccountgtaccountNumber) AS accountNumber (jsondocjsonbgtdatagtaccountgtbusinessUnitId) AS businessUnitId (jsondocjsonbgtdatagtgtpositionId) AS positionId FROM awstestpgtable) d

单个右箭头 (gt) 操作符返回所定义的 JSON 数据类型的值。例如，键 positionId 和值 0100。但是，如果您想将键值作为 INT 或 TEXT，则不能直接在 JSON 数据类型上进行类型转换。您可以使用双右箭头 (gtgt) 操作符返回以 INT 或 TEXT 格式表示的键值。

在以下 SQL 查询中，允许进行类型转换：

sqlSELECT FROM ( SELECT (jsondocjsonbgtdatagtaccountgtgtparentAccountNumber)INTEGER AS parentAccountNumber (jsondocjsonbgtdatagtaccountgtgtaccountNumber)INTEGER AS accountNumber (jsondocjsonbgtdatagtaccountgtgtbusinessUnitId)INTEGER AS businessUnitId (jsondocjsonbgtdatagtgtpositionId)TEXT AS positionId FROM awstestpgtable) d

使用双右箭头 (gtgt) 返回所定义键的实际值。例如，键 positionId 和没有双引号的值 0100。在使用 gtgt 时，允许进行类型转换。

在上面的例子中，parentAccountNumber、accountNumber 和 businessUnitId 被转换为 INT，而 positionId 被转换为 TEXT。

下表比较了 gt 和 gtgt 的作用。

将静态 Oracle JSON 数据传递给 JSON 表函数

我们可以使用相同的 JSONTABLE 函数将静态 JSON 数据转换为表格格式。请看以下示例代码：

sqlSELECT FROM JSONTABLE ( { accounts [{ accountNumber 42000 parentAccountNumber 32000 businessUnitId 7 } { accountNumber 42001 parentAccountNumber 32001 businessUnitId 6 }] } accounts[] ERROR ON ERROR COLUMNS ( parentaccountnumber PATH parentAccount

破解版加速器永久免费

---