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"';
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.
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 wordnull
. - 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;