forked from oracle-samples/oracle-db-examples
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathselectjson.js
More file actions
159 lines (148 loc) · 4.92 KB
/
selectjson.js
File metadata and controls
159 lines (148 loc) · 4.92 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
/* Copyright (c) 2015, 2018, Oracle and/or its affiliates. All rights reserved. */
/******************************************************************************
*
* You may not use the identified files except in compliance with the Apache
* License, Version 2.0 (the "License.")
*
* You may obtain a copy of the License at
* http://www.apache.org/licenses/LICENSE-2.0.
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*
* See the License for the specific language governing permissions and
* limitations under the License.
*
* NAME
* selectjson.js
*
* DESCRIPTION
* Shows some JSON features of Oracle Database 12c.
* Requires Oracle Database 12.1.0.2, which has extensive JSON datatype support.
* See https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=ADJSN
*
* Uses Oracle's sample HR schema.
* Also run demo.sql to create the required extra table or do:
*
* DROP TABLE j_purchaseorder;
* CREATE TABLE j_purchaseorder
* (po_document VARCHAR2(4000) CONSTRAINT ensure_json CHECK (po_document IS JSON));
*
*****************************************************************************/
var async = require('async');
var oracledb = require('oracledb');
var dbConfig = require('./dbconfig.js');
var doconnect = function(cb) {
oracledb.getConnection(dbConfig, cb);
};
var dorelease = function(conn) {
conn.close(function (err) {
if (err)
console.error(err.message);
});
};
var checkver = function (conn, cb) {
if (conn.oracleServerVersion < 1201000200) {
return cb(new Error('This example only works with Oracle Database 12.1.0.2 or greater'), conn);
} else {
return cb(null, conn);
}
};
var doinsert = function (conn, cb) {
var data = { "userId": 1, "userName": "Chris", "location": "Australia" };
var s = JSON.stringify(data);
conn.execute(
"INSERT INTO j_purchaseorder (po_document) VALUES (:bv)",
[s], // bind the JSON string for inserting into the JSON column.
{ autoCommit: true },
function (err) {
if (err) {
return cb(err, conn);
} else {
console.log("Data inserted successfully.");
return cb(null, conn);
}
});
};
// 1. Selecting JSON stored in a VARCHAR2 column
var dojsonquery = function (conn, cb) {
console.log('1. Selecting JSON stored in a VARCHAR2 column');
conn.execute(
"SELECT po_document FROM j_purchaseorder WHERE JSON_EXISTS (po_document, '$.location')",
function(err, result) {
if (err) {
return cb(err, conn);
} else {
var js = JSON.parse(result.rows[0][0]); // just show first record
console.log('Query results: ', js);
return cb(null, conn);
}
});
};
// 2. Extract a value from a JSON column. This syntax requires Oracle Database 12.2
var dorelationalquerydot = function (conn, cb) {
console.log('2. Using dot-notation to extract a value from a JSON column');
conn.execute(
"SELECT po.po_document.location FROM j_purchaseorder po",
function(err, result) {
if (err) {
return cb(err, conn);
} else {
console.log('Query results: ', result.rows[0][0]); // just show first record
return cb(null, conn);
}
});
};
// 3. Using JSON_VALUE to extract a value from a JSON column
var dorelationalquery = function (conn, cb) {
console.log('3. Using JSON_VALUE to extract a value from a JSON column');
conn.execute(
"SELECT JSON_VALUE(po_document, '$.location') FROM j_purchaseorder",
function(err, result) {
if (err) {
return cb(err, conn);
} else {
console.log('Query results: ', result.rows[0][0]); // just show first record
return cb(null, conn);
}
});
};
// 4. Using JSON_OBJECT to extract relational data as JSON
var dojsonfromrelational = function (conn, cb) {
console.log('4. Using JSON_OBJECT to extract relational data as JSON');
if (conn.oracleServerVersion < 1202000000) { // JSON_OBJECT is new in Oracle Database 12.2
console.log('The JSON_OBJECT example only works with Oracle Database 12.2 or greater');
return cb(null, conn);
} else {
conn.execute(
`SELECT JSON_OBJECT ('deptId' IS d.department_id, 'name' IS d.department_name) department
FROM departments d
WHERE department_id < :did`,
[50],
function(err, result) {
if (err) {
return cb(err, conn);
} else {
for (var i = 0; i < result.rows.length; i++)
console.log(result.rows[i][0]);
return cb(null, conn);
}
});
}
};
async.waterfall(
[
doconnect,
checkver,
doinsert,
dojsonquery,
dorelationalquerydot,
dorelationalquery,
dojsonfromrelational
],
function (err, conn) {
if (err) { console.error("In waterfall error cb: ==>", err, "<=="); }
if (conn)
dorelease(conn);
});