ephys/new-json.md

## new-json.md

      
    Raw
  

              new-json.md
            
          
    New JSON support in Sequelize

Sequelize supports a JSON extraction notation similar to the JS . operator:
User.findAll({
  where: {
    'jsonAttribute.address.country': 'Belgium',
  },
});
In Sequelize 6 and prior, the above produced the following where:
-- postgres
"jsonAttribute"#>>ARRAY['address','country'] = 'Belgium';

-- mysql & friends
`jsonAttribute`->>"$.address.country" = 'Belgium';
The ->> and #>> operators return the JSON value as unquoted text. A JSON string "belgium" is returned as belgium.
This is convenient for comparing string values but a problem with other types (sequelize/sequelize#15238)
We also cast values automatically based on the right-hand side:
User.findAll({
  where: {
    'jsonAttribute.birthday': new Date(),
  },
});
produced
-- postgres
CAST("jsonAttribute"->>'birthday' AS timestamptz) = '2022-01-01 00:00:00 +00:00';

-- mysql & friends
CAST(`jsonAttribute`->>"$.birthday" AS timestamp) = '2022-01-01 00:00:00';
The new system

In Sequelize 7, we use the -> qnd #> operators by default instead.
These operators do not unquote, they return a JSON value ("belgium", with the quotes, it's a JSON string).
We also don't cast automatically. Nested json properties are treated the same way as the json attribute itself.
This is really good for jsonb columns, as it makes it possible to use jsonb operators on nested attributes:
User.findAll({
  where: {
    'jsonAttribute.address': { [Op.anyKeyExists]: ['country', 'street'] },
  },
});
produces
-- postgres, jsonb does not exist in other dialects
"jsonAttribute"->'address' ?| ARRAY['country','street'];
Array Index Access

Most dialects use a different syntax for array index access vs 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: {
    'jsonAttribute.passwords[0]': 0451,
  },
});
produces
-- postgres
"jsonAttribute"#>ARRAY['passwords','0'] = '0451';

-- mysql & friends
`jsonAttribute`->"$.passwords[0]" = '0451';
Comparing JSON values

Simple equality comparison are still possible, as we stringify the value as 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
`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: {
    'jsonAttribute.age::integer': { [Op.gt]: 18 },
  },
});
Produces
-- 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 integer) > 18;
Unquoting JSON

You can still unquote like before by using the :unquote syntax. It looks like the cast operator (::), but it's not a cast, it's just a way to unquote the value.
User.findAll({
  where: {
    'jsonAddress.country:unquote': 'Belgium',
    'jsonPasswords[0]:unquote': '0451',
    'jsonString:unquote': 'abc',
  },
});
produces
-- postgres
"jsonAddress"->>'country' = 'Belgium';
"jsonPasswords"->>0 = '0451';
    
-- postgres does not have a JSON_UNQUOTE function, we use #>> which extracts & unquotes, 
-- but with an empty extraction path to get the entire value
"jsonString"#>>ARRAY[]::TEXT[] = 'abc';

-- mysql & friends
`jsonAddress`->>"$.country" = 'Belgium';
`jsonPasswords`->>"$[0]" = '0451';

-- mysql & friends do not have a JSON_UNQUOTE function
json_unquote(`jsonString`) = 'abc';
⚠️ You should only use this if the value is only ever a string, otherwise you will get unexpected results (sequelize/sequelize#15238).
You can of course also use the :: cast syntax to cast the unquoted value to the desired type.
Alternative 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 ->> or [] operators at the start of the path to change the default operator:
User.findAll({
  where: {
    jsonAttribute: {
      age: 18,
      '->>country': 'Belgium',
      '[0]': '1'
    },
  },
});
produces
-- postgres
"jsonAttribute"#>>ARRAY['address','country'] = 'Belgium';

-- mysql & friends
`jsonAttribute`->>"$.address.country" = 'Belgium';
❓️ Future addition: JSONB predicates

If you don't use the unquote path syntax, using an operator on a JSONB column will use the JSON path predicate operator to compare your value to the JSONB value:
User.findAll({
  where: {
    jsonAttribute: { 
      [Op.matches]: or({
        age: { [Op.gt]: 18 },
        name: { [Op.regex]: '^ab.*c' },
      }),
    }
  }
});
produces
-- postgres
-- JSONB is only supported in postgres
"jsonAttribute" @@ '$.age > 18 or $.name like_regex "^ab.*c"';
❓️ Future addition: wildcard operator

We could also add a wildcard operator to match any key in a JSON object:
User.findAll({
  where: {
    'jsonAttribute.*.country': 'Belgium',
  },
});
produces
-- postgres
jsonb_path_query("jsonAttribute", '$[*].country') = 'Belgium';

-- mysql & friends
`jsonAttribute`->"$[*].country" = 'Belgium';
This would also work with the ->> operator:
User.findAll({
  where: {
    'jsonAttribute.*->>country': 'Belgium',
  },
});
produces
-- postgres
jsonb_path_query("jsonAttribute", '$[*].country')#>>'{}' = 'Belgium';

-- mysql & friends
`jsonAttribute`->>"$[*].country" = 'Belgium';
❓️ Would this be worth it?

We'd have to choose which function to use (jsonb_path_query, jsonb_path_query_array, or jsonb_path_query_first).
It may discourage users from using the right one because it's not the default one.