Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Shell script to create a nested JSON with character sets and collations
// MySQL Shell script to create a nested JSON with character sets and collations
session.setCurrentSchema('information_schema');
var query = 'SELECT cs.CHARACTER_SET_NAME, cs.DESCRIPTION, cs.DEFAULT_COLLATE_NAME, co.COLLATION_NAME, co.IS_DEFAULT FROM CHARACTER_SETS as cs, COLLATIONS as co WHERE cs.CHARACTER_SET_NAME = co.CHARACTER_SET_NAME ORDER BY cs.CHARACTER_SET_NAME, co.IS_DEFAULT DESC, co.COLLATION_NAME';
var res = session.sql(query).execute();
var myJson = {['character_sets']:[]};
var row = res.fetchOne();
while(row) {
var character_set = {'name': {}, 'description': {}, 'default_collation': {}, 'collations': []};
character_set.name = row[0];
character_set.description = row[1];
character_set.default_collation = row[2];
var collations = [];
while(row){
if (row[0] == character_set.name){
collations.push(row[3]);
} else {
break;
}
row = res.fetchOne();
}
character_set.collations = collations;
myJson.character_sets.push(character_set);
}
print(JSON.stringify(myJson, null, 2));
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment