Repository: knex/knex
Pull request #4903 (2021-12-20 22:29:55)

Create multiple PKs with increments
- Resolve the case when a increments column is created in same time that primary keys. Now you can specify a increments columns with or without primary keys: 
   * If a primary key is specified, the increment column is added to composite key,
   * If a primary key is not specified, the increment column is set to primary key.

This two cases works :

```js
knex.schema.createTable('table_multiple_keys', function (t) {
  t.primary(['id', 'second_id', 'other_col']);
  t.increments('id');

  t.string('second_id', 16).notNullable();
  t.integer('other_col').notNullable();
});
```

```js
knex.schema.createTable('table_multiple_keys', function (t) {
  t.primary(['second_id', 'other_col']);
  t.increments('id');

  t.string('second_id', 16).notNullable();
  t.integer('other_col').notNullable();
});
```
- Solve tricky cases : 
     * SQLite : it's not possible to specify autoincrement with composite key, autoincrement need to be primary key. I solve it by transform primary keys in unique index if autoincrement with primary keys is specified.
     * MYSQL don't support autoincrement without primary keys so it's not possible to add autoincrement column without primary key and alter table to add primary key in second query. I solve it by add classic int column, add composite primary key and transform the column to autoincrement.

- Closes https://github.com/knex/knex/issues/385

OlivierCavadenti: @kibertoad CI seems KO :X
kibertoad: @OlivierCavadenti Great job, as always! Do you think this needs additional documentation?

Patch:
diff --git a/lib/client.js b/lib/client.js
index ec554215..1525fa0b 100644
--- a/lib/client.js
+++ b/lib/client.js
@@ -30,6 +30,7 @@ const Logger = require('./logger');
 const { POOL_CONFIG_OPTIONS } = require('./constants');
 const ViewBuilder = require('./schema/viewbuilder.js');
 const ViewCompiler = require('./schema/viewcompiler.js');
+const isPlainObject = require('lodash/isPlainObject');
 
 const debug = require('debug')('knex:client');
 
