Repository: knex/knex
Pull request #4859 (2021-11-28 21:31:53)

Json advanced support
I start https://github.com/knex/knex/issues/1337 1 month ago and after some iterations and pauses, I push a draft if anyone want to start a review, it's quite big.
JSON support is quite challenging, because all database have support of JSON, but in really different ways (syntax, cast, paths...)

Implemented :
- Json extraction of data.

one extraction way (json column, jsonpath, alias):
```js
 const res = await knex
    .jsonExtract('descriptions', '$.short', 'shortDescription')
    .from('cities');
```

multiple extraction way (pretty powerful):
```js
const res = await knex
    .jsonExtract([
      ['descriptions', '$.short', 'shortDescription'],
      ['population', '$.current', 'currentPop'],
      ['statistics', '$.roads.min', 'minRoads'],
      ['statistics', '$.hotYears[0]', 'firstHotYear'],
    ])
    .from('cities');
```

- Json manipulation of data

Json set, json insert, json remove (alias support)

```js
  const res = await knex
    .jsonSet('population', '$.max', '999999999', 'maxUpdated')
    .from('cities');
```

```js
const res = await knex
    .jsonInsert('population', '$.year2021', '747477', 'popIn2021Added')
    .from('cities');
```

```js
const res = await knex
  .jsonRemove('population', '$.min', 'popMinRemoved')
  .from('cities');
```

All extraction + manipulation functions are made to be used like 'select' or by nested like : 

```js
const res = await knex
  .jsonExtract([
    [knex.jsonRemove('population', '$.min'), '$', 'withoutMin'],
    [knex.jsonRemove('population', '$.max'), '$', 'withoutMax'],
    [
      knex.jsonSet('population', '$.current', '1234'),
      '$',
      'currentModified',
    ],
  ])
  .from('cities');
```

- Wheres JSON

* whereJsonObject (and/or/not functions) : test if column is equals to json object.
* whereJsonPath (and/or functions) : test if column is equals to value of json path (work with text and with >, < and numeric ).
* whereJsonSupersetOf (and/or/not  functions) : test if json column is superset of json (only postgres/mysql).
* whereJsonSubsetOf (and/or/not  functions) : test if json column is subset of json (only postgres/mysql).

- JSON Join

It's possible to join tables by json value from json columns like : 

```js
 knex.select('*')
    .from('users')
    .join('contacts', (qb) => {
      qb.onJsonPathEquals(
        'users.infos',
        '$.name',
        'contacts.data',
        '$.infos.name'
      );
    })
```

- Support of JSON object in value of JSON functions, you can put json in strings always, but it's way clear to put json objects. Knex will detect it and stringify it.

kibertoad: @rijkvanzanten Any chance you guys could help test this with Oracle? I know you support it in Directus, so I assume you have working image on the ready :)
OlivierCavadenti: > @rijkvanzanten Any chance you guys could help test this with Oracle? I know you support it in Directus, so I assume you have working image on the ready :)

I will update all tests + typings + documentation this week and I will able to run test in Oracle (I install all tools and have a new blazing fast computer \o/ ).
intech: @OlivierCavadenti when you complete the main work and there are only cockroach tests left, call me to this thread, I will add replacement functions for the dialect. Thanks!
OlivierCavadenti: > @OlivierCavadenti when you complete the main work and there are only cockroach tests left, call me to this thread, I will add replacement functions for the dialect.

Cool ! Yeah I will do that. You will be able to push on the branch (I create a branch on this repo but it's not really my goal... I will push later my other PR on my fork, like other users xD).

> And I suggest adding a cockroach tag and using that so I can quickly find topics that need help with cockroach.

Really thanks for the suggestion, I will do that.
rijkvanzanten: > @rijkvanzanten Any chance you guys could help test this with Oracle? I know you support it in Directus, so I assume you have working image on the ready :)

Totally! We've based our docker-compose setup on the one included in the knex tests, and altered it slightly to use a smaller image. Not sure how that affects the Knex test suite necessarily, but the image itself runs fine and allows for easy (local) debugging:

