Querying JSON

	PostgreSQL	MariaDB	MySQL	MSSQL	SQLite	Snowflake	db2	ibmi
Regular Queries	[docs]	[docs]	[docs]
Unquoted Queries				[docs]

Basics

JSON columns are a great way to opt-in to a document approach to data storage while preserving the advantages of relational databases.

Sequelize has first class support for querying JSON columns, including support for accessing nested properties. This is done thanks to the . syntax supported by attributes:

User.findAll({
  where: {
    'jsonAttribute.address.country': 'Belgium',
  },
});

-- postgres
"jsonAttribute"#>ARRAY['address','country'] = '"Belgium"';

-- mysql & friends
JSON_EXTRACT(`jsonAttribute`, '$.address.country') = '"Belgium"';

Nested properties are JSON too

This syntax to access nested properties produces JSON. In the example above, you can see that the right-hand side of the comparison has been stringified to JSON by Sequelize.

This means that JSON operators, such as Op.anyKeyExists can be used on nested properties:

User.findAll({
  where: {
    'jsonAttribute.address': { [Op.anyKeyExists]: ['country', 'street'] },
  },
});

-- anyKeyExists is only available in postgres
"jsonAttribute"->'address' ?| ARRAY['country','street'];

However it also means that you can't use operators such as Op.like on nested properties without either casting the value to a string, or unquoting it first.

In PostgreSQL, only JSONB values can be compared to other JSONB values, not JSON. Be mindful of this when choosing between JSON and JSONB for your columns.

Array Index Access

Most dialects use a different syntax for array index access than for object property access. In order to differentiate between the two, we use the [] syntax for array index access, and . for object property access:

User.findAll({
  where: {
    'gameData.passwords[0]': 0451,
  },
});

produces

-- postgres
-- while it may not look like it, it's still important to use the [] syntax
-- when accessing array indexes in postgres,
-- as Sequelize can sometimes use the "->" operator instead of "#>" for performance,
-- which uses a different syntax for indexes & keys
"gameData"#>ARRAY['passwords','0'] = '0451';

-- mysql & friends
JSON_EXTRACT(`gameData`, '$.passwords[0]') = '0451';

Comparing JSON values

When comparing JSON values, Sequelize automatically stringifies the value to JSON:

User.findAll({
  where: {
    'jsonAttribute.address.street': 'Belgium',
  },
});

Produces

-- postgres. Note that this only works with JSONB as `JSON = string` does not exist in postgres
"jsonAttribute"#>ARRAY['address','street'] = '"Belgium"';

-- mysql & friends
JSON_EXTRACT(`jsonAttribute`, '$.address.street') = '"Belgium"';

Casting JSON values

To compare the value, you can use the :: cast syntax to cast your value to the desired type:

User.findAll({
  where: {
    // The exact cast type depends on the dialect
    // For instance, in postgres you would use `::integer`,
    // While in mysql you would use `::signed` or `::unsigned`
    'jsonAttribute.age::integer': { [Op.gt]: 18 },
  },
});

-- postgres
-- ℹ️ in postgres, you can only cast JSONB columns, not JSON.
-- to compare JSON columns, you can either unquote first (see below)
-- or cast to text then the desired type (::text::integer)
CAST("jsonAttribute"->'age' AS integer) > 18;

-- mysql & friends
CAST(`jsonAttribute`->"$.age" AS signed) > 18;

Unquoting JSON

Unquoting JSON values is a way to access the contents of a JSON string. This can be more useful than casting to text, as casting to text will preserve the JSON quotes (e.g. "abc"), while unquoting will remove them (e.g. abc).

You can unquote by using the :unquote modifier. Note the single : instead of the double :: used for casting.

User.findAll({
  where: {
    'jsonAddress.country:unquote': 'Belgium',
  },
});

-- postgres (the ->> operator extracts & unquotes)
"jsonAddress"->>'country' = 'Belgium';

-- mysql & friends
JSON_UNQUOTE(JSON_EXTRACT(`jsonAddress`, '$.country')) = 'Belgium';

You can of course also use the :: cast syntax to cast the unquoted value to the desired type.

caution

You should only use this if the value can only be a string.