@@ -393,8 +394,13 @@ class Client extends EventEmitter {
       i = -1;
     while (++i < values.length) {
       if (i > 0) str += ', ';
+      let value = values[i];
+      // json columns can have object in values.
+      if (isPlainObject(value)) {
+        value = JSON.stringify(value);
+      }
       str += this.parameter(
-        values[i] === undefined ? notSetValue : values[i],
+        value === undefined ? notSetValue : value,
         builder,
         bindingsHolder
       );
@@ -443,6 +449,12 @@ class Client extends EventEmitter {
   processPassedConnection(connection) {
     // Default implementation is noop
   }
+
+  toPathForJson(jsonPath) {
+    // By default, we want a json path, so if this function is not overriden,
+    // we return the path.
+    return jsonPath;
+  }
 }
 
 Object.assign(Client.prototype, {
diff --git a/lib/dialects/cockroachdb/crdb-querycompiler.js b/lib/dialects/cockroachdb/crdb-querycompiler.js
index 9100d521..5b9ce699 100644
--- a/lib/dialects/cockroachdb/crdb-querycompiler.js
+++ b/lib/dialects/cockroachdb/crdb-querycompiler.js
@@ -1,4 +1,9 @@
 const QueryCompiler_PG = require('../postgres/query/pg-querycompiler');
+const {
+  columnize: columnize_,
+  wrap: wrap_,
+  operator: operator_,
+} = require('../../formatter/wrappingFormatter');
 
 class QueryCompiler_CRDB extends QueryCompiler_PG {
   truncate() {
@@ -22,6 +27,91 @@ class QueryCompiler_CRDB extends QueryCompiler_PG {
     const body = this._insertBody(upsertValues);
     return body === '' ? '' : sql + body;
   }
+
+  whereJsonPath(statement) {
+    let castValue = '';
+    if (parseInt(statement.value)) {
+      castValue = '::int';
+    } else if (parseFloat(statement.value)) {
+      castValue = '::float';
+    } else {
+      castValue = " #>> '{}'";
+    }
+    return `json_extract_path(${this._columnClause(
+      statement
+    )}, ${this.client.toArrayPathFromJsonPath(
+      statement.jsonPath,
+      this.builder,
+      this.bindingsHolder
+    )})${castValue} ${operator_(
+      statement.operator,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )} ${this._jsonValueClause(statement)}`;
+  }
+
+  // Json common functions
+  _jsonExtract(nameFunction, params) {
+    let extractions;
+    if (Array.isArray(params.column)) {
+      extractions = params.column;
+    } else {
+      extractions = [params];
+    }
+    return extractions
+      .map((extraction) => {
+        const jsonCol = `json_extract_path(${columnize_(
+          extraction.column || extraction[0],
+          this.builder,
+          this.client,
+          this.bindingsHolder
+        )}, ${this.client.toArrayPathFromJsonPath(
+          extraction.path || extraction[1],
+          this.builder,
+          this.bindingsHolder
+        )})`;
+        const alias = extraction.alias || extraction[2];
+        return alias
+          ? this.client.alias(jsonCol, this.formatter.wrap(alias))
+          : jsonCol;
+      })
+      .join(', ');
+  }
+
+  _onJsonPathEquals(nameJoinFunction, clause) {
+    return (
+      'json_extract_path(' +
+      wrap_(
+        clause.columnFirst,
+        undefined,
+        this.builder,
+        this.client,
+        this.bindingsHolder
+      ) +
+      ', ' +
+      this.client.toArrayPathFromJsonPath(
+        clause.jsonPathFirst,
+        this.builder,
+        this.bindingsHolder
+      ) +
+      ') = json_extract_path(' +
+      wrap_(
+        clause.columnSecond,
+        undefined,
+        this.builder,
+        this.client,
+        this.bindingsHolder
+      ) +
+      ', ' +
+      this.client.toArrayPathFromJsonPath(
+        clause.jsonPathSecond,
+        this.builder,
+        this.bindingsHolder
+      ) +
+      ')'
+    );
+  }
 }
 
 module.exports = QueryCompiler_CRDB;
diff --git a/lib/dialects/cockroachdb/index.js b/lib/dialects/cockroachdb/index.js
index 0b5ec6bf..a22411a0 100644
--- a/lib/dialects/cockroachdb/index.js
+++ b/lib/dialects/cockroachdb/index.js
@@ -58,6 +58,19 @@ class Client_CockroachDB extends Client_PostgreSQL {
       connectionToKill.activeQuery
     );
   }
+
+  toArrayPathFromJsonPath(jsonPath, builder, bindingsHolder) {
+    return jsonPath
+      .replace(/^(\$\.)/, '') // remove the first dollar
+      .replace(/\[([0-9]+)]/, '.$1')
+      .split('.')
+      .map(
+        function (v) {
+          return this.parameter(v, builder, bindingsHolder);
+        }.bind(this)
+      )
+      .join(', ');
+  }
 }
 
 Object.assign(Client_CockroachDB.prototype, {
diff --git a/lib/dialects/mssql/query/mssql-querycompiler.js b/lib/dialects/mssql/query/mssql-querycompiler.js
index 13a5774c..4dbe18de 100644
--- a/lib/dialects/mssql/query/mssql-querycompiler.js
+++ b/lib/dialects/mssql/query/mssql-querycompiler.js
@@ -6,6 +6,9 @@ const compact = require('lodash/compact');
 const identity = require('lodash/identity');
 const isEmpty = require('lodash/isEmpty');
 const Raw = require('../../../raw.js');
+const {
+  columnize: columnize_,
+} = require('../../../formatter/wrappingFormatter');
 
 const components = [
   'columns',
@@ -328,15 +331,17 @@ class QueryCompiler_MSSQL extends QueryCompiler {
           sql.push(this.aggregateRaw(stmt));
         } else if (stmt.type === 'analytic') {
           sql.push(this.analytic(stmt));
+        } else if (stmt.type === 'json') {
+          sql.push(this.json(stmt));
         } else if (stmt.value && stmt.value.length > 0) {
           sql.push(this.formatter.columnize(stmt.value));
         }
       }
     }
     if (sql.length === 0) sql = ['*'];
-
+    const select = this.onlyJson() ? '' : 'select ';
     return (
-      `select ${hints}${distinctClause}` +
+      `${select}${hints}${distinctClause}` +
       (top ? top + ' ' : '') +
       sql.join(', ') +
       (this.tableName ? ` from ${this.tableName}` : '')
@@ -515,6 +520,79 @@ class QueryCompiler_MSSQL extends QueryCompiler {
       'like '
     )}${this._valueClause(statement)}`;
   }
+
+  jsonExtract(params) {
+    // JSON_VALUE return NULL if we query object or array
+    // JSON_QUERY return NULL if we query literal/single value
+    return this._jsonExtract(
+      params.singleValue ? 'JSON_VALUE' : 'JSON_QUERY',
+      params
+    );
+  }
+
+  jsonSet(params) {
+    return this._jsonSet('JSON_MODIFY', params);
+  }
+
+  jsonInsert(params) {
+    return this._jsonSet('JSON_MODIFY', params);
+  }
+
+  jsonRemove(params) {
+    const jsonCol = `JSON_MODIFY(${columnize_(
+      params.column,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )},${this.client.parameter(
+      params.path,
+      this.builder,
+      this.bindingsHolder
+    )}, NULL)`;
+    return params.alias
+      ? this.client.alias(jsonCol, this.formatter.wrap(params.alias))
+      : jsonCol;
+  }
+
+  whereJsonPath(statement) {
+    return this._whereJsonPath('JSON_VALUE', statement);
+  }
+
+  whereJsonSupersetOf(statement) {
+    throw new Error(
+      'Json superset where clause not actually supported by MSSQL'
+    );
+  }
+
+  whereJsonSubsetOf(statement) {
+    throw new Error('Json subset where clause not actually supported by MSSQL');
+  }
+
+  _getExtracts(statement, operator) {
+    const column = columnize_(
+      statement.column,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    );
+    return (
+      Array.isArray(statement.values) ? statement.values : [statement.values]
+    )
+      .map(function (value) {
+        return (
+          'JSON_VALUE(' +
+          column +
+          ',' +
+          this.client.parameter(value, this.builder, this.bindingsHolder) +
+          ')'
+        );
+      }, this)
+      .join(operator);
+  }
+
+  onJsonPathEquals(clause) {
+    return this._onJsonPathEquals('JSON_VALUE', clause);
+  }
 }
 
 // Set the QueryBuilder & QueryCompiler on the client object,
diff --git a/lib/dialects/mssql/schema/mssql-columncompiler.js b/lib/dialects/mssql/schema/mssql-columncompiler.js
index 568407c6..6ff74ace 100644
--- a/lib/dialects/mssql/schema/mssql-columncompiler.js
+++ b/lib/dialects/mssql/schema/mssql-columncompiler.js
@@ -126,19 +126,30 @@ class ColumnCompiler_MSSQL extends ColumnCompiler {
     });
     return '';
   }
+
+  increments(options = { primaryKey: true }) {
+    return (
+      'int identity(1,1) not null' +
+      (this.tableCompiler._canBeAddPrimaryKey(options) ? ' primary key' : '')
+    );
+  }
+
+  bigincrements(options = { primaryKey: true }) {
+    return (
+      'bigint identity(1,1) not null' +
+      (this.tableCompiler._canBeAddPrimaryKey(options) ? ' primary key' : '')
+    );
+  }
 }
 
-ColumnCompiler_MSSQL.prototype.increments = ({ primaryKey = true } = {}) =>
-  'int identity(1,1) not null' + (primaryKey ? ' primary key' : '');
-ColumnCompiler_MSSQL.prototype.bigincrements = ({ primaryKey = true } = {}) =>
-  'bigint identity(1,1) not null' + (primaryKey ? ' primary key' : '');
 ColumnCompiler_MSSQL.prototype.bigint = 'bigint';
 ColumnCompiler_MSSQL.prototype.mediumint = 'int';
 ColumnCompiler_MSSQL.prototype.smallint = 'smallint';
 ColumnCompiler_MSSQL.prototype.text = 'nvarchar(max)';
 ColumnCompiler_MSSQL.prototype.mediumtext = 'nvarchar(max)';
 ColumnCompiler_MSSQL.prototype.longtext = 'nvarchar(max)';
-ColumnCompiler_MSSQL.prototype.json = 'nvarchar(max)';
+ColumnCompiler_MSSQL.prototype.json = ColumnCompiler_MSSQL.prototype.jsonb =
+  'nvarchar(max)';
 
 // TODO: mssql supports check constraints as of SQL Server 2008
 // so make enu here more like postgres
diff --git a/lib/dialects/mysql/query/mysql-querycompiler.js b/lib/dialects/mysql/query/mysql-querycompiler.js
index 382f2f7c..dfcb086b 100644
--- a/lib/dialects/mysql/query/mysql-querycompiler.js
+++ b/lib/dialects/mysql/query/mysql-querycompiler.js
@@ -3,6 +3,10 @@
 const identity = require('lodash/identity');
 const QueryCompiler = require('../../../query/querycompiler');
 const { wrapAsIdentifier } = require('../../../formatter/formatterUtils');
+const {
+  columnize: columnize_,
+  wrap: wrap_,
+} = require('../../../formatter/wrappingFormatter');
 
 class QueryCompiler_MySQL extends QueryCompiler {
   constructor(client, builder, formatter) {
@@ -163,6 +167,78 @@ class QueryCompiler_MySQL extends QueryCompiler {
       'like '
     )}${this._valueClause(statement)}`;
   }
+
+  // Json functions
+  jsonExtract(params) {
+    return this._jsonExtract(['json_extract', 'json_unquote'], params);
+  }
+
+  jsonSet(params) {
+    return this._jsonSet('json_set', params);
+  }
+
+  jsonInsert(params) {
+    return this._jsonSet('json_insert', params);
+  }
+
+  jsonRemove(params) {
+    const jsonCol = `json_remove(${columnize_(
+      params.column,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )},${this.client.parameter(
+      params.path,
+      this.builder,
+      this.bindingsHolder
+    )})`;
+    return params.alias
+      ? this.client.alias(jsonCol, this.formatter.wrap(params.alias))
+      : jsonCol;
+  }
+
+  whereJsonObject(statement) {
+    return this._not(
+      statement,
+      `json_contains(${this._columnClause(statement)}, ${this._jsonValueClause(
+        statement
+      )})`
+    );
+  }
+
+  whereJsonPath(statement) {
+    return this._whereJsonPath('json_extract', statement);
+  }
+
+  whereJsonSupersetOf(statement) {
+    return this._not(
+      statement,
+      `json_contains(${wrap_(
+        statement.column,
+        undefined,
+        this.builder,
+        this.client,
+        this.bindingsHolder
+      )},${this._jsonValueClause(statement)})`
+    );
+  }
+
+  whereJsonSubsetOf(statement) {
+    return this._not(
+      statement,
+      `json_contains(${this._jsonValueClause(statement)},${wrap_(
+        statement.column,
+        undefined,
+        this.builder,
+        this.client,
+        this.bindingsHolder
+      )})`
+    );
+  }
+
+  onJsonPathEquals(clause) {
+    return this._onJsonPathEquals('json_extract', clause);
+  }
 }
 
 // Set the QueryBuilder & QueryCompiler on the client object,
diff --git a/lib/dialects/mysql/schema/mysql-columncompiler.js b/lib/dialects/mysql/schema/mysql-columncompiler.js
index c44af84f..840e5d01 100644
--- a/lib/dialects/mysql/schema/mysql-columncompiler.js
+++ b/lib/dialects/mysql/schema/mysql-columncompiler.js
@@ -152,13 +152,30 @@ class ColumnCompiler_MySQL extends ColumnCompiler {
   collate(collation) {
     return collation && `collate '${collation}'`;
   }
+
+  increments(options = { primaryKey: true }) {
+    return (
+      'int unsigned not null' +
+      // In MySQL autoincrement are always a primary key. If you already have a primary key, we
+      // initialize this column as classic int column then modify it later in table compiler
+      (this.tableCompiler._canBeAddPrimaryKey(options)
+        ? ' auto_increment primary key'
+        : '')
+    );
+  }
+
+  bigincrements(options = { primaryKey: true }) {
+    return (
+      'bigint unsigned not null' +
+      // In MySQL autoincrement are always a primary key. If you already have a primary key, we
+      // initialize this column as classic int column then modify it later in table compiler
+      (this.tableCompiler._canBeAddPrimaryKey(options)
+        ? ' auto_increment primary key'
+        : '')
+    );
+  }
 }
 
-ColumnCompiler_MySQL.prototype.increments = ({ primaryKey = true } = {}) =>
-  'int unsigned not null auto_increment' + (primaryKey ? ' primary key' : '');
-ColumnCompiler_MySQL.prototype.bigincrements = ({ primaryKey = true } = {}) =>
-  'bigint unsigned not null auto_increment' +
-  (primaryKey ? ' primary key' : '');
 ColumnCompiler_MySQL.prototype.bigint = 'bigint';
 ColumnCompiler_MySQL.prototype.mediumint = 'mediumint';
 ColumnCompiler_MySQL.prototype.smallint = 'smallint';
diff --git a/lib/dialects/mysql/schema/mysql-tablecompiler.js b/lib/dialects/mysql/schema/mysql-tablecompiler.js
index d3b512b3..eab7fc0c 100644
--- a/lib/dialects/mysql/schema/mysql-tablecompiler.js
+++ b/lib/dialects/mysql/schema/mysql-tablecompiler.js
@@ -253,11 +253,31 @@ class TableCompiler_MySQL extends TableCompiler {
     constraintName = constraintName
       ? this.formatter.wrap(constraintName)
       : this.formatter.wrap(`${this.tableNameRaw}_pkey`);
+
+    const primaryCols = columns;
+    let incrementsCols = [];
+    if (this.grouped.columns) {
+      incrementsCols = this._getIncrementsColumnNames();
+      if (incrementsCols) {
+        incrementsCols.forEach((c) => {
+          if (!primaryCols.includes(c)) {
+            primaryCols.unshift(c);
+          }
+        });
+      }
+    }
     this.pushQuery(
       `alter table ${this.tableName()} add primary key ${constraintName}(${this.formatter.columnize(
-        columns
+        primaryCols
       )})`
     );
+    if (incrementsCols.length) {
+      this.pushQuery(
+        `alter table ${this.tableName()} modify column ${this.formatter.columnize(
+          incrementsCols
+        )} int unsigned not null auto_increment`
+      );
+    }
   }
 
   unique(columns, indexName) {
diff --git a/lib/dialects/oracle/schema/internal/trigger.js b/lib/dialects/oracle/schema/internal/trigger.js
index 859862a6..d84ff933 100644
--- a/lib/dialects/oracle/schema/internal/trigger.js
+++ b/lib/dialects/oracle/schema/internal/trigger.js
@@ -64,7 +64,7 @@ const trigger = {
       `PK_NAME VARCHAR(200); ` +
       `BEGIN` +
       `  EXECUTE IMMEDIATE ('CREATE SEQUENCE ${schemaQuoted}${sequenceNameQuoted}');` +
-      `  SELECT cols.column_name INTO PK_NAME` +
+      `  SELECT cols.column_name INTO PK_NAME` + // TODO : support autoincrement on table with multiple primary keys
       `  FROM all_constraints cons, all_cons_columns cols` +
       `  WHERE cons.constraint_type = 'P'` +
       `  AND cons.constraint_name = cols.constraint_name` +
diff --git a/lib/dialects/oracle/schema/oracle-columncompiler.js b/lib/dialects/oracle/schema/oracle-columncompiler.js
index 8482c12f..589301b4 100644
--- a/lib/dialects/oracle/schema/oracle-columncompiler.js
+++ b/lib/dialects/oracle/schema/oracle-columncompiler.js
@@ -15,14 +15,20 @@ class ColumnCompiler_Oracle extends ColumnCompiler {
     this.modifiers = ['defaultTo', 'checkIn', 'nullable', 'comment'];
   }
 
-  increments({ primaryKey = true } = {}) {
+  increments(options = { primaryKey: true }) {
     createAutoIncrementTriggerAndSequence(this);
-    return 'integer not null' + (primaryKey ? ' primary key' : '');
+    return (
+      'integer not null' +
+      (this.tableCompiler._canBeAddPrimaryKey(options) ? ' primary key' : '')
+    );
   }
 
-  bigincrements({ primaryKey = true } = {}) {
+  bigincrements(options = { primaryKey: true }) {
     createAutoIncrementTriggerAndSequence(this);
-    return 'number(20, 0) not null' + (primaryKey ? ' primary key' : '');
+    return (
+      'number(20, 0) not null' +
+      (this.tableCompiler._canBeAddPrimaryKey(options) ? ' primary key' : '')
+    );
   }
 
   floating(precision) {
diff --git a/lib/dialects/oracle/schema/oracle-tablecompiler.js b/lib/dialects/oracle/schema/oracle-tablecompiler.js
index 2dbe6944..8c953d4d 100644
--- a/lib/dialects/oracle/schema/oracle-tablecompiler.js
+++ b/lib/dialects/oracle/schema/oracle-tablecompiler.js
@@ -85,10 +85,6 @@ class TableCompiler_Oracle extends TableCompiler {
     );
   }
 
-  changeType() {
-    // alter table + table + ' modify ' + wrapped + '// type';
-  }
-
   _indexCommand(type, tableName, columns) {
     return this.formatter.wrap(
       utils.generateCombinedName(this.client.logger, type, tableName, columns)
@@ -104,9 +100,21 @@ class TableCompiler_Oracle extends TableCompiler {
     constraintName = constraintName
       ? this.formatter.wrap(constraintName)
       : this.formatter.wrap(`${this.tableNameRaw}_pkey`);
+    const primaryCols = columns;
+    let incrementsCols = [];
+    if (this.grouped.columns) {
+      incrementsCols = this._getIncrementsColumnNames();
+      if (incrementsCols) {
+        incrementsCols.forEach((c) => {
+          if (!primaryCols.includes(c)) {
+            primaryCols.unshift(c);
+          }
+        });
+      }
+    }
     this.pushQuery(
       `alter table ${this.tableName()} add constraint ${constraintName} primary key (${this.formatter.columnize(
-        columns
+        primaryCols
       )})${deferrable}`
     );
   }
diff --git a/lib/dialects/oracledb/query/oracledb-querycompiler.js b/lib/dialects/oracledb/query/oracledb-querycompiler.js
index 318baa0b..38af0d52 100644
--- a/lib/dialects/oracledb/query/oracledb-querycompiler.js
+++ b/lib/dialects/oracledb/query/oracledb-querycompiler.js
@@ -6,10 +6,11 @@ const Oracle_Compiler = require('../../oracle/query/oracle-querycompiler');
 const ReturningHelper = require('../utils').ReturningHelper;
 const BlobHelper = require('../utils').BlobHelper;
 const { isString } = require('../../../util/is');
-const Raw = require('../../../raw.js');
 const {
+  columnize: columnize_,
   direction: direction_,
 } = require('../../../formatter/wrappingFormatter');
+const Raw = require('../../../raw');
 
 class Oracledb_Compiler extends Oracle_Compiler {
   // Compiles an "insert" query, allowing for multiple
@@ -408,6 +409,88 @@ class Oracledb_Compiler extends Oracle_Compiler {
 
     return sql;
   }
+
+  _jsonPathWrap(extraction) {
+    return `'${extraction.path || extraction[1]}'`;
+  }
+
+  // Json functions
+  jsonExtract(params) {
+    return this._jsonExtract(
+      params.singleValue ? 'json_value' : 'json_query',
+      params
+    );
+  }
+
+  jsonSet(params) {
+    return `json_transform(${columnize_(
+      params.column,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )}, set ${this.client.parameter(
+      params.path,
+      this.builder,
+      this.bindingsHolder
+    )} = ${this.client.parameter(
+      params.value,
+      this.builder,
+      this.bindingsHolder
+    )})`;
+  }
+
+  jsonInsert(params) {
+    return `json_transform(${columnize_(
+      params.column,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )}, insert ${this.client.parameter(
+      params.path,
+      this.builder,
+      this.bindingsHolder
+    )} = ${this.client.parameter(
+      params.value,
+      this.builder,
+      this.bindingsHolder
+    )})`;
+  }
+
+  jsonRemove(params) {
+    const jsonCol = `json_transform(${columnize_(
+      params.column,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )}, remove ${this.client.parameter(
+      params.path,
+      this.builder,
+      this.bindingsHolder
+    )})`;
+    return params.alias
+      ? this.client.alias(jsonCol, this.formatter.wrap(params.alias))
+      : jsonCol;
+  }
+
+  whereJsonPath(statement) {
+    return this._whereJsonPath('json_value', statement);
+  }
+
+  whereJsonSupersetOf(statement) {
+    throw new Error(
+      'Json superset where clause not actually supported by Oracle'
+    );
+  }
+
+  whereJsonSubsetOf(statement) {
+    throw new Error(
+      'Json subset where clause not actually supported by Oracle'
+    );
+  }
+
+  onJsonPathEquals(clause) {
+    return this._onJsonPathEquals('json_value', clause);
+  }
 }
 
 module.exports = Oracledb_Compiler;
diff --git a/lib/dialects/oracledb/schema/oracledb-columncompiler.js b/lib/dialects/oracledb/schema/oracledb-columncompiler.js
index 07f1925e..136bf729 100644
--- a/lib/dialects/oracledb/schema/oracledb-columncompiler.js
+++ b/lib/dialects/oracledb/schema/oracledb-columncompiler.js
@@ -25,6 +25,16 @@ class ColumnCompiler_Oracledb extends ColumnCompiler_Oracle {
     }
     return useTz ? 'timestamp with local time zone' : 'timestamp';
   }
+
+  json() {
+    return `varchar2(4000) check (${this.formatter.columnize(
+      this.getColumnName()
+    )} is json)`;
+  }
+
+  jsonb() {
+    return this.json();
+  }
 }
 
 ColumnCompiler_Oracledb.prototype.time = 'timestamp with local time zone';
diff --git a/lib/dialects/postgres/index.js b/lib/dialects/postgres/index.js
index 5215e91d..6834bdaa 100644
--- a/lib/dialects/postgres/index.js
+++ b/lib/dialects/postgres/index.js
@@ -279,6 +279,21 @@ class Client_PG extends Client {
       options: {},
     });
   }
+
+  toPathForJson(jsonPath) {
+    const PG_PATH_REGEX = /^{.*}$/;
+    if (jsonPath.match(PG_PATH_REGEX)) {
+      return jsonPath;
+    }
+    return (
+      '{' +
+      jsonPath
+        .replace(/^(\$\.)/, '') // remove the first dollar
+        .replace('.', ',')
+        .replace(/\[([0-9]+)]/, ',$1') + // transform [number] to ,number
+      '}'
+    );
+  }
 }
 
 Object.assign(Client_PG.prototype, {
diff --git a/lib/dialects/postgres/query/pg-querycompiler.js b/lib/dialects/postgres/query/pg-querycompiler.js
index 9d2534ae..3e1dac3b 100644
--- a/lib/dialects/postgres/query/pg-querycompiler.js
+++ b/lib/dialects/postgres/query/pg-querycompiler.js
@@ -6,6 +6,8 @@ const reduce = require('lodash/reduce');
 const QueryCompiler = require('../../../query/querycompiler');
 const {
   wrapString,
+  columnize: columnize_,
+  operator: operator_,
   wrap: wrap_,
 } = require('../../../formatter/wrappingFormatter');
 
@@ -295,6 +297,97 @@ class QueryCompiler_PG extends QueryCompiler {
   distinctOn(value) {
     return 'distinct on (' + this.formatter.columnize(value) + ') ';
   }
+
+  // Json functions
+  jsonExtract(params) {
+    return this._jsonExtract('jsonb_path_query', params);
+  }
+
+  jsonSet(params) {
+    return this._jsonSet(
+      'jsonb_set',
+      Object.assign({}, params, {
+        path: this.client.toPathForJson(params.path),
+      })
+    );
+  }
+
+  jsonInsert(params) {
+    return this._jsonSet(
+      'jsonb_insert',
+      Object.assign({}, params, {
+        path: this.client.toPathForJson(params.path),
+      })
+    );
+  }
+
+  jsonRemove(params) {
+    const jsonCol = `${columnize_(
+      params.column,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )} #- ${this.client.parameter(
+      this.client.toPathForJson(params.path),
+      this.builder,
+      this.bindingsHolder
+    )}`;
+    return params.alias
+      ? this.client.alias(jsonCol, this.formatter.wrap(params.alias))
+      : jsonCol;
+  }
+
+  whereJsonPath(statement) {
+    let castValue = '';
+    if (parseInt(statement.value)) {
+      castValue = '::int';
+    } else if (parseFloat(statement.value)) {
+      castValue = '::float';
+    } else {
+      castValue = " #>> '{}'";
+    }
+    return `jsonb_path_query_first(${this._columnClause(
+      statement
+    )}, ${this.client.parameter(
+      statement.jsonPath,
+      this.builder,
+      this.bindingsHolder
+    )})${castValue} ${operator_(
+      statement.operator,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )} ${this._jsonValueClause(statement)}`;
+  }
+
+  whereJsonSupersetOf(statement) {
+    return this._not(
+      statement,
+      `${wrap_(
+        statement.column,
+        undefined,
+        this.builder,
+        this.client,
+        this.bindingsHolder
+      )} @> ${this._jsonValueClause(statement)}`
+    );
+  }
+
+  whereJsonSubsetOf(statement) {
+    return this._not(
+      statement,
+      `${columnize_(
+        statement.column,
+        this.builder,
+        this.client,
+        this.bindingsHolder
+      )} <@ ${this._jsonValueClause(statement)}`
+    );
+  }
+
+  onJsonPathEquals(clause) {
+    return this._onJsonPathEquals('jsonb_path_query_first', clause);
+  }
 }
 
 module.exports = QueryCompiler_PG;
diff --git a/lib/dialects/postgres/schema/pg-columncompiler.js b/lib/dialects/postgres/schema/pg-columncompiler.js
index 5fb7fa25..c3547271 100644
--- a/lib/dialects/postgres/schema/pg-columncompiler.js
+++ b/lib/dialects/postgres/schema/pg-columncompiler.js
@@ -100,12 +100,22 @@ class ColumnCompiler_PG extends ColumnCompiler {
       );
     }, comment);
   }
+
+  increments(options = { primaryKey: true }) {
+    return (
+      'serial' +
+      (this.tableCompiler._canBeAddPrimaryKey(options) ? ' primary key' : '')
+    );
+  }
+
+  bigincrements(options = { primaryKey: true }) {
+    return (
+      'bigserial' +
+      (this.tableCompiler._canBeAddPrimaryKey(options) ? ' primary key' : '')
+    );
+  }
 }
 
-ColumnCompiler_PG.prototype.bigincrements = ({ primaryKey = true } = {}) =>
-  'bigserial' + (primaryKey ? ' primary key' : '');
-ColumnCompiler_PG.prototype.increments = ({ primaryKey = true } = {}) =>
-  'serial' + (primaryKey ? ' primary key' : '');
 ColumnCompiler_PG.prototype.bigint = 'bigint';
 ColumnCompiler_PG.prototype.binary = 'bytea';
 ColumnCompiler_PG.prototype.bool = 'boolean';
diff --git a/lib/dialects/redshift/index.js b/lib/dialects/redshift/index.js
index ba98d285..7b5c41b7 100644
--- a/lib/dialects/redshift/index.js
+++ b/lib/dialects/redshift/index.js
@@ -63,6 +63,18 @@ class Client_Redshift extends Client_PG {
     }
     return resp;
   }
+
+  toPathForJson(jsonPath, builder, bindingsHolder) {
+    return jsonPath
+      .replace(/^(\$\.)/, '') // remove the first dollar
+      .split('.')
+      .map(
+        function (v) {
+          return this.parameter(v, builder, bindingsHolder);
+        }.bind(this)
+      )
+      .join(', ');
+  }
 }
 
 Object.assign(Client_Redshift.prototype, {
diff --git a/lib/dialects/redshift/query/redshift-querycompiler.js b/lib/dialects/redshift/query/redshift-querycompiler.js
index 2b8db74f..6687b80c 100644
--- a/lib/dialects/redshift/query/redshift-querycompiler.js
+++ b/lib/dialects/redshift/query/redshift-querycompiler.js
@@ -4,6 +4,9 @@ const QueryCompiler = require('../../../query/querycompiler');
 const QueryCompiler_PG = require('../../postgres/query/pg-querycompiler');
 
 const identity = require('lodash/identity');
+const {
+  columnize: columnize_,
+} = require('../../../formatter/wrappingFormatter');
 
 class QueryCompiler_Redshift extends QueryCompiler_PG {
   truncate() {
@@ -95,6 +98,66 @@ class QueryCompiler_Redshift extends QueryCompiler_PG {
 
     return this._buildColumnInfoQuery(schema, sql, bindings, column);
   }
+
+  jsonExtract(params) {
+    let extractions;
+    if (Array.isArray(params.column)) {
+      extractions = params.column;
+    } else {
+      extractions = [params];
+    }
+    return extractions
+      .map((extraction) => {
+        const jsonCol = `json_extract_path_text(${columnize_(
+          extraction.column || extraction[0],
+          this.builder,
+          this.client,
+          this.bindingsHolder
+        )}, ${this.client.toPathForJson(
+          params.path || extraction[1],
+          this.builder,
+          this.bindingsHolder
+        )})`;
+        const alias = extraction.alias || extraction[2];
+        return alias
+          ? this.client.alias(jsonCol, this.formatter.wrap(alias))
+          : jsonCol;
+      })
+      .join(', ');
+  }
+
+  jsonSet(params) {
+    throw new Error('Json set is not supported by Redshift');
+  }
+
+  jsonInsert(params) {
+    throw new Error('Json insert is not supported by Redshift');
+  }
+
+  jsonRemove(params) {
+    throw new Error('Json remove is not supported by Redshift');
+  }
+
+  whereJsonPath(statement) {
+    return this._whereJsonPath(
+      'json_extract_path_text',
+      Object.assign({}, statement, {
+        path: this.client.toPathForJson(statement.path),
+      })
+    );
+  }
+
+  whereJsonSupersetOf(statement) {
+    throw new Error('Json superset is not supported by Redshift');
+  }
+
+  whereJsonSubsetOf(statement) {
+    throw new Error('Json subset is not supported by Redshift');
+  }
+
+  onJsonPathEquals(clause) {
+    return this._onJsonPathEquals('json_extract_path_text', clause);
+  }
 }
 
 module.exports = QueryCompiler_Redshift;
diff --git a/lib/dialects/sqlite3/query/sqlite-querycompiler.js b/lib/dialects/sqlite3/query/sqlite-querycompiler.js
index 44d3931b..97e155ca 100644
--- a/lib/dialects/sqlite3/query/sqlite-querycompiler.js
+++ b/lib/dialects/sqlite3/query/sqlite-querycompiler.js
@@ -9,7 +9,10 @@ const reduce = require('lodash/reduce');
 const QueryCompiler = require('../../../query/querycompiler');
 const noop = require('../../../util/noop');
 const { isString } = require('../../../util/is');
-const { wrapString } = require('../../../formatter/wrappingFormatter');
+const {
+  wrapString,
+  columnize: columnize_,
+} = require('../../../formatter/wrappingFormatter');
 
 const emptyStr = constant('');
 
@@ -244,6 +247,55 @@ class QueryCompiler_SQLite3 extends QueryCompiler {
     this.single.limit = noLimit ? -1 : this.single.limit;
     return `limit ${this._getValueOrParameterFromAttribute('limit')}`;
   }
+
+  // Json functions
+  jsonExtract(params) {
+    return this._jsonExtract('json_extract', params);
+  }
+
+  jsonSet(params) {
+    return this._jsonSet('json_set', params);
+  }
+
+  jsonInsert(params) {
+    return this._jsonSet('json_insert', params);
+  }
+
+  jsonRemove(params) {
+    const jsonCol = `json_remove(${columnize_(
+      params.column,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )},${this.client.parameter(
+      params.path,
+      this.builder,
+      this.bindingsHolder
+    )})`;
+    return params.alias
+      ? this.client.alias(jsonCol, this.formatter.wrap(params.alias))
+      : jsonCol;
+  }
+
+  whereJsonPath(statement) {
+    return this._whereJsonPath('json_extract', statement);
+  }
+
+  whereJsonSupersetOf(statement) {
+    throw new Error(
+      'Json superset where clause not actually supported by SQLite'
+    );
+  }
+
+  whereJsonSubsetOf(statement) {
+    throw new Error(
+      'Json subset where clause not actually supported by SQLite'
+    );
+  }
+
+  onJsonPathEquals(clause) {
+    return this._onJsonPathEquals('json_extract', clause);
+  }
 }
 
 module.exports = QueryCompiler_SQLite3;
diff --git a/lib/dialects/sqlite3/schema/sqlite-columncompiler.js b/lib/dialects/sqlite3/schema/sqlite-columncompiler.js
index 787830ba..ede68dba 100644
--- a/lib/dialects/sqlite3/schema/sqlite-columncompiler.js
+++ b/lib/dialects/sqlite3/schema/sqlite-columncompiler.js
@@ -26,5 +26,9 @@ ColumnCompiler_SQLite3.prototype.double =
   ColumnCompiler_SQLite3.prototype.floating =
     'float';
 ColumnCompiler_SQLite3.prototype.timestamp = 'datetime';
+// autoincrement without primary key is a syntax error in SQLite, so it's necessary
+ColumnCompiler_SQLite3.prototype.increments =
+  ColumnCompiler_SQLite3.prototype.bigincrements =
+    'integer not null primary key autoincrement';
 
 module.exports = ColumnCompiler_SQLite3;
diff --git a/lib/dialects/sqlite3/schema/sqlite-tablecompiler.js b/lib/dialects/sqlite3/schema/sqlite-tablecompiler.js
index 2c8271f3..2bc5a06a 100644
--- a/lib/dialects/sqlite3/schema/sqlite-tablecompiler.js
+++ b/lib/dialects/sqlite3/schema/sqlite-tablecompiler.js
@@ -261,9 +261,14 @@ class TableCompiler_SQLite3 extends TableCompiler {
       if (constraintName) {
         constraintName = ' constraint ' + this.formatter.wrap(constraintName);
       }
-      return `,${constraintName} primary key (${this.formatter.columnize(
-        columns
-      )})`;
+      const needUniqueCols =
+        this.grouped.columns.filter((t) => t.builder._type === 'increments')
+          .length > 0;
+      // SQLite dont support autoincrement columns and composite primary keys (autoincrement is always primary key).
+      // You need to add unique index instead when you have autoincrement columns (https://stackoverflow.com/a/6154876/1535159)
+      return `,${constraintName} ${
+        needUniqueCols ? 'unique' : 'primary key'
+      } (${this.formatter.columnize(columns)})`;
     }
   }
 
diff --git a/lib/migrations/migrate/stub/knexfile-ts.stub b/lib/migrations/migrate/stub/knexfile-ts.stub
index 42d283f9..76618108 100644
--- a/lib/migrations/migrate/stub/knexfile-ts.stub
+++ b/lib/migrations/migrate/stub/knexfile-ts.stub
@@ -1,7 +1,8 @@
-// Update with your config settings.
+import type { Knex } from "knex";
 
-module.exports = {
+// Update with your config settings.
 
+const config: { [key: string]: Knex.Config } = {
   development: {
     client: "sqlite3",
     connection: {
@@ -42,3 +43,5 @@ module.exports = {
   }
 
 };
+
+module.exports = config;
diff --git a/lib/query/joinclause.js b/lib/query/joinclause.js
index 83aee892..c696aa11 100644
--- a/lib/query/joinclause.js
+++ b/lib/query/joinclause.js
@@ -70,16 +70,33 @@ class JoinClause {
     return this;
   }
 
-  // Adds a "using" clause to the current join.
-  using(column) {
-    return this.clauses.push({ type: 'onUsing', column, bool: this._bool() });
-  }
-
   // Adds an "or on" clause to the current join object.
   orOn(first, operator, second) {
     return this._bool('or').on.apply(this, arguments);
   }
 
+  onJsonPathEquals(columnFirst, jsonPathFirst, columnSecond, jsonPathSecond) {
+    this.clauses.push({
+      type: 'onJsonPathEquals',
+      columnFirst: columnFirst,
+      jsonPathFirst: jsonPathFirst,
+      columnSecond: columnSecond,
+      jsonPathSecond: jsonPathSecond,
+      bool: this._bool(),
+      not: this._not(),
+    });
+    return this;
+  }
+
+  orOnJsonPathEquals(columnFirst, jsonPathFirst, columnSecond, jsonPathSecond) {
+    return this._bool('or').onJsonPathEquals.apply(this, arguments);
+  }
+
+  // Adds a "using" clause to the current join.
+  using(column) {
+    return this.clauses.push({ type: 'onUsing', column, bool: this._bool() });
+  }
+
   onVal(first) {
     if (typeof first === 'object' && typeof first.toSQL !== 'function') {
       const keys = Object.keys(first);
@@ -247,5 +264,7 @@ JoinClause.prototype.andOnExists = JoinClause.prototype.onExists;
 JoinClause.prototype.andOnNotExists = JoinClause.prototype.onNotExists;
 JoinClause.prototype.andOnBetween = JoinClause.prototype.onBetween;
 JoinClause.prototype.andOnNotBetween = JoinClause.prototype.onNotBetween;
+JoinClause.prototype.andOnJsonPathEquals =
+  JoinClause.prototype.onJsonPathEquals;
 
 module.exports = JoinClause;
diff --git a/lib/query/method-constants.js b/lib/query/method-constants.js
index babab89e..e8896e85 100644
--- a/lib/query/method-constants.js
+++ b/lib/query/method-constants.js
@@ -92,4 +92,38 @@ module.exports = [
   'truncate',
   'transacting',
   'connection',
+
+  // JSON methods
+
+  // Json manipulation functions
+  'jsonExtract',
+  'jsonSet',
+  'jsonInsert',
+  'jsonRemove',
+
+  // Wheres Json
+  'whereJsonObject',
+  'orWhereJsonObject',
+  'andWhereJsonObject',
+  'whereNotJsonObject',
+  'orWhereNotJsonObject',
+  'andWhereNotJsonObject',
+
+  'whereJsonPath',
+  'orWhereJsonPath',
+  'andWhereJsonPath',
+
+  'whereJsonSupersetOf',
+  'orWhereJsonSupersetOf',
+  'andWhereJsonSupersetOf',
+  'whereJsonNotSupersetOf',
+  'orWhereJsonNotSupersetOf',
+  'andWhereJsonNotSupersetOf',
+
+  'whereJsonSubsetOf',
+  'orWhereJsonSubsetOf',
+  'andWhereJsonSubsetOf',
+  'whereJsonNotSubsetOf',
+  'orWhereJsonNotSubsetOf',
+  'andWhereJsonNotSubsetOf',
 ];
diff --git a/lib/query/querybuilder.js b/lib/query/querybuilder.js
index 60c224b9..ef9f118b 100644
--- a/lib/query/querybuilder.js
+++ b/lib/query/querybuilder.js
@@ -246,7 +246,6 @@ class Builder extends EventEmitter {
 
   // Adds a join clause to the query, allowing for advanced joins
   // with an anonymous function as the second argument.
-  // function(table, first, operator, second)
   join(table, first, ...args) {
     let join;
     const schema =
@@ -1329,6 +1328,179 @@ class Builder extends EventEmitter {
     );
   }
 
+  // JSON support functions
+  _json(nameFunction, params) {
+    this._statements.push({
+      grouping: 'columns',
+      type: 'json',
+      method: nameFunction,
+      params: params,
+    });
+    return this;
+  }
+
+  jsonExtract() {
+    const column = arguments[0];
+    let path;
+    let alias;
+    let singleValue = true;
+
+    // We use arguments to have the signatures :
+    // - column (string or array)
+    // - column + path
+    // - column + path + alias
+    // - column + path + alias + singleValue
+    // - column array + singleValue
+    if (arguments.length >= 2) {
+      path = arguments[1];
+    }
+    if (arguments.length >= 3) {
+      alias = arguments[2];
+    }
+    if (arguments.length === 4) {
+      singleValue = arguments[3];
+    }
+    if (
+      arguments.length === 2 &&
+      Array.isArray(arguments[0]) &&
+      isBoolean(arguments[1])
+    ) {
+      singleValue = arguments[1];
+    }
+    return this._json('jsonExtract', {
+      column: column,
+      path: path,
+      alias: alias,
+      singleValue, // boolean used only in MSSQL to use function for extract value instead of object/array.
+    });
+  }
+
+  jsonSet(column, path, value, alias) {
+    return this._json('jsonSet', {
+      column: column,
+      path: path,
+      value: value,
+      alias: alias,
+    });
+  }
+
+  jsonInsert(column, path, value, alias) {
+    return this._json('jsonInsert', {
+      column: column,
+      path: path,
+      value: value,
+      alias: alias,
+    });
+  }
+
+  jsonRemove(column, path, alias) {
+    return this._json('jsonRemove', {
+      column: column,
+      path: path,
+      alias: alias,
+    });
+  }
+
+  // Wheres for JSON
+  _isJsonObject(jsonValue) {
+    return isObject(jsonValue) && !(jsonValue instanceof Builder);
+  }
+
+  _whereJsonWrappedValue(type, column, value) {
+    const whereJsonClause = {
+      grouping: 'where',
+      type: type,
+      column,
+      value: value,
+      not: this._not(),
+      bool: this._bool(),
+      asColumn: this._asColumnFlag,
+    };
+    if (arguments[3]) {
+      whereJsonClause.operator = arguments[3];
+    }
+    if (arguments[4]) {
+      whereJsonClause.jsonPath = arguments[4];
+    }
+    this._statements.push(whereJsonClause);
+  }
+
+  whereJsonObject(column, value) {
+    this._whereJsonWrappedValue('whereJsonObject', column, value);
+    return this;
+  }
+
+  orWhereJsonObject(column, operator, value) {
+    return this._bool('or').whereJsonObject(column, operator, value);
+  }
+
+  whereNotJsonObject(column, value) {
+    this._not(true)._whereJsonWrappedValue('whereJsonObject', column, value);
+    return this;
+  }
+
+  orWhereNotJsonObject(column, operator, value) {
+    return this._not(true)._bool('or').whereJsonObject(column, operator, value);
+  }
+
+  whereJsonPath(column, path, operator, value) {
+    this._whereJsonWrappedValue('whereJsonPath', column, value, operator, path);
+    return this;
+  }
+
+  orWhereJsonPath(column, operator, value) {
+    return this._bool('or').whereJsonPath(column, operator, value);
+  }
+
+  // Json superset wheres
+  whereJsonSupersetOf(column, value) {
+    this._whereJsonWrappedValue('whereJsonSupersetOf', column, value);
+    return this;
+  }
+
+  whereJsonNotSupersetOf(column, value) {
+    this._not(true).whereJsonSupersetOf(column, value);
+    return this;
+  }
+
+  orWhereJsonSupersetOf(column, value) {
+    this._whereJsonWrappedValue('whereJsonSupersetOf', column, value);
+    return this;
+  }
+
+  orWhereJsonNotSupersetOf(column, value) {
+    this._not(true)._bool('or').whereJsonSupersetOf(column, value);
+    return this;
+  }
+
+  // Json subset wheres
+  whereJsonSubsetOf(column, value) {
+    this._whereJsonWrappedValue('whereJsonSubsetOf', column, value);
+    return this;
+  }
+
+  whereJsonNotSubsetOf(column, value) {
+    this._not(true).whereJsonSubsetOf(column, value);
+    return this;
+  }
+
+  orWhereJsonSubsetOf(column, value) {
+    this._whereJsonWrappedValue('whereJsonSubsetOf', column, value);
+    return this;
+  }
+
+  orWhereJsonNotSubsetOf(column, value) {
+    this._not(true)._bool('or').whereJsonSubsetOf(column, value);
+    return this;
+  }
+
+  whereJsonHasNone(column, values) {
+    this._not(true).whereJsonHasAll(column, values);
+    return this;
+  }
+
+  // end of wheres for JSON
+
   _analytic(alias, second, third) {
     let analytic;
     const { schema } = this._single;
@@ -1536,6 +1708,9 @@ Builder.prototype.andWhereColumn = Builder.prototype.whereColumn;
 Builder.prototype.andWhereRaw = Builder.prototype.whereRaw;
 Builder.prototype.andWhereBetween = Builder.prototype.whereBetween;
 Builder.prototype.andWhereNotBetween = Builder.prototype.whereNotBetween;
+Builder.prototype.andWhereJsonObject = Builder.prototype.whereJsonObject;
+Builder.prototype.andWhereNotJsonObject = Builder.prototype.whereJsonObject;
+Builder.prototype.andWhereJsonPath = Builder.prototype.whereJsonPath;
 Builder.prototype.andHaving = Builder.prototype.having;
 Builder.prototype.andHavingIn = Builder.prototype.havingIn;
 Builder.prototype.andHavingNotIn = Builder.prototype.havingNotIn;
diff --git a/lib/query/querycompiler.js b/lib/query/querycompiler.js
index 3f3d74b1..59c7e784 100644
--- a/lib/query/querycompiler.js
+++ b/lib/query/querycompiler.js
@@ -240,6 +240,8 @@ class QueryCompiler {
           sql.push(this.aggregateRaw(stmt));
         } else if (stmt.type === 'analytic') {
           sql.push(this.analytic(stmt));
+        } else if (stmt.type === 'json') {
+          sql.push(this.json(stmt));
         } else if (stmt.value && stmt.value.length > 0) {
           sql.push(
             columnize_(
@@ -253,8 +255,9 @@ class QueryCompiler {
       }
     }
     if (sql.length === 0) sql = ['*'];
+    const select = this.onlyJson() ? '' : 'select ';
     return (
-      `select ${hints}${distinctClause}` +
+      `${select}${hints}${distinctClause}` +
       sql.join(', ') +
       (this.tableName
         ? ` from ${this.single.only ? 'only ' : ''}${this.tableName}`
@@ -728,6 +731,15 @@ class QueryCompiler {
     );
   }
 
+  onlyJson() {
+    return (
+      !this.tableName &&
+      this.grouped.columns &&
+      this.grouped.columns.length === 1 &&
+      this.grouped.columns[0].type === 'json'
+    );
+  }
+
   limit() {
     const noLimit = !this.single.limit && this.single.limit !== 0;
     if (noLimit) return '';
@@ -1057,11 +1069,33 @@ class QueryCompiler {
     );
   }
 
+  _jsonWrapValue(jsonValue) {
+    if (this.builder._isJsonObject(jsonValue)) {
+      return JSON.stringify(jsonValue);
+    }
+    return jsonValue;
+  }
+
+  _jsonValueClause(statement) {
+    statement.value = this._jsonWrapValue(statement.value);
+    return this._valueClause(statement);
+  }
+
+  whereJsonObject(statement) {
+    return `${this._columnClause(statement)} ${
+      statement.not ? '!=' : '='
+    } ${this._jsonValueClause(statement)}`;
+  }
+
   wrap(str) {
     if (str.charAt(0) !== '(') return `(${str})`;
     return str;
   }
 
+  json(stmt) {
+    return this[stmt.method](stmt.params);
+  }
+
   analytic(stmt) {
     let sql = '';
     const self = this;
@@ -1346,6 +1380,112 @@ class QueryCompiler {
     }
     return this._tableName;
   }
+
+  _jsonPathWrap(extraction) {
+    return this.client.parameter(
+      extraction.path || extraction[1],
+      this.builder,
+      this.bindingsHolder
+    );
+  }
+
+  // Json common functions
+  _jsonExtract(nameFunction, params) {
+    let extractions;
+    if (Array.isArray(params.column)) {
+      extractions = params.column;
+    } else {
+      extractions = [params];
+    }
+    if (!Array.isArray(nameFunction)) {
+      nameFunction = [nameFunction];
+    }
+    return extractions
+      .map((extraction) => {
+        let jsonCol = `${columnize_(
+          extraction.column || extraction[0],
+          this.builder,
+          this.client,
+          this.bindingsHolder
+        )}, ${this._jsonPathWrap(extraction)}`;
+        nameFunction.forEach((f) => {
+          jsonCol = f + '(' + jsonCol + ')';
+        });
+        const alias = extraction.alias || extraction[2];
+        return alias
+          ? this.client.alias(jsonCol, this.formatter.wrap(alias))
+          : jsonCol;
+      })
+      .join(', ');
+  }
+
+  _jsonSet(nameFunction, params) {
+    const jsonSet = `${nameFunction}(${columnize_(
+      params.column,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )}, ${this.client.parameter(
+      params.path,
+      this.builder,
+      this.bindingsHolder
+    )}, ${this.client.parameter(
+      params.value,
+      this.builder,
+      this.bindingsHolder
+    )})`;
+    return params.alias
+      ? this.client.alias(jsonSet, this.formatter.wrap(params.alias))
+      : jsonSet;
+  }
+
+  _whereJsonPath(nameFunction, statement) {
+    return `${nameFunction}(${this._columnClause(
+      statement
+    )}, ${this._jsonPathWrap({ path: statement.jsonPath })}) ${operator_(
+      statement.operator,
+      this.builder,
+      this.client,
+      this.bindingsHolder
+    )} ${this._jsonValueClause(statement)}`;
+  }
+
+  _onJsonPathEquals(nameJoinFunction, clause) {
+    return (
+      nameJoinFunction +
+      '(' +
+      wrap_(
+        clause.columnFirst,
+        undefined,
+        this.builder,
+        this.client,
+        this.bindingsHolder
+      ) +
+      ', ' +
+      this.client.parameter(
+        clause.jsonPathFirst,
+        this.builder,
+        this.bindingsHolder
+      ) +
+      ') = ' +
+      nameJoinFunction +
+      '(' +
+      wrap_(
+        clause.columnSecond,
+        undefined,
+        this.builder,
+        this.client,
+        this.bindingsHolder
+      ) +
+      ', ' +
+      this.client.parameter(
+        clause.jsonPathSecond,
+        this.builder,
+        this.bindingsHolder
+      ) +
+      ')'
+    );
+  }
 }
 
 module.exports = QueryCompiler;
diff --git a/lib/schema/columncompiler.js b/lib/schema/columncompiler.js
index 56a35adf..b949b423 100644
--- a/lib/schema/columncompiler.js
+++ b/lib/schema/columncompiler.js
@@ -134,6 +134,18 @@ class ColumnCompiler {
   defaultTo(value) {
     return `default ${formatDefault(value, this.type, this.client)}`;
   }
+
+  increments(options = { primaryKey: true }) {
+    return (
+      'integer not null' +
+      (this.tableCompiler._canBeAddPrimaryKey(options) ? ' primary key' : '') +
+      ' autoincrement'
+    );
+  }
+
+  bigincrements(options = { primaryKey: true }) {
+    return this.increments(options);
+  }
 }
 
 ColumnCompiler.prototype.binary = 'blob';
@@ -149,10 +161,6 @@ ColumnCompiler.prototype.enu = 'varchar';
 ColumnCompiler.prototype.bit = ColumnCompiler.prototype.json = 'text';
 ColumnCompiler.prototype.uuid = ({ useBinaryUuid = false } = {}) =>
   useBinaryUuid ? 'binary(16)' : 'char(36)';
-ColumnCompiler.prototype.increments = ({ primaryKey = true } = {}) =>
-  'integer not null' + (primaryKey ? ' primary key' : '') + ' autoincrement';
-ColumnCompiler.prototype.bigincrements = ({ primaryKey = true } = {}) =>
-  'integer not null' + (primaryKey ? ' primary key' : '') + ' autoincrement';
 ColumnCompiler.prototype.integer =
   ColumnCompiler.prototype.smallint =
   ColumnCompiler.prototype.mediumint =
diff --git a/lib/schema/tablecompiler.js b/lib/schema/tablecompiler.js
index bd4dd733..49bb607e 100644
--- a/lib/schema/tablecompiler.js
+++ b/lib/schema/tablecompiler.js
@@ -361,6 +361,23 @@ class TableCompiler {
     ).toLowerCase();
     return this.formatter.wrap(indexName);
   }
+
+  _getPrimaryKeys() {
+    return (this.grouped.alterTable || [])
+      .filter((a) => a.method === 'primary')
+      .flatMap((a) => a.args)
+      .flat();
+  }
+
+  _canBeAddPrimaryKey(options) {
+    return options.primaryKey && this._getPrimaryKeys().length === 0;
+  }
+
+  _getIncrementsColumnNames() {
+    return this.grouped.columns
+      .filter((c) => c.builder._type === 'increments')
+      .map((c) => c.builder._args[0]);
+  }
 }
 
 TableCompiler.prototype.pushQuery = pushQuery;
diff --git a/types/index.d.ts b/types/index.d.ts
index 51d55663..89cd1a11 100644
--- a/types/index.d.ts
+++ b/types/index.d.ts
@@ -509,6 +509,12 @@ export declare namespace Knex {
     fullOuterJoin: Join<TRecord, TResult>;
     crossJoin: Join<TRecord, TResult>;
 
+    // Json manipulation
+    jsonExtract: JsonExtract<TRecord, TResult>;
+    jsonSet: JsonSet<TRecord, TResult>;
+    jsonInsert: JsonInsert<TRecord, TResult>;
+    jsonRemove: JsonRemove<TRecord, TResult>;
+
     // Using
     using: Using<TRecord, TResult>;
 
@@ -552,6 +558,31 @@ export declare namespace Knex {
     orWhereNotBetween: WhereBetween<TRecord, TResult>;
     andWhereNotBetween: WhereBetween<TRecord, TResult>;
 
+    whereJsonObject: WhereJsonObject<TRecord, TResult>;
+    orWhereJsonObject: WhereJsonObject<TRecord, TResult>;
+    andWhereJsonObject: WhereJsonObject<TRecord, TResult>;
+    whereNotJsonObject: WhereJsonObject<TRecord, TResult>;
+    orWhereNotJsonObject: WhereJsonObject<TRecord, TResult>;
+    andWhereNotJsonObject: WhereJsonObject<TRecord, TResult>;
+
+    whereJsonPath: WhereJsonPath<TRecord, TResult>;
+    orWhereJsonPath: WhereJsonPath<TRecord, TResult>;
+    andWhereJsonPath: WhereJsonPath<TRecord, TResult>;
+
+    whereJsonSupersetOf: WhereJsonObject<TRecord, TResult>;
+    orWhereJsonSupersetOf: WhereJsonObject<TRecord, TResult>;
+    andWhereJsonSupersetOf: WhereJsonObject<TRecord, TResult>;
+    whereJsonNotSupersetOf: WhereJsonObject<TRecord, TResult>;
+    orWhereJsonNotSupersetOf: WhereJsonObject<TRecord, TResult>;
+    andWhereJsonNotSupersetOf: WhereJsonObject<TRecord, TResult>;
+
+    whereJsonSubsetOf: WhereJsonObject<TRecord, TResult>;
+    orWhereJsonSubsetOf: WhereJsonObject<TRecord, TResult>;
+    andWhereJsonSubsetOf: WhereJsonObject<TRecord, TResult>;
+    whereJsonNotSubsetOf: WhereJsonObject<TRecord, TResult>;
+    orWhereJsonNotSubsetOf: WhereJsonObject<TRecord, TResult>;
+    andWhereJsonNotSubsetOf: WhereJsonObject<TRecord, TResult>;
+
     // Group by
     groupBy: GroupBy<TRecord, TResult>;
     groupByRaw: RawQueryBuilder<TRecord, TResult>;
@@ -1097,6 +1128,30 @@ export declare namespace Knex {
     ): QueryBuilder<TRecord, TResult2>;
   }
 
+  interface JsonExtraction {
+    column: string | Raw | QueryBuilder;
+    path: string;
+    alias?: string;
+    singleValue?: boolean;
+  }
+
+  interface JsonExtract<TRecord extends {} = any, TResult extends {} = any> {
+    (column: string | Raw | QueryBuilder, path: string, alias?: string, singleValue?: boolean): QueryBuilder<TRecord, TResult>;
+    (column: JsonExtraction[] | any[][], singleValue?: boolean): QueryBuilder<TRecord, TResult>;
+  }
+
+  interface JsonSet<TRecord extends {} = any, TResult extends {} = any> {
+    (column: string | Raw | QueryBuilder, path: string, value: any, alias?: string): QueryBuilder<TRecord, TResult>;
+  }
+
+  interface JsonInsert<TRecord extends {} = any, TResult extends {} = any> {
+    (column: string | Raw | QueryBuilder, path: string, value: any, alias?: string): QueryBuilder<TRecord, TResult>;
+  }
+
+  interface JsonRemove<TRecord extends {} = any, TResult extends {} = any> {
+    (column: string | Raw | QueryBuilder, path: string, alias?: string): QueryBuilder<TRecord, TResult>;
+  }
+
   interface HintComment<TRecord extends {} = any, TResult extends {} = any> {
     (hint: string): QueryBuilder<TRecord, TResult>;
     (hints: readonly string[]): QueryBuilder<TRecord, TResult>;
@@ -1328,6 +1383,8 @@ export declare namespace Knex {
     onNotBetween(column1: string, range: readonly [any, any]): JoinClause;
     andOnNotBetween(column1: string, range: readonly [any, any]): JoinClause;
     orOnNotBetween(column1: string, range: readonly [any, any]): JoinClause;
+    onJsonPathEquals(columnFirst: string, jsonPathFirst: string, columnSecond: string, jsonPathSecond: string): JoinClause;
+    orOnJsonPathEquals(columnFirst: string, jsonPathFirst: string, columnSecond: string, jsonPathSecond: string): JoinClause;
     using(
       column: string | readonly string[] | Raw | { [key: string]: string | Raw }
     ): JoinClause;
@@ -1463,6 +1520,14 @@ export declare namespace Knex {
     ): QueryBuilder<TRecord, TResult>;
   }
 
+  interface WhereJsonObject<TRecord = any, TResult = unknown[]> {
+    (columnName: keyof TRecord, value: any): QueryBuilder<TRecord, TResult>;
+  }
+
+  interface WhereJsonPath<TRecord = any, TResult = unknown[]> {
+    (columnName: keyof TRecord, jsonPath: string, operator: string, value: any): QueryBuilder<TRecord, TResult>;
+  }
+
   interface WhereIn<TRecord = any, TResult = unknown[]> {
     <K extends keyof ResolveTableType<TRecord>>(
       columnName: K,


Test patch:
diff --git a/test/integration2/query/select/joins.spec.js b/test/integration2/query/select/joins.spec.js
index e7177cb9..0dc3873e 100644
--- a/test/integration2/query/select/joins.spec.js
+++ b/test/integration2/query/select/joins.spec.js
@@ -7,6 +7,8 @@ const { isOracle, isMssql } = require('../../../util/db-helpers');
 const {
   insertTestTableTwoData,
   insertAccounts,
+  insertCities,
+  insertCountry,
 } = require('../../../util/dataInsertHelper');
 const {
   getAllDbs,
@@ -17,6 +19,8 @@ const {
   dropTables,
   createAccounts,
   createTestTableTwo,
+  createCities,
+  createCountry,
 } = require('../../../util/tableCreatorHelper');
 const { assertNumber } = require('../../../util/assertHelper');
 
@@ -1950,6 +1954,45 @@ describe('Joins', function () {
         assertNumber(knex, rows2[0].testcolumn, 50);
         assertNumber(knex, rows2[1].testcolumn, 70);
       });
+
+      describe('json joins', () => {
+        before(async () => {
+          await knex.schema.dropTableIfExists('cities');
+          await knex.schema.dropTableIfExists('country');
+          await createCities(knex);
+          await createCountry(knex);
+        });
+
+        beforeEach(async () => {
+          await knex('cities').truncate();
+          await knex('country').truncate();
+          await insertCities(knex);
+          await insertCountry(knex);
+        });
+
+        it('join on json path value', async () => {
+          const result = await knex('cities')
+            .select('cities.name as cityName', 'country.name as countryName')
+            .join('country', function () {
+              this.onJsonPathEquals(
+                'temperature',
+                '$.desc',
+                'climate',
+                '$.type'
+              );
+            });
+          expect(result).to.eql([
+            {
+              cityName: 'Paris',
+              countryName: 'France',
+            },
+            {
+              cityName: 'Milan',
+              countryName: 'Italy',
+            },
+          ]);
+        });
+      });
     });
   });
 });
diff --git a/test/integration2/query/select/selects.spec.js b/test/integration2/query/select/selects.spec.js
index 36891296..92b98bd8 100644
--- a/test/integration2/query/select/selects.spec.js
+++ b/test/integration2/query/select/selects.spec.js
@@ -20,12 +20,19 @@ const {
   createAccounts,
   createCompositeKeyTable,
   createTestTableTwo,
+  createCities,
   dropTables,
   createDefaultTable,
   createParentAndChildTables,
 } = require('../../../util/tableCreatorHelper');
-const { insertAccounts } = require('../../../util/dataInsertHelper');
-const { assertNumberArrayStrict } = require('../../../util/assertHelper');
+const {
+  insertAccounts,
+  insertCities,
+} = require('../../../util/dataInsertHelper');
+const {
+  assertNumberArrayStrict,
+  assertJsonEquals,
+} = require('../../../util/assertHelper');
 const {
   getAllDbs,
   getKnexForDb,
@@ -1247,6 +1254,285 @@ describe('Selects', function () {
             }
           );
       });
+
+      describe('json selections', () => {
+        before(async () => {
+          await knex.schema.dropTableIfExists('cities');
+          await createCities(knex);
+        });
+
+        beforeEach(async () => {
+          await knex('cities').truncate();
+          await insertCities(knex);
+        });
+
+        it('json extract', async () => {
+          const res = await knex
+            .jsonExtract('descriptions', '$.short', 'shortDescription')
+            .from('cities');
+          expect(res).to.eql([
+            { shortDescription: 'beautiful city' },
+            { shortDescription: 'large city' },
+            { shortDescription: 'cold city' },
+          ]);
+        });
+
+        it('json multiple extract', async () => {
+          const res = await knex
+            .jsonExtract([
+              ['descriptions', '$.short', 'shortDescription'],
+              ['population', '$.current.value', 'currentPop'],
+              ['statistics', '$.roads.min', 'minRoads'],
+              ['statistics', '$.hotYears[0]', 'firstHotYear'],
+            ])
+            .from('cities');
+          assertJsonEquals(
+            [res[0], res[1]],
+            [
+              {
+                shortDescription: 'beautiful city',
+                currentPop: 10000000,
+                minRoads: 1234,
+                firstHotYear: null,
+              },
+              {
+                shortDescription: 'large city',
+                currentPop: 1500000,
+                minRoads: 1455,
+                firstHotYear: 2012,
+              },
+            ]
+          );
+        });
+
+        it('json set', async function () {
+          // jsonSet is only for Oracle21c
+          if (isOracle(knex)) {
+            this.skip();
+          }
+          const res = await knex
+            .jsonSet('population', '$.minMax.max', '999999999', 'maxUpdated')
+            .from('cities');
+          assertJsonEquals(
+            [res[0].maxUpdated, res[1].maxUpdated],
+            [
+              {
+                current: { value: 10000000 },
+                minMax: {
+                  min: 50000,
+                  max: '999999999',
+                },
+              },
+              {
+                current: { value: 1500000 },
+                minMax: {
+                  min: 44000,
+                  max: '999999999',
+                },
+              },
+            ]
+          );
+        });
+
+        it('json insert', async function () {
+          // jsonInsert is only for Oracle21c
+          if (isOracle(knex)) {
+            this.skip();
+          }
+          const res = await knex
+            .jsonInsert('population', '$.year2021', '747477', 'popIn2021Added')
+            .from('cities');
+          assertJsonEquals(
+            [res[0].popIn2021Added, res[1].popIn2021Added],
+            [
+              {
+                current: {
+                  value: 10000000,
+                },
+                minMax: {
+                  min: 50000,
+                  max: 12000000,
+                },
+                year2021: '747477',
+              },
+              {
+                current: {
+                  value: 1500000,
+                },
+                minMax: {
+                  min: 44000,
+                  max: 1200000,
+                },
+                year2021: '747477',
+              },
+            ]
+          );
+        });
+
+        it('json remove', async function () {
+          // jsonRemove is only for Oracle21c
+          if (isOracle(knex)) {
+            this.skip();
+          }
+          const res = await knex
+            .jsonRemove('population', '$.minMax.min', 'popMinRemoved')
+            .from('cities');
+          assertJsonEquals(
+            [res[0].popMinRemoved, res[1].popMinRemoved],
+            [
+              {
+                current: {
+                  value: 10000000,
+                },
+                minMax: {
+                  max: 12000000,
+                },
+              },
+              {
+                current: {
+                  value: 1500000,
+                },
+                minMax: {
+                  max: 1200000,
+                },
+              },
+            ]
+          );
+        });
+
+        it('json insert then extract', async function () {
+          if (isOracle(knex)) {
+            this.skip();
+          }
+          const res = await knex
+            .jsonExtract(
+              knex.jsonInsert('population', '$.test', '1234'),
+              '$.test',
+              'insertExtract'
+            )
+            .from('cities');
+          assertJsonEquals(
+            [res[0], res[1]],
+            [{ insertExtract: '1234' }, { insertExtract: '1234' }]
+          );
+        });
+
+        it('json remove then extract', async function () {
+          if (isOracle(knex)) {
+            this.skip();
+          }
+          await knex
+            .jsonExtract(
+              knex.jsonRemove('population', '$.minMax.min'),
+              '$.minMax.max',
+              'maxPop'
+            )
+            .from('cities')
+            .testSql(function (tester) {
+              tester(
+                'mysql',
+                'select json_unquote(json_extract(json_remove(`population`,?), ?)) as `maxPop` from `cities`',
+                ['$.minMax.min', '$.minMax.max'],
+                [
+                  { maxPop: '12000000' },
+                  { maxPop: '1200000' },
+                  { maxPop: '1450000' },
+                ]
+              );
+              tester(
+                'pg',
+                'select jsonb_path_query("population" #- ?, ?) as "maxPop" from "cities"',
+                ['{minMax,min}', '$.minMax.max'],
+                [{ maxPop: 12000000 }, { maxPop: 1200000 }, { maxPop: 1450000 }]
+              );
+              tester(
+                'pgnative',
+                'select jsonb_path_query("population" #- ?, ?) as "maxPop" from "cities"',
+                ['{minMax,min}', '$.minMax.max'],
+                [{ maxPop: 12000000 }, { maxPop: 1200000 }, { maxPop: 1450000 }]
+              );
+              tester(
+                'pg-redshift',
+                'select jsonb_path_query("population" #- ?, ?) as "maxPop" from "cities"',
+                ['{minMax,min}', '$.minMax.max'],
+                [
+                  { maxPop: '12000000' },
+                  { maxPop: 1200000 },
+                  { maxPop: 1450000 },
+                ]
+              );
+              tester(
+                'sqlite3',
+                'select json_extract(json_remove(`population`,?), ?) as `maxPop` from `cities`',
+                ['$.minMax.min', '$.minMax.max'],
+                [{ maxPop: 12000000 }, { maxPop: 1200000 }, { maxPop: 1450000 }]
+              );
+              tester(
+                'mssql',
+                'select JSON_VALUE(JSON_MODIFY([population],?, NULL), ?) as [maxPop] from [cities]',
+                ['$.minMax.min', '$.minMax.max'],
+                [
+                  { maxPop: '12000000' },
+                  { maxPop: '1200000' },
+                  { maxPop: '1450000' },
+                ]
+              );
+            });
+        });
+
+        it('json remove, set then extract', async function () {
+          if (isOracle(knex)) {
+            this.skip();
+          }
+          const res = await knex
+            .jsonExtract(
+              [
+                [
+                  knex.jsonRemove('population', '$.minMax.min'),
+                  '$.minMax',
+                  'withoutMin',
+                ],
+                [
+                  knex.jsonRemove('population', '$.minMax.max'),
+                  '$.minMax',
+                  'withoutMax',
+                ],
+                [
+                  knex.jsonSet('population', '$.current.value', '1234'),
+                  '$.current',
+                  'currentModified',
+                ],
+              ],
+              false
+            )
+            .from('cities');
+          assertJsonEquals(res[0].currentModified, {
+            value: '1234',
+          });
+          assertJsonEquals(res[0].withoutMax, {
+            min: 50000,
+          });
+          assertJsonEquals(res[0].withoutMin, {
+            max: 12000000,
+          });
+
+          assertJsonEquals(res[1].currentModified, {
+            value: '1234',
+          });
+          assertJsonEquals(res[1].withoutMax, { min: 44000 });
+          assertJsonEquals(res[1].withoutMin, {
+            max: 1200000,
+          });
+
+          assertJsonEquals(res[2].currentModified, {
+            value: 1234,
+          });
+          assertJsonEquals(res[2].withoutMax, { min: 44000 });
+          assertJsonEquals(res[2].withoutMin, {
+            max: 1450000,
+          });
+        });
+      });
     });
   });
 });