```yaml
# In docker-compose.yaml
# ...

  oracle:
    image: quillbuilduser/oracle-18-xe-micro-sq
    ports:
      - 1521:1521
    environment:
      - OPATCH_JRE_MEMORY_OPTIONS=-Xms128m -Xmx256m -XX:PermSize=16m -XX:MaxPermSize=32m -Xss1m
      - ORACLE_ALLOW_REMOTE=true
    shm_size: '1gb' # more like smh-size ammirite 🥁

# ...
```

One thing to note: we've had _a lot_ of trouble getting NodeJS to connect properly to OracleDB in a containerized environment though, due to Oracle's dependency on their `instantclient` package.. I'm also pretty sure this image will not run at all on newer ARM based machines (eg Macs with M1).
kibertoad: @OlivierCavadenti I am thinking about releasing 1.0.0 after this lands, we have plenty of good stuff lined up already. Do you feel like anything else should make the cut too?
OlivierCavadenti: > @OlivierCavadenti I am thinking about releasing 1.0.0 after this lands, we have plenty of good stuff lined up already. Do you feel like anything else should make the cut too?

No, I will finish this and work to polish checks constraints (I also have castTo functions in the pipe) but I think is better to stabilize all the stuff.  And Json Indexes will be in a second part I think
OlivierCavadenti: > @OlivierCavadenti I am thinking about releasing 1.0.0 after this lands, we have plenty of good stuff lined up already. Do you feel like anything else should make the cut too?

I add all missing unit tests (finally, integration seems good) and update index.d.ts (I just need to add json manipulations functions, I am not familiar with complex TypeScript index.d.ts types so I will update later).

I made some tests on Oracle and unfortunaly I have ' ORA-40454: path expression not a literal' errors (json path is a classic string parameters... need to investigate) and other syntax errors for other functions (json_transform is introduced in Oracle 21c...).
So if I fix the path expression error, I assume column json type addition I made + some of where clauses can be used with Oracle. I need to install Oracle 21c to check Json transform functions, if we decide to add them with version restriction (available only with Oracle 21c+).

OlivierCavadenti: @intech can you check cockroach tests plz ?
OlivierCavadenti: @kibertoad I push typings so I think I am done with this first version. I will push documentation in 1 or 2 days.
intech: > @intech can you check cockroach tests plz ?

Yes! How do we deal with the `jsonb_path_query_first` function that is not supported? I will have to duplicate the code again in order to replace this function with another in all requests.
Can we move it into a variable? Perhaps we will move all the functions into some kind of enumeration so that they can be redefined there?
OlivierCavadenti: > > @intech can you check cockroach tests plz ?
> 
> Yes! How do we deal with the `jsonb_path_query_first` function that is not supported? I will have to duplicate the code again in order to replace this function with another in all requests. Can we move it into a variable? Perhaps we will move all the functions into some kind of enumeration so that they can be redefined there?

 I made a generic '_jsonExtract' function in querycompiler that take the name of json function to query and params (column, path, value,...). For example `this._jsonExtract('jsonb_path_query', params)` in PG.
It seems CockroachDB dont support JsonPath queries and only '->' and '->>' notations to query json ? 
I can check what we can do to support that notation to provide a support for basic json querying.

Maybe specify jsonExtract and override _jsonExtract function in querycompiler that will transform a jsonPath from 'params.path' variable into '->' expression (like toPathForJson functions for RedShift and PG in index.js files). If we cant transform jsonPath into '->' notation, we will raise an error.
OlivierCavadenti: @intech I implement method to convert json path to json arrow notation to extract data from json. 

I just need to fix join json path and it will be done.
intech: @OlivierCavadenti I was busy and did not have time to do it yet. The cockroach has a function `json_extract_path (jsonb, string ...) → jsonb`


OlivierCavadenti: > @OlivierCavadenti I was busy and did not have time to do it yet. The cockroach has a function `json_extract_path (jsonb, string ...) → jsonb`

No problem, I am free today I will rework that today.

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..4f6e10bf 100644
--- a/lib/dialects/mssql/schema/mssql-columncompiler.js
+++ b/lib/dialects/mssql/schema/mssql-columncompiler.js
@@ -138,7 +138,8 @@ 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/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/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/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/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/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/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,
 };