-- Oracle 12c: JSON_Value Function


SELECT  JSON_VALUE('{a:100}','$.a') as a_value
FROM DUAL;

Get_Value_Of_A

SELECT  JSON_VALUE('{a:{b:100}}','$.a.b') as a_value
FROM DUAL;

Get_Inner_Value_of_A

SELECT  JSON_VALUE('{a:{x:100},
                     b:{y:200},
                     c:{z:300}
                    }', '$.*.y') as b_y_value
FROM DUAL;

Get_Value_Y

SELECT  JSON_VALUE('{a:100}','$.a' RETURNING NUMBER) as a_Numeric_value 
FROM DUAL;

Returning_Number


-- String Value in JSON_Value Function

SELECT
 JSON_VALUE(qt.json_doc,'$.lastName')||', '||
 JSON_VALUE(qt.json_doc,'$.firstName')  as user_full_name,
 ROUND((TRUNC(SYSDATE)-TO_DATE(JSON_VALUE(qt.json_doc,'$.dob'),'Mon-dd-yyyy'))/365.25,0) as user_age 
FROM
(SELECT
   '{firstName:"fName",
     lastName:"lName",
     dob:"Feb-29-1980"
    }' as json_doc
 FROM DUAL
) qt
;

SELECT JSON_VALUE(qt.json_doc,'$.mName' DEFAULT 'No Middle Name/Initial entry'
         ON ERROR)  as Middle_Name
FROM
(SELECT
   '{firstName:"fName",
     lastName:"lName",
     dob:"Feb-29-1980"
    }' as json_doc
 FROM DUAL
) qt
;

-- Array Value in JSON_Value Function

SELECT
 JSON_VALUE('[0, 1, 2, 3]','$[1]') as second_value
FROM DUAL;

SELECT
 JSON_VALUE('{a:[10, 20, 30, 40]}','$.a[0]') as first_value
FROM DUAL;

SELECT
 JSON_VALUE('{"a" : "2016-01-07T12:47:57"}','$.a' RETURNING 
  TIMESTAMP) time_stamp
 ,CAST(JSON_VALUE('{"a" : "2016-01-07T12:47:57"}','$.a' RETURNING 
  TIMESTAMP)  AS DATE) date_value
FROM DUAL;