Access JSONB property: sequelize.json() not working for included models? #7956
Description
I want to get a single property from a JSONB attribute of an associated model (joined via include)
I tried the following, but it did not work:
Order.findAll({
attributes: ['id'],
include: [{ model: Product, attributes: ['id',[sequelize.json('name.fr'), 'name']]}]})the query produces this error:
error: column Product.(name#>>'{fr}') does not exist
The generated SQL looks like this (postgres):
SELECT "Order"."id",
"Product"."id" AS "Product.id",
"Product"."(name#>>\'{fr}\')" AS "Product.name"
FROM "Order" AS "Order"
LEFT OUTER JOIN "Product" AS "Product" ON "Order"."productId" = "Product"."id"Seems like this part is not correct: "(name#>>\'{fr}\')"
Instead it should be: "name"#>>'{fr}'
The complete, correct SQL would be:
SELECT "Order"."id",
"Product"."id" AS "Product.id",
"Product"."name"#>>'{fr}' AS "Product.name"
FROM "Order" AS "Order"
LEFT OUTER JOIN "Product" AS "Product" ON "Order"."productId" = "Product"."id"When I query Product directly (not via association include), sequelize.json() works fine:
Product.findAll({
attributes: ['id', [sequelize.json('name.fr'), 'name']]
})The correctly generated SQL is:
SELECT "id", ("name"#>>'{fr}') AS "name" FROM "Product" AS "Product" Obviously the problem only occurs when joining associated models via include.
I know that I can use literal SQL and the following query works with 'include':
Order.findAll({
attributes: ['id'],
include: [{
model: Product,
attributes: ['id',[sequelize.literal(`"Product"."name"#>>'{fr}'`), "name"]]
}]
})I just wonder why the "official" way with sequelize.json() fails for 'include. I think it should work for included models, too.
for completeness, here is the full example with model definitions and so on:
//create models
let Product = sequelize.define( "Product",
{ id: { type: Sequelize.STRING(20), primaryKey: true},
name: { type: Sequelize.JSONB}},
{ timestamps: false, freezeTableName: true });
let Order = sequelize.define( "Order",
{ id: { type: Sequelize.STRING(20), primaryKey: true} },
{ timestamps: false, freezeTableName: true });
Order.belongsTo(Product,{foreignKey:'productId'});
//create some data
Product.create( { id: 'PROD001', name: {en: 'dog', de:'Hund', 'fr': 'chien'}})
.then(()=>Order.create({id: 'ORDER001',productId: 'PROD001'}))
//this works:
.then(()=> Product.findAll( { attributes: [ 'id', [sequelize.json('name.fr'), 'name']]}))
//this works, too:
.then(()=> Order.findAll({
attributes: ['id'],
include: [{
model: Product,
attributes: ['id',[sequelize.literal(`"Product"."name"#>>'{fr}'`), "name"]]
}]
}))
//this fails:
.then(()=> Order.findAll({
attributes: ['id'],
include: [{
model: Product,
attributes: ['id',[sequelize.json('name.fr'), 'name']]
}]
}))DB: postgres 9.6
Sequelize version: 4.3.2