创建带json字段的表：
CREATE TABLE test.t_json (
id NUMBER,
json_data JSON
);
插入一批数据
DECLARE
BEGIN
FOR i IN 1..9999 LOOP
INSERT INTO test.t_json(id, json_data)
VALUES (i, JSON('{"name":"张三' || i || '","value":' || i || '}'));
END LOOP;
COMMIT;
END;
/
select * from test.t_json；
查询结果部分值：
ID JSON_DATA
---------- --------------------------------------------------------------------------------
9984 {"name":"张三9984","value":9984}
9985 {"name":"张三9985","value":9985}
9986 {"name":"张三9986","value":9986}
9987 {"name":"张三9987","value":9987}
9988 {"name":"张三9988","value":9988}
9989 {"name":"张三9989","value":9989}
9990 {"name":"张三9990","value":9990}
查询id为10的，json数据的name键值
SQL> SELECT JSON_VALUE(json_data, '$.name') AS j_name FROM test.t_json WHERE id = 10;
J_NAME
-----------------------------
张三10
对json_data字段的name键值创建索引
SQL> CREATE INDEX test.idx_name ON test.t_json(JSON_VALUE(json_data, '$.name'));
查看执行计划：
SQL> set autot on;
SQL> SELECT * FROM test.t_json WHERE JSON_VALUE(json_data, '$.name')='张三9984';
ID JSON_DATA
---------- --------------------------------------------------------------------------------
9984 {"name":"张三9984","value":9984}
Execution Plan
----------------------------------------------------------
Plan hash value: 2111867144
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6117 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T_JSON | 1 | 6117 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_NAME | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(JSON_VALUE("JSON_DATA" /*+ LOB_BY_VALUE */ FORMAT OSON , '$.name'
RETURNING VARCHAR2(4000) NULL ON ERROR TYPE(LAX) )='张三9984')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Statistics
----------------------------------------------------------
130 recursive calls
0 db block gets
105 consistent gets
0 physical reads
0 redo size
827 bytes sent via SQL*Net to client
522 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed