Oracle 视图 ALL_JSON_COLUMNS 官方解释,作用,如何使用详细说明
本站中文解释
Oracle视图ALL_JSON_COLUMNS提供了Oracle表中支持JSON(JavaScript Object Notation,JavaScript对象标记)字段的信息。这个视图主要是供开发者在Oracle数据库中使用JSON格式存取数据时,从中取得表结构信息。
使用方法:
首先创建一个包含JSON格式字段的表,然后可以使用如下SQL命令来查询ALL_JSON_COLUMNS这个视图:
SELECT *
FROM all_json_columns
WHERE table_name='[表名]’;
这个查询的结果会显示该表中的JSON字段信息,其中包括:字段名、字段数据类型(即VARCHAR2、NUMBER、DATE等)、是否为主键字段、JSON字段所属的表等等。
官方英文解释
ALL_JSON_COLUMNS
provides information on the JavaScript Object Notation (JSON) columns accessible to the current user.
This view displays information on table and view columns that are guaranteed to return JSON data. These include:
-
Columns of data type
JSON
-
Columns on which a check constraint containing the
IS
JSON
condition is defined, where theIS
JSON
condition is always enforced. Such check constraints can contain only theIS
JSON
condition, or they can contain theIS
JSON
condition as part of a logicalAND
condition.For example, the following check constraints ensure that the
IS
JSON
condition is always enforced and will therefore causemycol
to appear in theALL_JSON_COLUMNS
view:mycol IS JSON mycol IS JSON AND LENGTH(mycol) > 100However, the following check constraint does not ensure that the
IS
JSON
condition is enforced, because it is part of a logicalOR
condition, and will therefore causemycol
to be omitted from theALL_JSON_COLUMNS
view:mycol IS JSON OR LENGTH(mycol) > 100 - View columns that are defined as the return value of a SQL function that returns JSON data. Such functions include:
TREAT
(
expr
AS
JSON
)
JSON_ARRAY
,JSON_ARRAYAGG
,JSON_MERGEPATCH
,JSON_OBJECT
,JSON_OBJECTAGG
,JSON_QUERY
,JSON_SERIALIZE
, andJSON_TRANSFORM
For example, in the following definition for view
v1
, columnmycol
is defined as the return value of theJSON_OBJECT
function, which will therefore causemycol
to appear in theALL_JSON_COLUMNS
view:CREATE TABLE t1 (text varchar2(100)); CREATE VIEW v1 AS SELECT JSON_OBJECT(text) AS mycol FROM t1;
Related Views
-
DBA_JSON_COLUMNS
provides information on all JSON columns. -
USER_JSON_COLUMNS
provides information on the JSON columns for which the user is the owner. This view does not display theOWNER
column.
Column | Datatype | NULL | Description |
---|---|---|---|
|
|
Owner of the table with the JSON column |
|
|
|
Name of the table with the JSON column |
|
|
|
Object type:
|
|
|
|
Name of the JSON column |
|
|
|
Format of the JSON data |
|
|
|
Data type of the JSON column |
See Also:
-
“DBA_JSON_COLUMNS”
-
“USER_JSON_COLUMNS”
-
Oracle XML DB Developer’s
Guide for more information about using JSON with Oracle Database