Using :unquote on a mixed-type property can produce unexpected results, as unquoting something that is not a string will not do anything, making it impossible to distinguish between a JSON string "null" and a JSON null, as they will both return null.

Nested extraction syntax

You can also nest JSON objects as an alternative to the . syntax.

For instance, the two following queries are equivalent:

User.findAll({
  where: {
    jsonAttribute: {
      address: {
        country: 'Belgium',
        street: 'Rue de la Loi',
      },
      'age::integer': { [Op.gt]: 18 },
    },
  },
});

User.findAll({
  where: {
    'jsonAttribute.address.country': 'Belgium',
    'jsonAttribute.address.street': 'Rue de la Loi',
    'jsonAttribute.age::integer': { [Op.gt]: 18 },
  },
});

ℹ️ Nesting a JSON object uses the . operator by default, but you can use the [index] operator at the start of the path to change the default operator:

User.findAll({
  where: {
    jsonAttribute: {
      age: 18,
      '[0]': '1'
    },
  },
});

produces

-- postgres
"jsonAttribute"->'age' = 18 AND "jsonAttribute"->0 = '"1"';

-- mysql & friends
JSON_EXTRACT(`jsonAttribute`, '$.age') = 18 AND JSON_EXTRACT(`jsonAttribute`, '$[0]') = '"1"';

Path Segment Escaping

If one of your properties contains characters that Sequelize gives special meaning to, such as ., ::, : and [], you can escape by surrounding that path segment with double quotes:

User.findAll({
  where: {
    // This will access the property named `address.country` on `jsonAttribute`,
    // instead of accessing the `country` property on the `address` object
    'jsonAttribute."address.country"': 'Belgium',
  },
});

-- postgres
"jsonAttribute"->'address.country' = '"Belgium"';

-- mysql & friends
JSON_EXTRACT(`jsonAttribute`, '$."address.country"') = '"Belgium"';

Double quotes themselves can be escaped by using backslashes:

User.findAll({
  where: {
    // This will access the property named `address."country` on `jsonAttribute`,
    'jsonAttribute.address."\\"country"': 'Belgium',
  },
});

JSON null vs SQL NULL

When using JSON values in SQL, you should be aware of the difference between JSON null and SQL NULL, as they are distinct values.

• You can view the JSON null value as a string that contains the word null.
• While the SQL NULL value is a special value that represents the absence of a value.

When inserting null into a JSON column, Sequelize will stringify it as JSON:

User.create({
  jsonAttribute: null,
});

INSERT INTO "Users" ("jsonAttribute") VALUES ('null');

If you wish to also use the SQL NULL value, you can use raw SQL to insert it:

import { sql } from '@sequelize/core';

User.create({
  jsonAttribute: sql`NULL`,
});

INSERT INTO "Users" ("jsonAttribute") VALUES (NULL);

When extracting a JSON value, if the value exists and is the JSON null, the JSON null will be returned. If the value does not exist, the SQL NULL will be returned.

However, when inserting or comparing the JavaScript null value against a JSON column, Sequelize will stringify it as JSON.

This means that the following query will check that the name property exists and is set to the JSON null value:

User.findAll({
  where: {
    'jsonAttribute.name': null,
  },
});

-- postgres
"jsonAttribute"->'name' = 'null';

-- mysql & friends
JSON_EXTRACT(`jsonAttribute`, '$.name') = 'null';

If you want to check that something is the SQL NULL, you have to use the IS NULL operator instead of the = operator:

User.findAll({
  where: {
    'jsonAttribute.name': { [Op.is]: null },
  },
});

-- postgres
"jsonAttribute"->'name' IS NULL;

-- mysql & friends
JSON_EXTRACT(`jsonAttribute`, '$.name') IS NULL;

If you want to check that something is either the JSON null, or the SQL NULL, you must use both operators:

User.findAll({
  where: or(
    { 'jsonAttribute.name': { [Op.eq]: null } },
    { 'jsonAttribute.name': { [Op.is]: null } },
  ),
});

-- postgres
   "jsonAttribute"->'name' = 'null'
OR "jsonAttribute"->'name' IS NULL;

-- mysql & friends
   JSON_EXTRACT(`jsonAttribute`, '$.name') = 'null'
OR JSON_EXTRACT(`jsonAttribute`, '$.name') IS NULL;