Skip to content

Instantly share code, notes, and snippets.

@WunGCQ
Created March 22, 2018 08:10
Show Gist options
  • Save WunGCQ/c4df1929c5975c2a79133bc8300fcd6e to your computer and use it in GitHub Desktop.
Save WunGCQ/c4df1929c5975c2a79133bc8300fcd6e to your computer and use it in GitHub Desktop.
MYSQL: sequelize attribute alias for query JSON field
/*
when querying attributes using alias like:
attributes: [['m','n']] // to show m as n
sequelize will wrap the field word width '`'
the query will looks like `m` AS `n`
this seems simple;
but when query JSON fields:
//my data column: data, value: {name:'wungcq'}
attributes: ["data->'$.name'","name"]
the query will turn to be
`data->'$.name'`
which could not be found as an valid field,
so, you should query like this:
attributes: [[`JSON_EXTRACT(data,"$.name")`,'name']],
*/
const JSON_KEY = (field,key)=>`JSON_EXTRACT(${field},"$.${key}")`;
const res = model.findAll({
attributes: [[JSON_KEY('data','name'),'name']]
})
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment