Retrieving data that is inside a JSON document

You can use the JSON_VAL function to retrieve data that is inside a JSON document.

Before you begin

The examples in this procedure assume that you have created and populated the following tables:

The CLAIMS table:
- Define CLAIMS as shown in Creating a column to store JSON data.
- Populate CLAIMS as shown in Inserting JSON data into a table.

The ASSOCIATES table:

Define ASSOCIATES like this:

CREATE TABLE ASSOCIATES        
 (ASSOC_ID CHAR(6), NAME VARCHAR(40));

Populate ASSOCIATES like this:

INSERT INTO ASSOCIATES (ASSOC_ID, NAME) VALUES ('000100','Paula Garcia');
INSERT INTO ASSOCIATES (ASSOC_ID, NAME) VALUES ('000200','Steven Barnes');
INSERT INTO ASSOCIATES (ASSOC_ID, NAME) VALUES ('000300','Roger McCarthy');

Procedure

To retrieve data that is inside a JSON document:

Issue the SELECT SQL statement and specify the JSON_VAL function.

Example

Start of change The following example uses the JSON_VAL function to extract data from a JSON document for retrieval through a SELECT clause. End of change

SELECT CLAIM_ID, 
 JSON_VAL(CALL_RECORDS, 'satisfactionSurveyResult', 's:25' ) 
 AS "SATISFACTION SURVEY RESULT"
 FROM CLAIMS 
 WHERE CLAIM_ID = 'AB0033789';

The output should look like this.

CLAIM_ID	SATISFACTION SURVEY RESULT
AB0033789	needs improvement

The following example uses the JSON_VAL function to extract a value that is inside the JSON document for use in a WHERE clause predicate:

SELECT CUSTOMER_ID, CLAIM_ID, SYSTOOLS.BSON2JSON(CALL_RECORDS) 
 AS "CALL RECORD"
 FROM SYSADM.CLAIMS 
 WHERE JSON_VAL(CALL_RECORDS,
  'satisfactionSurveyResult', 's:25') = 'needs improvement' ;

The output should look like this.

CUSTOMER_ID	CLAIM_ID	CALL RECORD
09736814	AB0033789	{"calls":[{"dateOfCall":"2014-02-26","associate":"Steven Barnes","conversationNotes":"Customer provided details of accident and witness contact information. Stated that her local agent was unavailable."}],"satisfactionSurveyResult":"needs improvement"}

The following example uses the JSON_VAL function to extract a value that is inside the JSON document for use in a JOIN predicate:

SELECT C.CUSTOMER_ID, C.CLAIM_ID, SYSTOOLS.BSON2JSON(C.CALL_RECORDS) 
 AS "CALL RECORD", 
 A.NAME, A.ASSOC_ID
 FROM CLAIMS C, ASSOCIATES A
 WHERE JSON_VAL(C.CALL_RECORDS, 'satisfactionSurveyResult', 's:25') = 
  'needs improvement'
  AND JSON_VAL(C.CALL_RECORDS, 'calls.associate', 's:40') = A.NAME;

You should receive results like these:

CUSTOMER_ID	CLAIM_ID	CALL RECORD	NAME	ASSOC_ID
09736814	AB0033789	{"calls":[{"dateOfCall":"2014-02-26","associate":"Steven Barnes","conversationNotes":"Customer provided details of accident and witness contact information. Stated that her local agent was unavailable."}],"satisfactionSurveyResult":"needs improvement"}	Steven Barnes	000200