oracle 23ai json简单使用

创建带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