-- Oracle 12c: JSON_QUERY
-- Get all values

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$') op_values
FROM DUAL
;

--Get value associated with a is 100

SELECT JSON_QUERY('{a:100, b:200, c:300}', '$.a' WITH WRAPPER) op_value 
FROM DUAL
;

-- $.a = 450, $.b = 200 and resultant sum of 650

SELECT  
 SUM(REGEXP_REPLACE(ref_value,'[]^[+]')) sum_ref_value 
FROM 
(SELECT JSON_QUERY('{a:450, b:150, c:300}', '$.a' WITH WRAPPER) ref_value 
 FROM DUAL
 UNION
 SELECT JSON_QUERY('{a:500, b:200, c:350}', '$.b' WITH WRAPPER) ref_value 
 FROM DUAL
);



SELECT
 JSON_QUERY(
  '{"HCPCSCode"      : "99001",
    "PatientID"      : "1A2B3C4D5E",
    "LocationCode"   : "NY001",
    "ProviderID"     : "1A1-2B2-3C3",
    "ProviderAddr"   : {"AddrNo":  100, "Type":"Number",
                        "AddrIdx": "A", "Type":"String",
                        "AddrLine1": "1st Avenue", "Type":"String",
                        "AddrLine2": " ", "Type":"String",
                        "City": "New York", "Type":"String",
                        "State": "NY", "Type":"String",
                        "ZipCode": 10001, "Type":"String",
                        "Phone": 2120000000, "Type":"Number"
                       },
    "InsuranceCo"    : "A1B2C3, Insurance Co.",
    "PolicyNo"       : "9O8B7I6J5A4BZ0Z"
   }',
   '$.HCPCSCode'
  WITH WRAPPER)  HCPCS_Code 
FROM DUAL;



--Get the second value in an array

SELECT JSON_QUERY('[0,1,2,3,4,5]', '$[2]' WITH WRAPPER) value2 
FROM DUAL
;



--Get all values in an array

SELECT JSON_QUERY('[0,1,2,3,4,5]', '$[*]' WITH WRAPPER) all_values
FROM DUAL
;