JSON_TABLE

功能描述

Vastbase在Oracle兼容模式下支持JSON_TABLE函数，用于将JSON数据转换为表格形式，接受一个或多个JSON文档作为输入，解析这些JSON文档后根据用户指定的模板，将JSON数据转换为表格形式。JSON_TABLE函数的返回值是一个虚拟表格，可以像普通表格一样进行查询和操作。

注意事项

该功能仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。

语法格式

JSON_TABLE(json,path,columns())

参数说明

• json

  包含输入的json数据，为json类型。

  不支持格式化json。

• path

  指定要转换的json的路径。如 '$.array[*]'指定的是json路径下的array数组下的全部元素。

  不支持指定在发生错误时的返回值。

• columns

  指定输出的结果表的列结构，分为PATH,NESTED_PATH两种类型。

示例

1、创建测试表并插入数据。

CREATE TABLE t_1172410(id int NOT NULL,date_loaded TIMESTAMP(6) WITH TIME ZONE,po_document varchar2(2000));

2、向测试表中插入数据。

INSERT INTO t_1172410 VALUES (
1,
date'2000-12-01',
'{"PONumber" : 1600,
"Reference" : "ABULL-20140421",
"Requestor" : "Alexis Bull",
"User" : "ABULL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Alexis Bull",
"Address": {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : [{"type" : "Office", "number" : "909-555-7307"},
{"type" : "Mobile", "number" : "415-555-1234"}]},
"Special Instructions" : null,
"AllowPartialShipment" : true,
"LineItems" : [{"ItemNumber" : 1,
"Part" : {"Description" : "One Magic Christmas",
"UnitPrice" : 19.95,
"UPCCode" : 13131092899},
"Quantity" : 9.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Lethal Weapon",
"UnitPrice" : 19.95,
"UPCCode" : 85391628927},
"Quantity" : 5.0}]}');

3、查询测试表数据，使用JSON_TABLE函数将数据转换为表结构。

SELECT jt.*
FROM t_1172410,
JSON_TABLE(po_document, '$.ShippingInstructions.Phone[*]'
COLUMNS (row_number int,
phone_type VARCHAR2(10) PATH '$.type',
phone_num VARCHAR2(20) PATH '$.number'))
AS jt;

返回结果为：

 row_number | phone_type |  phone_num
------------+------------+--------------
            | Office     | 909-555-7307
            | Mobile     | 415-555-1234
(2 rows)

4、清理测试表。

DROP TABLE t_1172410;