时间戳

首页 > 解决方案 > 将全表迁移到另一个表中的 json/clob 列

sql - 将全表迁移到另一个表中的 json/clob 列

问题描述

我想从以下table1迁移数据：

NAME | Surname
-----+---------
Name1| Surname1
Name2| Surname2

进入table2 作为 clob (nclob,nvarchar(max),json.. etc.) 列：

NAME | JSON_COLUMN
-----+-------------------------------
NAME1| {Name: Name1,Surname: Surname1}
NAME2| {Name: Name2,Surname: Surname2}

我想知道进行此类迁移的最佳方法是什么？

是否有可以将表格转换为 json 格式的函数，以便我可以将其插入到新表格中？

像这样的东西：

insert into table2 (JSON_TABLE) select (table1 as json) from table1

标签： sqljsonoracle

解决方案

在 Oracle 12c 及更高版本中，您需要将列值使用JSON_OBJECTand转换CLOB为正确的 JSON 使用FORMAT JSON（SQL 字符串值'true'和'false'转换为JSON Boolean值true和false。）

例子：

SQL> WITH TABLE1 AS (
  2      SELECT 'Name1' NAME, 'Surname1' SURNAME FROM DUAL
  3      UNION ALL
  4      SELECT 'Name2', 'Surname2' FROM DUAL
  5  )
  6  SELECT
  7      NAME,
  8      TO_CLOB(JSON_OBJECT ( 'Name' VALUE NAME,
  9                    'Surname' VALUE SURNAME
 10                    FORMAT JSON )) AS "JSON_TABLE"
 11  FROM
 12      TABLE1;

NAME      |JSON_TABLE
----------|--------------------------------------------------
Name1     |{"Name":"Name1","Surname":Surname1}
Name2     |{"Name":"Name2","Surname":Surname2}

SQL>

对于 Oracle 11g 及更低版本，您需要自己创建 JSON，如下所示：

SQL> WITH TABLE1 AS (
  2      SELECT 'Name1' NAME, 'Surname1' SURNAME FROM DUAL
  3      UNION ALL
  4      SELECT 'Name2', 'Surname2' FROM DUAL
  5  )
  6  SELECT
  7      NAME,
  8      to_clob('{"Name:"' || NAME || '","Surname":'||SURNAME || '}') as "JSON_TABLE"
  9  FROM
 10      TABLE1;

NAME      |JSON_TABLE
----------|--------------------------------------------------
Name1     |{"Name:"Name1","Surname":Surname1}
Name2     |{"Name:"Name2","Surname":Surname2}

SQL>

Oracle 11g 中的另一个选项是使用 sql*plus 命令行界面，如下所示：

set sqlformat json

然后SELECT * FROM TABLE1将生成 json 格式的结果，但您需要使用将其导出到某个文件spool，然后将其加载到TABLE2. （可能是冗长的方法）

干杯！！

推荐阅读