Skip to content

Commit a2375c5

Browse files
Drethicsdepold
Drethic
and
sdepold
authored
fix(abstract): patch jsonb operator for pg if value is json (#13780)
* fix(abstract): dialog query-generator for JSON in PG Added check to traverseJSON that checks if value is JSON and passes result to jsonPathExtrationQuery Added isJson param to jsonPathExtractionQuery Updated postgres JSON join arrow to change if value is JSON with '#' will be ignored, and an empty message aborts the commit. * fix(abstract): updated unit tests Added check if value is string and operator is contains Updated unit tests with new expected outputs * fix(abstract): add query generator unit tests Added tests to validate jsonPathExtractionQuery works with and without isJSON * fix(abstract): Fix merge error Removed extra bracket from merge conflict * fix(abstract): Patch failing tests Updated query generator tests to handle all supported DB types * fix(tests): update test name Co-authored-by: Sascha Depold <[email protected]>
1 parent b532ab1 commit a2375c5

2 files changed

Lines changed: 70 additions & 8 deletions

File tree

‎lib/dialects/abstract/query-generator.js‎

Lines changed: 18 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1095,12 +1095,13 @@ class QueryGenerator {
10951095
/**
10961096
* Generates an SQL query that extract JSON property of given path.
10971097
*
1098-
* @param {string} column The JSON column
1099-
* @param {string|Array<string>} [path] The path to extract (optional)
1100-
* @returns {string} The generated sql query
1098+
* @param {string} column The JSON column
1099+
* @param {string|Array<string>} [path] The path to extract (optional)
1100+
* @param {boolean} [isJson] The value is JSON use alt symbols (optional)
1101+
* @returns {string} The generated sql query
11011102
* @private
11021103
*/
1103-
jsonPathExtractionQuery(column, path) {
1104+
jsonPathExtractionQuery(column, path, isJson) {
11041105
let paths = _.toPath(path);
11051106
let pathStr;
11061107
const quotedColumn = this.isIdentifierQuoted(column)
@@ -1135,8 +1136,9 @@ class QueryGenerator {
11351136
return `json_unquote(json_extract(${quotedColumn},${pathStr}))`;
11361137

11371138
case 'postgres':
1139+
const join = isJson ? '#>' : '#>>';
11381140
pathStr = this.escape(`{${paths.join(',')}}`);
1139-
return `(${quotedColumn}#>>${pathStr})`;
1141+
return `(${quotedColumn}${join}${pathStr})`;
11401142

11411143
default:
11421144
throw new Error(`Unsupported ${this.dialect} for JSON operations`);
@@ -2484,11 +2486,21 @@ class QueryGenerator {
24842486
path[path.length - 1] = tmp[0];
24852487
}
24862488

2487-
const pathKey = this.jsonPathExtractionQuery(baseKey, path);
2489+
let pathKey = this.jsonPathExtractionQuery(baseKey, path);
24882490

24892491
if (_.isPlainObject(item)) {
24902492
Utils.getOperators(item).forEach(op => {
24912493
const value = this._toJSONValue(item[op]);
2494+
let isJson = false;
2495+
if (typeof value === 'string' && op === Op.contains) {
2496+
try {
2497+
JSON.stringify(value);
2498+
isJson = true;
2499+
} catch (e) {
2500+
// failed to parse, is not json so isJson remains false
2501+
}
2502+
}
2503+
pathKey = this.jsonPathExtractionQuery(baseKey, path, isJson);
24922504
items.push(this.whereItemQuery(this._castKey(pathKey, value, cast), { [op]: value }));
24932505
});
24942506
_.forOwn(item, (value, itemProp) => {

‎test/unit/dialects/abstract/query-generator.test.js‎

Lines changed: 52 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,8 +2,9 @@
22

33
const chai = require('chai'),
44
expect = chai.expect,
5-
Op = require('sequelize/lib/operators'),
6-
getAbstractQueryGenerator = require('../../support').getAbstractQueryGenerator;
5+
Op = require('../../../../lib/operators'),
6+
Support = require('../../support'),
7+
getAbstractQueryGenerator = Support.getAbstractQueryGenerator;
78
const AbstractQueryGenerator = require('sequelize/lib/dialects/abstract/query-generator');
89

910
describe('QueryGenerator', () => {
@@ -134,6 +135,55 @@ describe('QueryGenerator', () => {
134135
});
135136
});
136137

138+
describe('jsonPathExtractionQuery', () => {
139+
const expectQueryGenerator = (query, assertions) => {
140+
const expectation = assertions[Support.sequelize.dialect.name];
141+
if (!expectation) {
142+
throw new Error(`Undefined expectation for "${Support.sequelize.dialect.name}"!`);
143+
}
144+
return expectation(query);
145+
};
146+
147+
it('should handle isJson parameter true', function() {
148+
const QG = getAbstractQueryGenerator(this.sequelize);
149+
expectQueryGenerator(() => QG.jsonPathExtractionQuery('profile', 'id', true), {
150+
postgres: query => expect(query()).to.equal('(profile#>\'{id}\')'),
151+
sqlite: query => expect(query()).to.equal('json_extract(profile,\'$.id\')'),
152+
mariadb: query => expect(query()).to.equal('json_unquote(json_extract(profile,\'$.id\'))'),
153+
mysql: query => expect(query()).to.equal("json_unquote(json_extract(profile,'$.\\\"id\\\"'))"),
154+
mssql: query => expect(query).to.throw(Error),
155+
snowflake: query => expect(query).to.throw(Error),
156+
db2: query => expect(query).to.throw(Error)
157+
});
158+
});
159+
160+
it('should use default handling if isJson is false', function() {
161+
const QG = getAbstractQueryGenerator(this.sequelize);
162+
expectQueryGenerator(() => QG.jsonPathExtractionQuery('profile', 'id', false), {
163+
postgres: query => expect(query()).to.equal('(profile#>>\'{id}\')'),
164+
sqlite: query => expect(query()).to.equal('json_extract(profile,\'$.id\')'),
165+
mariadb: query => expect(query()).to.equal('json_unquote(json_extract(profile,\'$.id\'))'),
166+
mysql: query => expect(query()).to.equal("json_unquote(json_extract(profile,'$.\\\"id\\\"'))"),
167+
mssql: query => expect(query).to.throw(Error),
168+
snowflake: query => expect(query).to.throw(Error),
169+
db2: query => expect(query).to.throw(Error)
170+
});
171+
});
172+
173+
it('Should use default handling if isJson is not passed', function() {
174+
const QG = getAbstractQueryGenerator(this.sequelize);
175+
expectQueryGenerator(() => QG.jsonPathExtractionQuery('profile', 'id'), {
176+
postgres: query => expect(query()).to.equal('(profile#>>\'{id}\')'),
177+
sqlite: query => expect(query()).to.equal('json_extract(profile,\'$.id\')'),
178+
mariadb: query => expect(query()).to.equal('json_unquote(json_extract(profile,\'$.id\'))'),
179+
mysql: query => expect(query()).to.equal("json_unquote(json_extract(profile,'$.\\\"id\\\"'))"),
180+
mssql: query => expect(query).to.throw(Error),
181+
snowflake: query => expect(query).to.throw(Error),
182+
db2: query => expect(query).to.throw(Error)
183+
});
184+
});
185+
});
186+
137187
describe('queryIdentifier', () => {
138188
it('should throw an error if call base quoteIdentifier', function() {
139189
const QG = new AbstractQueryGenerator({ sequelize: this.sequelize, _dialect: this.sequelize.dialect });

0 commit comments

Comments
 (0)