diff --git a/test/integration2/query/select/where.spec.js b/test/integration2/query/select/where.spec.js
index cc04b6de..892eff05 100644
--- a/test/integration2/query/select/where.spec.js
+++ b/test/integration2/query/select/where.spec.js
@@ -19,8 +19,12 @@ const {
   createTestTableTwo,
   dropTables,
   createDefaultTable,
+  createCities,
 } = require('../../../util/tableCreatorHelper');
-const { insertAccounts } = require('../../../util/dataInsertHelper');
+const {
+  insertAccounts,
+  insertCities,
+} = require('../../../util/dataInsertHelper');
 
 const {
   getAllDbs,
@@ -28,6 +32,7 @@ const {
 } = require('../../util/knex-instance-provider');
 const logger = require('../../../integration/logger');
 const { TEST_TIMESTAMP } = require('../../../util/constants.js');
+const { assertJsonEquals } = require('../../../util/assertHelper');
 
 describe('Where', function () {
   getAllDbs().forEach((db) => {
@@ -607,6 +612,121 @@ describe('Where', function () {
         expect(raw.bindings).to.eql(expected1);
         expect(raw2.bindings).to.eql(expected2);
       });
+
+      describe('json wheres', () => {
+        before(async () => {
+          await knex.schema.dropTableIfExists('cities');
+          await createCities(knex);
+        });
+
+        beforeEach(async () => {
+          await knex('cities').truncate();
+          await insertCities(knex);
+        });
+
+        it('where json object', async () => {
+          const result = await knex('cities')
+            .select('name')
+            .whereJsonObject('descriptions', {
+              type: 'bigcity',
+              short: 'beautiful city',
+              long: 'beautiful and dirty city',
+            });
+          expect(result[0]).to.eql({
+            name: 'Paris',
+          });
+        });
+
+        it('where not json object', async () => {
+          const result = await knex('cities')
+            .select('name')
+            .whereNotJsonObject('descriptions', {
+              type: 'bigcity',
+              short: 'beautiful city',
+              long: 'beautiful and dirty city',
+            });
+          expect(result.length).to.equal(2);
+          assertJsonEquals(result, [
+            {
+              name: 'Milan',
+            },
+            {
+              name: 'Oslo',
+            },
+          ]);
+        });
+
+        it('where json path greater than numeric', async () => {
+          const result = await knex('cities')
+            .select('name')
+            .whereJsonPath('statistics', '$.roads.min', '>', 1000);
+          expect(result[0]).to.eql({
+            name: 'Paris',
+          });
+        });
+
+        it('where json path equal numeric', async () => {
+          const result = await knex('cities')
+            .select('name')
+            .whereJsonPath('statistics', '$.roads.min', '=', 1455);
+          expect(result[0]).to.eql({
+            name: 'Milan',
+          });
+        });
+
+        it('where multiple json path', async () => {
+          const result = await knex('cities')
+            .select('name')
+            .whereJsonPath('statistics', '$.roads.min', '<', 2000)
+            .andWhereJsonPath('temperature', '$.desc', '=', 'cold');
+          expect(result.length).to.equal(1);
+          assertJsonEquals(result, [
+            {
+              name: 'Paris',
+            },
+          ]);
+        });
+
+        it('where json superset of', async function () {
+          if (!(isPostgreSQL(knex) || isMysql(knex))) {
+            this.skip();
+          }
+          const result = await knex('cities')
+            .select('name')
+            // where descriptions json object contains type : 'bigcity'
+            .whereJsonSupersetOf('descriptions', {
+              type: 'bigcity',
+            });
+          expect(result.length).to.equal(2);
+          assertJsonEquals(result, [
+            {
+              name: 'Paris',
+            },
+            {
+              name: 'Milan',
+            },
+          ]);
+        });
+
+        it('where json subset of', async function () {
+          if (!(isPostgreSQL(knex) || isMysql(knex))) {
+            this.skip();
+          }
+          const result = await knex('cities')
+            .select('name')
+            // where temperature json object is included in given object
+            .whereJsonSubsetOf('temperature', {
+              desc: 'cold',
+              desc2: 'very cold',
+            });
+          expect(result.length).to.equal(1);
+          assertJsonEquals(result, [
+            {
+              name: 'Paris', // contains only desc: 'cold' but it's matched
+            },
+          ]);
+        });
+      });
     });
   });
 });
