메뉴 건너뛰기

Korea Oracle User Group

Guru's Articles

Oracle 12c SQL – Using JSON

명품관 2015.12.29 16:32 조회 수 : 148

JSON Is Part of Oracle 12c

One of the more-exciting features (at least to me) of Oracle 12c is the support for JSON now built into the database.

What is JSON?
JSON (JavaScript Object Notation) is a language-independent open data format (see www.json.org for more). JSON stores text in name-value pairs. Originally usedin JavaScript, JSON is now also in: Java, R, .NET, PHP, Python, Node.js, and Oracle. JSON is most-often used for data interchange but is frequently used to pass data to and from REST-style web services. Increasingly JSON is becoming favored for big-data persistence.

12c (12.1.0.2) and JSON
The Oracle 12c patch-set 2 (12.1.0.2) added JSON. JSON documents are stored as VARCHAR2, CLOB, or BLOB data type. JSON data works with all existing Oracle features including SQL and Analytics. Oracle 12c supports path-based queries of JSON data stored in the database using JSON Path Language and JSON Path Expressions. In addition, JSON may be used in SQL via SQL/JSON views and JSON documents may be indexed.

JSON-XML Similarities
JSON is text only, just like XML and thus is an excellent vehicle for data interchange. JSON and XML are “human readable” and “self-describing” (sort of…). JSON and XML are both hierarchical (data sets nested within data sets). Finally, JSON and XML offer validation capability; XML’s is more mature and capable today.
JSON-XML Dissimilarities
XML is verbose, JSON is shorter (but only slightly). The reduced size is largely because JSON has no end tags; end tags are required in XML. So, JSON is quicker to read and write since it is simpler and briefer. Reading XML documents requires “walking the DOM” – JSON does not. In addition, JSON works more easily and is faster than XML when working with AJAX. Finally, XML documents must be tested for “well-formed”-ness before processing (the reason often cited for XML performance issues).

JSON Syntax
Data is stored using comma-delimited name/value pairs as follows:

Field name/key (surrounded by double quotes)
Colon “:”,
Value (double quotes surround strings, numeric values unquoted, boolean true/false/null

Objects are included inside curly braces “{“ & “}”

        {"lastName":"King"}

Arrays of objects use brackets “[“ &  “]” and commas

        [ {"lastName": "King"},
           {"lastName": "Manzo"} ]

XML File
Here is an example of a simple XML file:

<?xml version="1.0"?>

  
    Learning XML
    Eric T. Ray
    O'Reilly   
  
    XML Bible
    Elliotte Rusty Harold
    IDG Books   
  
    XML by Example
    Sean McGrath   

JSON File
Here is an example of a JSON file containing the same data as the XML file above. Note, does not seem much simpler…

{"myBooks":
  [ {"book": {
         "name":"Learning XML",
         "author":"Eric T. Ray",
         "publisher":"O'Reilly" }
     },
     {"book": {
         "name":"XML Bible",
         "author":"Elliotte Rusty Harold",
         "publisher":"IDG Books" }
     },
     {"book": {
         "name":"XML by Example",
         "author":"Sean McGrath",
         "publisher":"Prentice-Hall" }
     }
  ]}

Oracle as JSON Data Store
JSON documents are stored in the database using existing data types VARCHAR2, CLOB and BLOB for character mode JSON (rather than creating a new datatype like Oracle used for XML) . In addition, external JSON data sources (including those in HDFS file system) are accessible through external tables. Using JSON queries JSON data is available to SQL via relational views based upon JSON_TABLE. To aid performance Oracle JSON documents may be indexed; JSON paths may use functional indexes or CTXX indexes.

JSON SQL
JSON content is accessible from SQL and may be treated as JSON using:

JSON conditional operators
JSON functions
JSON operators and functions use JSON Path language to navigate JSON objects

JSON Conditionals
JSON content is accessible from SQL via new condition operators:

IS JSON
Validates JSON, often in used in CHECK constraint and/or WHERE (IS / IS NOT)
JSON_EXISTS
Returns true if JSON path exists in document.
JSON_TEXTCONTAINS
Returns true if text string is found in JSON property values (requires Oracle Text).

JSON Functions
JSON content is also accessed via new functions:

JSON_VALUE
Used to query a scalar value from a JSON document.
JSON_QUERY
Used to query all or part of a JSON document.
JSON_TABLE
Used to query JSON document and create relational-style columns.

IS JSON – IS NOT JSON
IS JSON returns TRUE if specified expression is a valid JSON document.

**expr**       IS JSON | IS NOT JSON
        FORMAT JSON
        STRICT|LAX
        WITH|WITHOUT UNIQUE KEYS
  • IS true if valid JSON, IS NOT true if not JSON
  • FORMAT JSON (only required for BLOB data)
  • STRICT / LAX strictness level required for true
  • WITH / WITHOUT if unique keys are required

IS JSON Check Constraint

create table deptj
(id raw(16) not null,
 dept_info clob constraint deptjson 
 check (dept_info is json)
);

create table deptj
(id raw(16) not null,
  dept_info clob constraint deptjson 
  check (dept_info is json strict));

IS JSON in WHERE

select id,dept_info
from deptj
where dept_info is json

select id,dept_info
from deptj
where dept_info is json strict;

select id,dept_info
from deptj
where dept_info is json format json strict;

JSON and DML

Inserting (or updating) data into JSON columns is accomplished using standard SQL, it’s just that the text being inserted/updated is JSON.

insert into deptj values
(sys_guid(),
 '{"departments":{
   "DEPTNO": 10, "DNAME": "ACCOUNTING", "LOC": "NEW YORK",
    "deptemps": [
                 { "EMPNO": 7782,
                   "ENAME": "CLARK",
                   "JOB": "MANAGER",
                   "MGR": 7839,
                   "HIREDATE": "09-JUN-81",
                   "pay":{
                          "SAL": 2450,
                          "COMM": null},
                   "DEPTNO": "10"                   },
                  /* more */

Simple JSON Query

When JSON data is queried using regular SQL, the JSON data is returned as is:

select dept_info
from deptj;
DEPT_INFO
---------------------------------------------
{"departments":{
 "DEPTNO": 10,
 "DNAME": "ACCOUNTING",
 "LOC": "NEW YORK",
 "deptemps": [
               {
                "EMPNO": 7782,
                "ENAME": "CLARK",
                **** more ****

JSON Path

SQL using the new JSON functions do so using a special JSON path syntax.

$.jsonpath    Identifies path in JSON document

$. (required)
jsonpath (describes part of JSON document to be searched, if omitted entire document returned

JSON path is used by JSON_QUERY, JSON_VALUE, JSON_TABLE, JSON_EXISTS, JSON_TEXTCONTAINS.

JSON_VALUE
The JSON_VALUE function finds a value in a JSON document and returns it as VARCHAR2 or NUMBER to SQL.

JSON_VALUE (expr | expr FORMAT JSON,
    '$.jsonpath’
    RETURNING VARCHAR2(n) | NUMBER(n,n)
    ERROR | NULL | DEFAULT xxx ON ERROR
)

Query with JSON_VALUE

select json_value(dept_info, '$.departments.DNAME')
from deptj;
DNAME
-----------------
ACCOUNTING
RESEARCH
SALES
OPERATIONS

JSON_VALUE returns scalar values from JSON data

    select json_value(dept_info
        , '$.departments.DNAME')
        from deptj;

    select json_value(dept_info
        , '$.departments.deptemps[0].ENAME')
        from deptj;

    select dept_info
        from deptj
        where json_value(dept_info,
             '$[0].departments.DEPTNO') = '10'

JSON_QUERY
JSON_QUERY finds values in a JSON document and returns a character string.

JSON_QUERY(expr | expr FORMAT JSON, ‘$.jsonpath’    RETURNING VARCHAR2(n)     PRETTY    ASCII

  WITH | WITH CONDITIONAL | WITH UNCONDITIONAL 
   WRAPPER | ARRAY WRAPPER   ERROR | NULL | DEFAULT xxx ON ERROR )

Use PRETTY to pretty-print output
Use ASCII to escape non-ASCII characters
WRAPPER needed if multiple values or scalar returned

Query examples using JSON_QUERY:

select json_query(dept_info,    '$.departments.DEPTEMPS.ENAME'         with unconditional wrapper)
   from deptj dj;
select json_query(dept_info,
    '$.departments.DEPTEMPS.ENAME'         pretty with wrapper)
   from deptj dj;
select json_query(dept_info,    '$.departments.DEPTEMPS[0].ENAME'         pretty with conditional wrapper)
   from deptj dj;

JSON_TABLE
JSON_TABLE maps JSON data into relational rows and columns. JSON_TABLE is frequently used in dynamic queries (in FROM clause) and when used in a view allow “normal SQL” access to JSON data.

JSON_TABLE ( expr | expr FORMAT JSON,'$.jsonpath’
    ERROR | NULL | DEFAULT xxx ON ERROR
    COLUMNS (
       colname1 datatype
         EXISTS PATH '$.jsonpath’
        ERROR|NULL|DEFAULT xxx ON ERROR,
       colname2 datatype FORMAT JSON
        jsonquerywrapper PATH '$.jsonpath’
        ERROR|NULL|DEFAULT xxx ON ERROR,
       colname3 datatype PATH '$.jsonpath’
        ERROR|NULL|DEFAULT xxx ON ERROR,
        NESTED PATH '$.jsonpath' COLUMNS (...),
       colname4 FOR ORDINALITY    )

Here is an example query using a JSON_TABLE:

select dname,ename,job,sal
from deptj, json_table(dept_info,'$.departments'
            columns (dname varchar2(15) path '$.DNAME'
            ,nested path '$.deptemps[*]'
              columns (ename varchar2(20) path '$.ENAME'
              ,job varchar2(20) path '$.JOB'
                ,nested path '$.pay'
                  columns (sal number path '$.SAL')
              )
));
DNAME         ENAME        JOB         SAL
------------ -------   --------   ----------
ACCOUNTING     CLARK   MANAGER          2450
ACCOUNTING     KING    PRESIDENT        5000
**** more ****
JSON and Indexing
JSON function/expression based indexes may be “b-tree” (normal) or bitmap. JSON full-search context indexes may also be created
Function/Expression-based indexes are created as follows:

create unique index deptj_ix
  on deptj    (json_value             (dept_info,'$.departments.DEPTNO'));

create bitmap index deptj_emp_ix
    on deptj
    (json_value(dept_info,        '$.departments.DEPTEMPS.EMPNO'));

Full-search context index are created as follows:

create index deptj_ctx_ix 
  on deptj (dept_info)
    indextype is ctxsys.context     parameters ('section group         CTXSYS.JSON_SECTION_GROUP         sync (on commit)’);

Context indexes are not just for TEXTCONTAINS they may also be used for JSON_VALUE and JSON_EXISTS tests.

Wrapping it all Up
Oracle 12c support for JSON is timely and useful. Furthermore, JSON is coming to an application near you soon and you need to be ready.
JSON is the most-common mechanism for interacting with AJAX, becoming the most-common mechanism for mobile/web service data (especially REST/HTTP API based services), and JSON is the cornerstone of several “big data” data stores.
Take the time to learn JSON, JSON syntax, and Oracle’s implementation.

 

출처

번호 제목 글쓴이 날짜 조회 수
공지 Guru's Article 게시판 용도 ecrossoug 2015.11.18 12
27 V$EVENT_NAME 뷰의 Name 컬럼에 정의된 event name에서 오는 오해 명품관 2017.03.08 50
26 Can I apply a BP on top of a PSU? Or vice versa? 명품관 2016.06.01 113
25 How to change the database name in 12c 명품관 2016.05.31 46
24 How to Recover Data (Without a Backup!) 명품관 2016.05.11 86
23 On ROWNUM and Limiting Results (오라클 매거진 : AskTom) 명품관 2016.04.28 77
22 DDL Logging - 12c (조나단 루이스) 명품관 2016.04.26 99
21 11.1.0.6 부터 지원하는 Oracle Online Patching New Feature 명품관 2016.04.22 85
20 Quick tip on Function Based Indexes 명품관 2016.04.19 35
19 Oracle Linux 6.7 with Oracle 11.2.0.4 RAC 명품관 2016.04.15 99
18 Parameter Recommendations for Oracle Database 12c - Part II 명품관 2016.03.18 71
17 Parameter Recommendations for Oracle Database 12c - Part I 명품관 2016.03.07 240
16 Hybrid Columnar Compression Common Questions 명품관 2016.03.04 34
15 What is an In-Memory Compression Unit (IMCU)? 명품관 2016.02.24 48
14 Why You Can Get ORA-00942 Errors with Flashback Query 명품관 2016.02.01 58
13 How do I capture a 10053 trace for a SQL statement called in a PL/SQL package? 명품관 2016.01.06 39
12 (유투브) KISS series on Analytics: Dealing with null - Connor McDonald 명품관 2016.01.05 28
11 Upgrade a Pluggable Database in Oracle 12c 명품관 2015.12.30 11
» Oracle 12c SQL – Using JSON 명품관 2015.12.29 148
9 Oracle Enterprise Manager Cloud Control 13c Release 1 (13.1.0.0) Installation on Oracle Linux 6 and 7 명품관 2015.12.23 60
8 Top 5 SQL Monitor Features file 명품관 2015.12.01 17
위로