🧾 JSON
JSON_VALUE, JSON_TABLE and JSON generation in Oracle.
| Snippet | |
|---|---|
| ☆JSON_VALUE — Extract a scalar from a JSON document |
SELECT JSON_VALUE(doc, '$.name') AS name FROM t_json;
|
| ☆JSON_TABLE — Shred a JSON array into relational rows |
SELECT jt.* FROM t_json j, JSON_TABLE(j.doc, '$.items[*]' COLUMNS (id NUMBER PATH '$.id', name VARCHAR2(50) PATH '$.name')) jt;
|
| ☆JSON_OBJECT — Build JSON documents from rows |
SELECT JSON_OBJECT('id' VALUE id, 'name' VALUE name) FROM employees;
|
| ☆JSON_ARRAYAGG — Aggregate rows into a JSON array |
SELECT JSON_ARRAYAGG(name) FROM employees WHERE deptno = 10;
|
| ☆IS JSON Check — Validate that a column holds JSON |
SELECT * FROM t_json WHERE doc IS JSON;
|