diff --git a/test/integration2/schema/primary-keys.spec.js b/test/integration2/schema/primary-keys.spec.js
index fa8d5d45..1de1612b 100644
--- a/test/integration2/schema/primary-keys.spec.js
+++ b/test/integration2/schema/primary-keys.spec.js
@@ -31,7 +31,7 @@ describe('Schema', () => {
         });
 
         afterEach(async () => {
-          await knex.schema.dropTable('primary_table');
+          await knex.schema.dropTableIfExists('primary_table');
         });
 
         describe('createPrimaryKey', () => {
@@ -63,6 +63,103 @@ describe('Schema', () => {
             }
           });
 
+          it('create multiple primary keys with increments on same column', async function () {
+            await knex.schema.dropTableIfExists('table_multiple_keys');
+            await knex.schema.createTable('table_multiple_keys', function (t) {
+              t.primary(['id', 'second_id', 'other_col']);
+              t.string('second_id', 16).notNullable();
+              t.integer('other_col').notNullable();
+              t.increments('id');
+            });
+            await knex('table_multiple_keys').insert([
+              {
+                second_id: 'abc',
+                other_col: 2,
+              },
+              {
+                second_id: 'abc',
+                other_col: 3,
+              },
+            ]);
+            expect(() => {
+              knex('table_multiple_keys').insert([
+                {
+                  id: 1,
+                  second_id: 'abc',
+                  other_col: 2,
+                },
+              ]);
+            }).to.throw;
+            // It's always ok, the primary key is on three columns.
+            await knex('table_multiple_keys').insert([
+              {
+                second_id: 'abc',
+                other_col: 2,
+              },
+              {
+                second_id: 'abc',
+                other_col: 3,
+              },
+            ]);
+            expect(() => {
+              knex('table_multiple_keys').insert([
+                {
+                  id: 4,
+                  second_id: 'abc',
+                  other_col: 3,
+                },
+              ]);
+            }).to.throw;
+          });
+
+          it('create multiple primary keys with increments on other columns', async function () {
+            await knex.schema.dropTableIfExists('table_multiple_keys');
+            await knex.schema.createTable('table_multiple_keys', function (t) {
+              t.primary(['second_id', 'other_col']);
+              t.string('second_id', 16).notNullable();
+              t.integer('other_col').notNullable();
+              t.increments('id');
+            });
+            await knex('table_multiple_keys').insert([
+              {
+                second_id: 'abc',
+                other_col: 2,
+              },
+              {
+                second_id: 'abc',
+                other_col: 3,
+              },
+            ]);
+            expect(() => {
+              knex('table_multiple_keys').insert([
+                {
+                  second_id: 'abc',
+                  other_col: 2,
+                },
+              ]);
+            }).to.throw;
+            // It's always ok, the primary key is on three columns.
+            await knex('table_multiple_keys').insert([
+              {
+                second_id: 'bcd',
+                other_col: 2,
+              },
+              {
+                second_id: 'abc',
+                other_col: 4,
+              },
+            ]);
+            expect(() => {
+              knex('table_multiple_keys').insert([
+                {
+                  id: 4,
+                  second_id: 'abc',
+                  other_col: 3,
+                },
+              ]);
+            }).to.throw;
+          });
+
           it('creates a primary key with a custom constraint name', async function () {
             // CockroachDB 21.1 throws "(72): unimplemented: primary key dropped without subsequent addition of new primary key in same transaction"
             if (isCockroachDB(knex)) {
diff --git a/test/unit/query/builder.js b/test/unit/query/builder.js
index c11047df..17c24c78 100644
--- a/test/unit/query/builder.js
+++ b/test/unit/query/builder.js
@@ -4792,6 +4792,28 @@ describe('QueryBuilder', () => {
     );
   });
 
+  it('on json path join', () => {
+    testsql(
+      qb()
+        .select('*')
+        .from('users')
+        .join('contacts', (qb) => {
+          qb.onJsonPathEquals(
+            'users.infos',
+            '$.name',
+            'contacts.data',
+            '$.infos.name'
+          );
+        }),
+      {
+        pg: {
+          sql: 'select * from "users" inner join "contacts" on jsonb_path_query_first("users"."infos", ?) = jsonb_path_query_first("contacts"."data", ?)',
+          bindings: ['$.name', '$.infos.name'],
+        },
+      }
+    );
+  });
+
   it('raw expressions in select', () => {
     testsql(qb().select(raw('substr(foo, 6)')).from('users'), {
       mysql: {
@@ -10416,4 +10438,535 @@ describe('QueryBuilder', () => {
       );
     });
   });
+
+  describe('json functions', () => {
+    describe('json manipulation', () => {
+      it('should extract json value', () => {
+        testsql(qb().jsonExtract('name', '$.names.firstName').from('users'), {
+          pg: {
+            sql: 'select jsonb_path_query("name", ?) from "users"',
+            bindings: ['$.names.firstName'],
+          },
+          mysql: {
+            sql: 'select json_unquote(json_extract(`name`, ?)) from `users`',
+            bindings: ['$.names.firstName'],
+          },
+          mssql: {
+            sql: 'select JSON_VALUE([name], ?) from [users]',
+            bindings: ['$.names.firstName'],
+          },
+          oracledb: {
+            sql: 'select json_value("name", \'$.names.firstName\') from "users"',
+            bindings: [],
+          },
+          sqlite3: {
+            sql: 'select json_extract(`name`, ?) from `users`',
+            bindings: ['$.names.firstName'],
+          },
+          'pg-redshift': {
+            sql: 'select json_extract_path_text("name", ?, ?) from "users"',
+            bindings: ['names', 'firstName'],
+          },
+          cockroachdb: {
+            sql: 'select json_extract_path("name", ?, ?) from "users"',
+            bindings: ['names', 'firstName'],
+          },
+        });
+      });
+
+      it('should extract json value with alias', () => {
+        testsql(qb().jsonExtract('json_col', '$.name', 'name').from('users'), {
+          pg: {
+            sql: 'select jsonb_path_query("json_col", ?) as "name" from "users"',
+            bindings: ['$.name'],
+          },
+          mysql: {
+            sql: 'select json_unquote(json_extract(`json_col`, ?)) as `name` from `users`',
+            bindings: ['$.name'],
+          },
+          mssql: {
+            sql: 'select JSON_VALUE([json_col], ?) as [name] from [users]',
+            bindings: ['$.name'],
+          },
+          oracledb: {
+            sql: 'select json_value("json_col", \'$.name\') "name" from "users"',
+            bindings: [],
+          },
+          sqlite3: {
+            sql: 'select json_extract(`json_col`, ?) as `name` from `users`',
+            bindings: ['$.name'],
+          },
+          'pg-redshift': {
+            sql: 'select json_extract_path_text("json_col", ?) as "name" from "users"',
+            bindings: ['name'],
+          },
+          cockroachdb: {
+            sql: 'select json_extract_path("json_col", ?) as "name" from "users"',
+            bindings: ['name'],
+          },
+        });
+      });
+
+      it('should extract json values with mutiple extracts', () => {
+        testsql(
+          qb()
+            .jsonExtract([
+              ['json_col', '$.name', 'name'],
+              ['json_col', '$.last_name', 'last_name'],
+              ['json_col', '$.infos.age', 'age'],
+              ['json_col', '$.infos.gender', 'gender'],
+            ])
+            .from('users'),
+          {
+            pg: {
+              sql:
+                'select jsonb_path_query("json_col", ?) as "name", jsonb_path_query("json_col", ?) as "last_name", ' +
+                'jsonb_path_query("json_col", ?) as "age", jsonb_path_query("json_col", ?) as "gender" from "users"',
+              bindings: [
+                '$.name',
+                '$.last_name',
+                '$.infos.age',
+                '$.infos.gender',
+              ],
+            },
+            mysql: {
+              sql:
+                'select json_unquote(json_extract(`json_col`, ?)) as `name`, json_unquote(json_extract(`json_col`, ?)) ' +
+                'as `last_name`, json_unquote(json_extract(`json_col`, ?)) as `age`, json_unquote(json_extract' +
+                '(`json_col`, ?)) as `gender` from `users`',
+              bindings: [
+                '$.name',
+                '$.last_name',
+                '$.infos.age',
+                '$.infos.gender',
+              ],
+            },
+            mssql: {
+              sql:
+                'select JSON_VALUE([json_col], ?) as [name], JSON_VALUE([json_col], ?) as [last_name], ' +
+                'JSON_VALUE([json_col], ?) as [age], JSON_VALUE([json_col], ?) as [gender] from [users]',
+              bindings: [
+                '$.name',
+                '$.last_name',
+                '$.infos.age',
+                '$.infos.gender',
+              ],
+            },
+            oracledb: {
+              sql:
+                'select json_value("json_col", \'$.name\') "name", json_value("json_col", \'$.last_name\') "last_name",' +
+                ' json_value("json_col", \'$.infos.age\') "age", json_value("json_col", \'$.infos.gender\') "gender" from "users"',
+              bindings: [],
+            },
+            sqlite3: {
+              sql:
+                'select json_extract(`json_col`, ?) as `name`, json_extract(`json_col`, ?) as `last_name`, ' +
+                'json_extract(`json_col`, ?) as `age`, json_extract(`json_col`, ?) as `gender` from `users`',
+              bindings: [
+                '$.name',
+                '$.last_name',
+                '$.infos.age',
+                '$.infos.gender',
+              ],
+            },
+            'pg-redshift': {
+              sql:
+                'select json_extract_path_text("json_col", ?) as "name", ' +
+                'json_extract_path_text("json_col", ?) as "last_name", json_extract_path_text("json_col", ?, ?) as "age", ' +
+                'json_extract_path_text("json_col", ?, ?) as "gender" from "users"',
+              bindings: [
+                'name',
+                'last_name',
+                'infos',
+                'age',
+                'infos',
+                'gender',
+              ],
+            },
+            cockroachdb: {
+              sql:
+                'select json_extract_path("json_col", ?) as "name", json_extract_path("json_col", ?) as "last_name",' +
+                ' json_extract_path("json_col", ?, ?) as "age", json_extract_path("json_col", ?, ?) as "gender" from "users"',
+              bindings: [
+                'name',
+                'last_name',
+                'infos',
+                'age',
+                'infos',
+                'gender',
+              ],
+            },
+          }
+        );
+      });
+
+      it('should set json value', () => {
+        testsql(
+          qb().jsonSet('address', '$.street.numbers[2]', '5').from('users'),
+          {
+            pg: {
+              sql: 'select jsonb_set("address", ?, ?) from "users"',
+              bindings: ['{street,numbers,2}', '5'],
+            },
+            mysql: {
+              sql: 'select json_set(`address`, ?, ?) from `users`',
+              bindings: ['$.street.numbers[2]', '5'],
+            },
+            mssql: {
+              sql: 'select JSON_MODIFY([address], ?, ?) from [users]',
+              bindings: ['$.street.numbers[2]', '5'],
+            },
+            oracledb: {
+              sql: 'select json_transform("address", set ? = ?) from "users"',
+              bindings: ['$.street.numbers[2]', '5'],
+            },
+            sqlite3: {
+              sql: 'select json_set(`address`, ?, ?) from `users`',
+              bindings: ['$.street.numbers[2]', '5'],
+            },
+            cockroachdb: {
+              sql: 'select jsonb_set("address", ?, ?) from "users"',
+              bindings: ['{street,numbers,2}', '5'],
+            },
+          }
+        );
+      });
+
+      it('should set json value with pg path syntax', async function () {
+        testsql(
+          qb().jsonSet('address', '{street,numbers,2}', '5').from('users'),
+          {
+            pg: {
+              sql: 'select jsonb_set("address", ?, ?) from "users"',
+              bindings: ['{street,numbers,2}', '5'],
+            },
+            cockroachdb: {
+              sql: 'select jsonb_set("address", ?, ?) from "users"',
+              bindings: ['{street,numbers,2}', '5'],
+            },
+          }
+        );
+      });
+
+      it('should set json value with nested function', async function () {
+        testsql(
+          qb()
+            .jsonSet(
+              qb().jsonExtract('cities', '$.mainStreet'),
+              '$.street.numbers[1]',
+              "{'test': 2}"
+            )
+            .from('users'),
+          {
+            pg: {
+              sql: 'select jsonb_set(jsonb_path_query("cities", ?), ?, ?) from "users"',
+              bindings: ['$.mainStreet', '{street,numbers,1}', "{'test': 2}"],
+            },
+            mysql: {
+              sql: 'select json_set(json_unquote(json_extract(`cities`, ?)), ?, ?) from `users`',
+              bindings: ['$.mainStreet', '$.street.numbers[1]', "{'test': 2}"],
+            },
+            mssql: {
+              sql: 'select JSON_MODIFY(JSON_VALUE([cities], ?), ?, ?) from [users]',
+              bindings: ['$.mainStreet', '$.street.numbers[1]', "{'test': 2}"],
+            },
+            oracledb: {
+              sql: 'select json_transform(json_value("cities", \'$.mainStreet\'), set ? = ?) from "users"',
+              bindings: ['$.street.numbers[1]', "{'test': 2}"],
+            },
+            sqlite3: {
+              sql: 'select json_set(json_extract(`cities`, ?), ?, ?) from `users`',
+              bindings: ['$.mainStreet', '$.street.numbers[1]', "{'test': 2}"],
+            },
+            cockroachdb: {
+              sql: 'select jsonb_set(json_extract_path("cities", ?), ?, ?) from "users"',
+              bindings: ['mainStreet', '{street,numbers,1}', "{'test': 2}"],
+            },
+          }
+        );
+      });
+
+      it('should insert json', async function () {
+        testsql(qb().jsonInsert('address', '$.mainStreet', '5').from('users'), {
+          pg: {
+            sql: 'select jsonb_insert("address", ?, ?) from "users"',
+            bindings: ['{mainStreet}', '5'],
+          },
+          mysql: {
+            sql: 'select json_insert(`address`, ?, ?) from `users`',
+            bindings: ['$.mainStreet', '5'],
+          },
+          mssql: {
+            sql: 'select JSON_MODIFY([address], ?, ?) from [users]',
+            bindings: ['$.mainStreet', '5'],
+          },
+          oracledb: {
+            sql: 'select json_transform("address", insert ? = ?) from "users"',
+            bindings: ['$.mainStreet', '5'],
+          },
+          sqlite3: {
+            sql: 'select json_insert(`address`, ?, ?) from `users`',
+            bindings: ['$.mainStreet', '5'],
+          },
+          cockroachdb: {
+            sql: 'select jsonb_insert("address", ?, ?) from "users"',
+            bindings: ['{mainStreet}', '5'],
+          },
+        });
+      });
+
+      it('should remove path in json', async function () {
+        testsql(qb().jsonRemove('address', '$.street[1]').from('users'), {
+          pg: {
+            sql: 'select "address" #- ? from "users"',
+            bindings: ['{street,1}'],
+          },
+          mysql: {
+            sql: 'select json_remove(`address`,?) from `users`',
+            bindings: ['$.street[1]'],
+          },
+          mssql: {
+            sql: 'select JSON_MODIFY([address],?, NULL) from [users]',
+            bindings: ['$.street[1]'],
+          },
+          oracledb: {
+            sql: 'select json_transform("address", remove ?) from "users"',
+            bindings: ['$.street[1]'],
+          },
+          sqlite3: {
+            sql: 'select json_remove(`address`,?) from `users`',
+            bindings: ['$.street[1]'],
+          },
+          cockroachdb: {
+            sql: 'select "address" #- ? from "users"',
+            bindings: ['{street,1}'],
+          },
+        });
+      });
+
+      it('should insert then extract', async function () {
+        testsql(
+          qb()
+            .jsonExtract(
+              qb().jsonInsert('population', '$.test', '1234'),
+              '$.test',
+              'insertExtract'
+            )
+            .from('cities'),
+          {
+            pg: {
+              sql: 'select jsonb_path_query(jsonb_insert("population", ?, ?), ?) as "insertExtract" from "cities"',
+              bindings: ['{test}', '1234', '$.test'],
+            },
+            mysql: {
+              sql: 'select json_unquote(json_extract(json_insert(`population`, ?, ?), ?)) as `insertExtract` from `cities`',
+              bindings: ['$.test', '1234', '$.test'],
+            },
+            mssql: {
+              sql: 'select JSON_VALUE(JSON_MODIFY([population], ?, ?), ?) as [insertExtract] from [cities]',
+              bindings: ['$.test', '1234', '$.test'],
+            },
+            oracledb: {
+              sql: 'select json_value(json_transform("population", insert ? = ?), \'$.test\') "insertExtract" from "cities"',
+              bindings: ['$.test', '1234'],
+            },
+            sqlite3: {
+              sql: 'select json_extract(json_insert(`population`, ?, ?), ?) as `insertExtract` from `cities`',
+              bindings: ['$.test', '1234', '$.test'],
+            },
+            cockroachdb: {
+              sql: 'select json_extract_path(jsonb_insert("population", ?, ?), ?) as "insertExtract" from "cities"',
+              bindings: ['{test}', '1234', 'test'],
+            },
+          }
+        );
+      });
+    });
+
+    describe('where json', function () {
+      it('where equals json', async function () {
+        testsql(
+          qb()
+            .select()
+            .from('users')
+            .whereJsonObject('address', { street: 'street1', number: 5 })
+            .orWhereNotJsonObject('address', {
+              street: 'street2',
+              number: 7,
+            }),
+          {
+            pg: {
+              sql: 'select * from "users" where "address" = ? or "address" != ?',
+              bindings: [
+                '{"street":"street1","number":5}',
+                '{"street":"street2","number":7}',
+              ],
+            },
+            mysql: {
+              sql: 'select * from `users` where json_contains(`address`, ?) or not json_contains(`address`, ?)',
+              bindings: [
+                '{"street":"street1","number":5}',
+                '{"street":"street2","number":7}',
+              ],
+            },
+            mssql: {
+              sql: 'select * from [users] where [address] = ? or [address] != ?',
+              bindings: [
+                '{"street":"street1","number":5}',
+                '{"street":"street2","number":7}',
+              ],
+            },
+            oracledb: {
+              sql: 'select * from "users" where "address" = ? or "address" != ?',
+              bindings: [
+                '{"street":"street1","number":5}',
+                '{"street":"street2","number":7}',
+              ],
+            },
+            sqlite3: {
+              sql: 'select * from `users` where `address` = ? or `address` != ?',
+              bindings: [
+                '{"street":"street1","number":5}',
+                '{"street":"street2","number":7}',
+              ],
+            },
+            cockroachdb: {
+              sql: 'select * from "users" where "address" = ? or "address" != ?',
+              bindings: [
+                '{"street":"street1","number":5}',
+                '{"street":"street2","number":7}',
+              ],
+            },
+          }
+        );
+      });
+
+      it('where json column is equals to the value returned by a json path', async function () {
+        testsql(
+          qb()
+            .select()
+            .from('users')
+            .whereJsonPath('address', '$.street.number', '>', 5),
+          {
+            pg: {
+              sql: 'select * from "users" where jsonb_path_query_first("address", ?)::int > ?',
+              bindings: ['$.street.number', 5],
+            },
+            mysql: {
+              sql: 'select * from `users` where json_extract(`address`, ?) > ?',
+              bindings: ['$.street.number', 5],
+            },
+            mssql: {
+              sql: 'select * from [users] where JSON_VALUE([address], ?) > ?',
+              bindings: ['$.street.number', 5],
+            },
+            oracledb: {
+              sql: 'select * from "users" where json_value("address", \'$.street.number\') > ?',
+              bindings: [5],
+            },
+            sqlite3: {
+              sql: 'select * from `users` where json_extract(`address`, ?) > ?',
+              bindings: ['$.street.number', 5],
+            },
+            cockroachdb: {
+              sql: 'select * from "users" where json_extract_path("address", ?, ?)::int > ?',
+              bindings: ['street', 'number', 5],
+            },
+          }
+        );
+      });
+
+      it('where a json column is a superset of value', async function () {
+        testsql(
+          qb()
+            .select()
+            .from('users')
+            .whereJsonSupersetOf('address', { test: 'value' }),
+          {
+            pg: {
+              sql: 'select * from "users" where "address" @> ?',
+              bindings: ['{"test":"value"}'],
+            },
+            mysql: {
+              sql: 'select * from `users` where json_contains(`address`,?)',
+              bindings: ['{"test":"value"}'],
+            },
+            cockroachdb: {
+              sql: 'select * from "users" where "address" @> ?',
+              bindings: ['{"test":"value"}'],
+            },
+          }
+        );
+      });
+
+      it('where a json column is not a superset of value', async function () {
+        testsql(
+          qb()
+            .select()
+            .from('users')
+            .whereJsonNotSupersetOf('address', { test: 'value' }),
+          {
+            pg: {
+              sql: 'select * from "users" where not "address" @> ?',
+              bindings: ['{"test":"value"}'],
+            },
+            mysql: {
+              sql: 'select * from `users` where not json_contains(`address`,?)',
+              bindings: ['{"test":"value"}'],
+            },
+            cockroachdb: {
+              sql: 'select * from "users" where not "address" @> ?',
+              bindings: ['{"test":"value"}'],
+            },
+          }
+        );
+      });
+
+      it('where a json column be a subset of value', async function () {
+        testsql(
+          qb()
+            .select()
+            .from('users')
+            .whereJsonSubsetOf('address', { test: 'value' }),
+          {
+            pg: {
+              sql: 'select * from "users" where "address" <@ ?',
+              bindings: ['{"test":"value"}'],
+            },
+            mysql: {
+              sql: 'select * from `users` where json_contains(?,`address`)',
+              bindings: ['{"test":"value"}'],
+            },
+            cockroachdb: {
+              sql: 'select * from "users" where "address" <@ ?',
+              bindings: ['{"test":"value"}'],
+            },
+          }
+        );
+      });
+
+      it('where a json column is not subset of value', async function () {
+        testsql(
+          qb()
+            .select()
+            .from('users')
+            .whereJsonNotSubsetOf('address', { test: 'value' }),
+          {
+            pg: {
+              sql: 'select * from "users" where not "address" <@ ?',
+              bindings: ['{"test":"value"}'],
+            },
+            mysql: {
+              sql: 'select * from `users` where not json_contains(?,`address`)',
+              bindings: ['{"test":"value"}'],
+            },
+            cockroachdb: {
+              sql: 'select * from "users" where not "address" <@ ?',
+              bindings: ['{"test":"value"}'],
+            },
+          }
+        );
+      });
+    });
+  });
 });
diff --git a/test/unit/schema-builder/mysql.js b/test/unit/schema-builder/mysql.js
index dbd8c7ac..00b7fcaa 100644
--- a/test/unit/schema-builder/mysql.js
+++ b/test/unit/schema-builder/mysql.js
@@ -68,13 +68,14 @@ module.exports = function (dialect) {
         .schemaBuilder()
         .createTable('users', function (table) {
           table.increments('id');
+          // In MySQL a autoincrement column is always a primary key
           table.increments('other_id', { primaryKey: false });
         })
         .toSQL();
 
       equal(1, tableSql.length);
       expect(tableSql[0].sql).to.equal(
-        'create table `users` (`id` int unsigned not null auto_increment primary key, `other_id` int unsigned not null auto_increment)'
+        'create table `users` (`id` int unsigned not null auto_increment primary key, `other_id` int unsigned not null)'
       );
     });
 
@@ -665,13 +666,14 @@ module.exports = function (dialect) {
       tableSql = client
         .schemaBuilder()
         .table('users', function () {
+          // In MySQL a autoincrement column is always a primary key
           this.bigIncrements('id', { primaryKey: false });
         })
         .toSQL();
 
       equal(1, tableSql.length);
       expect(tableSql[0].sql).to.equal(
-        'alter table `users` add `id` bigint unsigned not null auto_increment'
+        'alter table `users` add `id` bigint unsigned not null'
       );
     });
 
diff --git a/test/unit/schema-builder/oracle.js b/test/unit/schema-builder/oracle.js
index ca8eba8d..ea5df400 100644
--- a/test/unit/schema-builder/oracle.js
+++ b/test/unit/schema-builder/oracle.js
@@ -76,7 +76,7 @@ describe('Oracle SchemaBuilder', function () {
         table.increments('id', { primaryKey: false });
       });
 
-    equal(2, tableSql.toSQL().length);
+    expect(tableSql.toSQL().length).to.equal(2);
     expect(tableSql.toSQL()[0].sql).to.equal(
       'begin execute immediate \'create table "users" ("id" integer not null)\'; exception when others then if sqlcode != -955 then raise; end if; end;'
     );
diff --git a/test/unit/schema-builder/sqlite3.js b/test/unit/schema-builder/sqlite3.js
index 1333c390..c0713737 100644
--- a/test/unit/schema-builder/sqlite3.js
+++ b/test/unit/schema-builder/sqlite3.js
@@ -214,15 +214,15 @@ describe('SQLite SchemaBuilder', function () {
     tableSql = client
       .schemaBuilder()
       .createTable('users', function (table) {
-        table.increments('id');
-        table.increments('other_id', { primaryKey: false });
+        // This make nothing in SQLite, autoincrement without primary key is a syntax error.
+        table.increments('id', { primaryKey: false });
       })
       .toSQL();
 
     equal(1, tableSql.length);
     equal(
       tableSql[0].sql,
-      'create table `users` (`id` integer not null primary key autoincrement, `other_id` integer not null autoincrement)'
+      'create table `users` (`id` integer not null primary key autoincrement)'
     );
   });
 
@@ -563,6 +563,7 @@ describe('SQLite SchemaBuilder', function () {
     tableSql = client
       .schemaBuilder()
       .table('users', function (table) {
+        // This make nothing in SQLite, autoincrement without primary key is a syntax error.
         table.bigIncrements('id', { primaryKey: false });
       })
       .toSQL();
@@ -570,7 +571,7 @@ describe('SQLite SchemaBuilder', function () {
     equal(1, tableSql.length);
     equal(
       tableSql[0].sql,
-      'alter table `users` add column `id` integer not null autoincrement'
+      'alter table `users` add column `id` integer not null primary key autoincrement'
     );
   });
 
diff --git a/test/util/assertHelper.js b/test/util/assertHelper.js
index 42f7f392..7b2a6a4c 100644
--- a/test/util/assertHelper.js
+++ b/test/util/assertHelper.js
@@ -1,5 +1,6 @@
 const { isCockroachDB } = require('./db-helpers');
 const { expect } = require('chai');
+const { isObject } = require('../../lib/util/is');
 
 function normalizePath(pathEntry) {
   return pathEntry.replace(/\\/g, '/');
@@ -40,10 +41,42 @@ function assertNumber(knex, id, expectedId) {
   }
 }
 
+function stringifyJsonValues(json) {
+  if (Array.isArray(json)) {
+    return json.map((j) => stringifyJsonValues(j));
+  } else if (isObject(json)) {
+    Object.keys(json).map((k, i) => {
+      json[k] = stringifyJsonValues(json[k]);
+    });
+    return json;
+  } else {
+    return json !== null && json !== undefined ? json.toString() : json;
+  }
+}
+
+function assertJsonEquals(jsons, jsonsExpected) {
+  if (!Array.isArray(jsons)) {
+    jsons = [jsons];
+  }
+  if (!Array.isArray(jsonsExpected)) {
+    jsonsExpected = [jsonsExpected];
+  }
+  expect(jsons.length).to.equal(jsonsExpected.length);
+  jsons.forEach((json, i) => {
+    const jsonExpected = JSON.parse(
+      isObject(json) ? JSON.stringify(json) : json
+    );
+    expect(stringifyJsonValues(jsonExpected)).to.eql(
+      stringifyJsonValues(jsonsExpected[i])
+    );
+  });
+}
+
 module.exports = {
   assertNumber,
   assertNumberArray,
   assertNumberArrayStrict,
   normalizePath,
   normalizePathArray,
+  assertJsonEquals,
 };
diff --git a/test/util/dataInsertHelper.js b/test/util/dataInsertHelper.js
index 7e4230a6..2394b58d 100644
--- a/test/util/dataInsertHelper.js
+++ b/test/util/dataInsertHelper.js
@@ -119,8 +119,115 @@ function insertAccount(knex, overrides) {
   );
 }
 
+async function insertCities(knex) {
+  await knex('cities').insert({
+    name: 'Paris',
+    population: {
+      current: {
+        value: 10000000,
+      },
+      minMax: {
+        min: 50000,
+        max: 12000000,
+      },
+    },
+    descriptions: {
+      type: 'bigcity',
+      short: 'beautiful city',
+      long: 'beautiful and dirty city',
+    },
+    statistics: {
+      size: 1000000,
+      buildings: 1232121,
+      roads: {
+        min: 1234,
+        max: 1333,
+      },
+    },
+    temperature: {
+      desc: 'cold',
+    },
+  });
+  await knex('cities').insert({
+    name: 'Milan',
+    population: {
+      current: {
+        value: 1500000,
+      },
+      minMax: {
+        min: 44000,
+        max: 1200000,
+      },
+    },
+    descriptions: {
+      type: 'bigcity',
+      short: 'large city',
+      long: 'large and sunny city',
+    },
+    statistics: {
+      size: 1200000,
+      buildings: 12121,
+      roads: {
+        min: 1455,
+        max: 1655,
+      },
+      hotYears: [2012, 2015, 2021],
+    },
+    temperature: {
+      desc: 'warm',
+    },
+  });
+  await knex('cities').insert({
+    name: 'Oslo',
+    population: {
+      current: {
+        value: 1456478,
+      },
+      minMax: {
+        min: 44000,
+        max: 1450000,
+      },
+    },
+    descriptions: {
+      type: 'city',
+      short: 'cold city',
+      long: 'cold and cool city',
+    },
+    statistics: {
+      size: 4500000,
+      buildings: 44454,
+      roads: {
+        min: 140,
+        max: 156,
+      },
+      hotYears: [2016],
+    },
+    temperature: {
+      desc: 'verycold',
+      desc2: 'very cold',
+    },
+  });
+}
+
+async function insertCountry(knex) {
+  await knex('country').insert({
+    name: 'France',
+    climate: {
+      type: 'cold',
+    },
+  });
+  await knex('country').insert({
+    name: 'Italy',
+    climate: {
+      type: 'warm',
+    },
+  });
+}
+
 module.exports = {
   insertAccount,
   insertAccounts,
   insertTestTableTwoData,
+  insertCities,
+  insertCountry,
 };
diff --git a/test/util/tableCreatorHelper.js b/test/util/tableCreatorHelper.js
index 64359881..dedcff94 100644
--- a/test/util/tableCreatorHelper.js
+++ b/test/util/tableCreatorHelper.js
@@ -95,6 +95,23 @@ async function createParentAndChildTables(knex) {
   });
 }
 
+async function createCities(knex) {
+  await knex.schema.createTable('cities', (table) => {
+    table.string('name');
+    table.jsonb('population');
+    table.jsonb('descriptions');
+    table.jsonb('statistics');
+    table.jsonb('temperature');
+  });
+}
+
+async function createCountry(knex) {
+  await knex.schema.createTable('country', (table) => {
+    table.string('name');
+    table.jsonb('climate');
+  });
+}
+
 async function dropTables(knex) {
   await knex.schema.dropTableIfExists('accounts');
   await knex.schema.dropTableIfExists('users');
@@ -117,5 +134,7 @@ module.exports = {
   createUsers,
   createTestTableTwo,
   createParentAndChildTables,
+  createCities,
+  createCountry,
   dropTables,
 };