json_test

View All Public Scripts Login to Run Script

Script Name json_test
Visibility Unlisted - anyone with the share link can access
Description No description provided
Area SQL General
Contributor Oracle
Created Sunday August 27, 2017

Statement 1
```
{   
```
Statement 2
```
	   "user_key":1, 
```
Statement 3
```
	   "user_name":"Arthur Stygall", 
```
Statement 4
```
	   "user_type":"End-user", 
```
Statement 5
```
	   "user_status":"Active", 
```
Statement 6
```
	   "user_emp_type":"Full-Time", 
```
Statement 7
```
	   "user_login":"arthurstygall", 
```
Statement 8
```
	   "user_password":"test1243", 
```
Statement 9
```
	   "user_address":{   
```
Statement 10
```
			"street":"200 Sporting Green", 
```
Statement 11
```
			"city":"South San Francisco", 
```
Statement 12
```
			"state":"CA", 
```
Statement 13
```
			"zipCode":99236, 
```

Statement 14

			"country":"United States of America"

Statement 15
```
	   }, 
```
Statement 16
```
	   "user_phone":[   
```
Statement 17
```
		 {   
```
Statement 18
```
		    "type":"Office", 
```
Statement 19
```
		    "number":"823-555-9969" 
```
Statement 20
```
		 }, 
```
Statement 21
```
		 {   
```
Statement 22
```
		    "type":"Mobile", 
```
Statement 23
```
		    "number":"976-555-1234" 
```
Statement 24
```
		 } 
```
Statement 25
```
	   ], 
```

Statement 26

	   "user_email":"arthur.stygall@achme.com",

Statement 27
```
	   "user_create_date":"19-JAN-10", 
```
Statement 28
```
	   "user_manager_name":"Ron Howard", 
```
Statement 29
```
	   "user_experience":[   
```
Statement 30
```
		      {   
```
Statement 31
```
			 "idx":1, 
```
Statement 32
```
			 "info":{  		     
```
Statement 33
```
				    "Company":"Microsoft", 
```
Statement 34
```
				    "Role":"Architect", 
```

Statement 35

				    "Description":"Technical architect for Microsoft Azure Cloud"

Statement 36
```
				} 
```
Statement 37
```
		      }, 
```
Statement 38
```
		      {   
```
Statement 39
```
			 "idx":2, 
```
Statement 40
```
			 "info":{   
```
Statement 41
```
				    "Company":"Adobe", 
```
Statement 42
```
				    "Role":"PMTS", 
```

Statement 43

				    "Description":"Lead developer for Adobe Flash technology"

Statement 44
```
				} 
```
Statement 45
```
		      } 
```
Statement 46
```
	] 
```
Statement 47
```
} 
```
Statement 48
```
DROP TABLE test_user
```

Statement 49

CREATE TABLE test_user ( 
  id			RAW(16) NOT NULL, 
  loading_date		TIMESTAMP(6) WITH TIME ZONE, 
  user_json		CLOB CHECK (user_json IS JSON) 
)

Statement 50

INSERT INTO test_user 
  VALUES ( 
    SYS_GUID(), 
    SYSTIMESTAMP, 
    '{   
	   "user_key":1, 
	   "user_name":"Arthur Stygall", 
	   "user_type":"End-user", 
	   "user_status":"Active", 
	   "user_emp_type":"Full-Time", 
	   "user_login":"arthurstygall", 
	   "user_password":"test1243", 
	   "user_address":{   
			"street":"200 Sporting Green", 
			"city":"South San Francisco", 
			"state":"CA", 
			"zipCode":99236, 
			"country":"United States of America" 
	   }, 
	   "user_phone":[   
		 {   
		    "type":"Office", 
		    "number":"823-555-9969" 
		 }, 
		 {   
		    "type":"Mobile", 
		    "number":"976-555-1234" 
		 } 
	   ], 
	   "user_email":"arthur.stygall@achme.com", 
	   "user_create_date":"19-JAN-10", 
	   "user_manager_name":"Ron Howard", 
	   "user_experience":[   
		      {   
			 "idx":1, 
			 "info":{  		     
				    "Company":"Microsoft", 
				    "Role":"Architect", 
				    "Description":"Technical architect for Microsoft Azure Cloud" 
				} 
		      }, 
		      {   
			 "idx":2, 
			 "info":{   
				    "Company":"Adobe", 
				    "Role":"PMTS", 
				    "Description":"Lead developer for Adobe Flash technology" 
				} 
		      } 
	] 
}' 
)

Statement 51
```
COMMIT
```

Statement 52

CREATE OR REPLACE VIEW view_test_user 
AS 
SELECT d.* 
FROM test_user p, 
       JSON_TABLE( 
         p.user_json, 
         '$' 
         columns ( 
		USER_KEY			VARCHAR2(30 CHAR)    path '$.user_key', 
		USER_NAME			VARCHAR2(30 CHAR)    path '$.user_name', 
		USER_TYPE			VARCHAR2(128 CHAR)   path '$.user_type', 
		USER_STATUS			VARCHAR2(10 CHAR)    path '$.user_status', 
		USER_EMP_TYPE			VARCHAR2(16)         path '$.user_emp_type', 
		USER_LOGIN			VARCHAR2(16)         path '$.user_login', 
		USER_PASSWORD			VARCHAR2(16)         path '$.user_password', 
		USER_DTLS_STREET		VARCHAR2(32 CHAR)    path '$.user_address.street', 
		USER_DTLS_CITY			VARCHAR2(32 CHAR)    path '$.user_address.city', 
		USER_DTLS_COUNTY		VARCHAR2(32 CHAR)    path '$.user_address.county', 
		USER_DTLS_POSTCODE		VARCHAR2(10 CHAR)    path '$.user_address.postcode', 
		USER_DTLS_STATE			VARCHAR2(2 CHAR)     path '$.user_address.state', 
		USER_DTLS_PROVINCE		VARCHAR2(2 CHAR)     path '$.user_address.province', 
		USER_DTLS_ZIP			VARCHAR2(8 CHAR)     path '$.user_address.zipCode', 
		USER_DTLS_COUNTRY		VARCHAR2(32 CHAR)    path '$.user_address.country', 
		NESTED PATH '$.user_phone[*]' 
		columns ( 
			USER_PHONE_TYPE		VARCHAR2(24 CHAR)    path '$.type',  
			USER_PHONE_NUMBER	VARCHAR2(24 CHAR)    path '$.number' 
			), 
		USER_EMAIL			VARCHAR2(1024 CHAR)  path '$.user_email', 
		USER_CREATE_DATE		VARCHAR2(32 CHAR)    path '$.user_create_date', 
		USER_MANAGER_NAME		VARCHAR2(32 CHAR)    path '$.user_manager_name', 
		NESTED PATH '$.user_experience[*]' 
		columns ( 
			USER_EXP_IDX    NUMBER(38)	     path '$.idx',  
			USER_EXP_CMPNY	VARCHAR2(256 CHAR)   path '$.info.Company',  
			USER_EXP_ROLE	VARCHAR2(14 CHAR)    path '$.info.Role',  
			USER_EXP_DESC	VARCHAR2(1024 CHAR)  path '$.info.Description' 
			) 
		) 
	) d

Statement 53
```
SELECT * FROM view_test_user
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database