Mixing
How to convert value on tables with mysql into json file with trigger?
I use phpmyadmin for handle the mysql database.
I have got a table that call NodeJS and the column of this table calls id and number.
I tried to create a JSON file with this trigger on mysql:
SELECT id, number FROM NodeJS
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
but the result of prova.json file is this:
"1","12"
"3","13.4"
"4","13.4"
"5","13.9"
"6","13.9"
"7","12.9"
"8","12.9"
"9","123"
"10","123"
"11","12345"
"12","12345"
"13","3"
"14","3"
"15","126"
"16","126"
"17","111"
"18","123"
"19","123"
"20","1"
"21","1"
"22","13.4"
"25","11"
"26","11"
"27","12342300"
So I would like to understand how to convert it decently into a real json file taking into account that the fields of the table, as I said before, are id and number.
I thank everyone for the attention.
mysql json triggers
asked Jan 2 at 22:07
fabios95fabios95
54
add a comment |
I use phpmyadmin for handle the mysql database.
I have got a table that call NodeJS and the column of this table calls id and number.
I tried to create a JSON file with this trigger on mysql:
SELECT id, number FROM NodeJS
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
but the result of prova.json file is this:
"1","12"
"3","13.4"
"4","13.4"
"5","13.9"
"6","13.9"
"7","12.9"
"8","12.9"
"9","123"
"10","123"
"11","12345"
"12","12345"
"13","3"
"14","3"
"15","126"
"16","126"
"17","111"
"18","123"
"19","123"
"20","1"
"21","1"
"22","13.4"
"25","11"
"26","11"
"27","12342300"
So I would like to understand how to convert it decently into a real json file taking into account that the fields of the table, as I said before, are id and number.
I thank everyone for the attention.
mysql json triggers
asked Jan 2 at 22:07
fabios95fabios95
54
Note that you won't be able over write that file more than once. SELECT INTO OUTFILE won't overwrite a file. "file_namecannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed." I suggest you need to do this data export in your app, not in a trigger.
– Bill Karwin
Jan 2 at 23:42
Thank you. Now instead of using the trigger, I use a stored procedure that is launched by my web application.
– fabios95
Jan 6 at 19:41
add a comment |
I use phpmyadmin for handle the mysql database.
I have got a table that call NodeJS and the column of this table calls id and number.
I tried to create a JSON file with this trigger on mysql:
SELECT id, number FROM NodeJS
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
but the result of prova.json file is this:
"1","12"
"3","13.4"
"4","13.4"
"5","13.9"
"6","13.9"
"7","12.9"
"8","12.9"
"9","123"
"10","123"
"11","12345"
"12","12345"
"13","3"
"14","3"
"15","126"
"16","126"
"17","111"
"18","123"
"19","123"
"20","1"
"21","1"
"22","13.4"
"25","11"
"26","11"
"27","12342300"
So I would like to understand how to convert it decently into a real json file taking into account that the fields of the table, as I said before, are id and number.
I thank everyone for the attention.
mysql json triggers
asked Jan 2 at 22:07
fabios95fabios95
54
I use phpmyadmin for handle the mysql database.
I have got a table that call NodeJS and the column of this table calls id and number.
I tried to create a JSON file with this trigger on mysql:
SELECT id, number FROM NodeJS
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'
but the result of prova.json file is this:
"1","12"
"3","13.4"
"4","13.4"
"5","13.9"
"6","13.9"
"7","12.9"
"8","12.9"
"9","123"
"10","123"
"11","12345"
"12","12345"
"13","3"
"14","3"
"15","126"
"16","126"
"17","111"
"18","123"
"19","123"
"20","1"
"21","1"
"22","13.4"
"25","11"
"26","11"
"27","12342300"
So I would like to understand how to convert it decently into a real json file taking into account that the fields of the table, as I said before, are id and number.
I thank everyone for the attention.
mysql json triggers
mysql json triggers
asked Jan 2 at 22:07
fabios95fabios95
54
asked Jan 2 at 22:07
fabios95fabios95
54
asked Jan 2 at 22:07
fabios95fabios95
54
asked Jan 2 at 22:07
fabios95fabios95
54
asked Jan 2 at 22:07
fabios95fabios95
54
54
Note that you won't be able over write that file more than once. SELECT INTO OUTFILE won't overwrite a file. "file_namecannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed." I suggest you need to do this data export in your app, not in a trigger.
– Bill Karwin
Jan 2 at 23:42
Thank you. Now instead of using the trigger, I use a stored procedure that is launched by my web application.
– fabios95
Jan 6 at 19:41
add a comment |
Note that you won't be able over write that file more than once. SELECT INTO OUTFILE won't overwrite a file. "file_namecannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed." I suggest you need to do this data export in your app, not in a trigger.
– Bill Karwin
Jan 2 at 23:42
Thank you. Now instead of using the trigger, I use a stored procedure that is launched by my web application.
– fabios95
Jan 6 at 19:41
Note that you won't be able over write that file more than once. SELECT INTO OUTFILE won't overwrite a file. "
file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed." I suggest you need to do this data export in your app, not in a trigger.– Bill Karwin
Jan 2 at 23:42
Note that you won't be able over write that file more than once. SELECT INTO OUTFILE won't overwrite a file. "
file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed." I suggest you need to do this data export in your app, not in a trigger.– Bill Karwin
Jan 2 at 23:42
Thank you. Now instead of using the trigger, I use a stored procedure that is launched by my web application.
– fabios95
Jan 6 at 19:41
Thank you. Now instead of using the trigger, I use a stored procedure that is launched by my web application.
– fabios95
Jan 6 at 19:41
add a comment |
2 Answers
2
active
oldest
votes
This solves your problem, using MySql CONACT, GROUP_CONCAT, and OUTFILE export method.
SELECT GROUP_CONCAT(CONCAT('{ "id": ' ,id, ', "number": "', number, '" } r')) FROM nodejs
into @result;
SET @q = CONCAT('[ ', @result, ' ]');
SELECT @q INTO OUTFILE 'e:/json_data.json';
you can follow the same approch, to add remove or modify whatever fields you have and the output is perfectly valid .json file.
** Note: you may have permissions issues if you try to write your C: dirve, thats why i tried writing to my E: drive.
answered Jan 2 at 23:07
TarreqTarreq
603312
Thank you very much!
– fabios95
Jan 4 at 21:24
add a comment |
Hmm, that isn't meant to create JSON files. But maybe following trick works. Use json_arrayagg() and json_object() to create your JSON. Then export it into the file, setting all the delimiters to empty strings (or white spaces, that JSON ignores).
SELECT json_arrayagg(json_object('id', id, 'number', number))
FROM nodejs
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ''
ENCLOSED BY ''
LINES TERMINATED BY '';
answered Jan 2 at 23:11
sticky bitsticky bit
15.7k111733
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013809%2fhow-to-convert-value-on-tables-with-mysql-into-json-file-with-trigger%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
This solves your problem, using MySql CONACT, GROUP_CONCAT, and OUTFILE export method.
SELECT GROUP_CONCAT(CONCAT('{ "id": ' ,id, ', "number": "', number, '" } r')) FROM nodejs
into @result;
SET @q = CONCAT('[ ', @result, ' ]');
SELECT @q INTO OUTFILE 'e:/json_data.json';
you can follow the same approch, to add remove or modify whatever fields you have and the output is perfectly valid .json file.
** Note: you may have permissions issues if you try to write your C: dirve, thats why i tried writing to my E: drive.
answered Jan 2 at 23:07
TarreqTarreq
603312
Thank you very much!
– fabios95
Jan 4 at 21:24
add a comment |
This solves your problem, using MySql CONACT, GROUP_CONCAT, and OUTFILE export method.
SELECT GROUP_CONCAT(CONCAT('{ "id": ' ,id, ', "number": "', number, '" } r')) FROM nodejs
into @result;
SET @q = CONCAT('[ ', @result, ' ]');
SELECT @q INTO OUTFILE 'e:/json_data.json';
you can follow the same approch, to add remove or modify whatever fields you have and the output is perfectly valid .json file.
** Note: you may have permissions issues if you try to write your C: dirve, thats why i tried writing to my E: drive.
answered Jan 2 at 23:07
TarreqTarreq
603312
Thank you very much!
– fabios95
Jan 4 at 21:24
add a comment |
This solves your problem, using MySql CONACT, GROUP_CONCAT, and OUTFILE export method.
SELECT GROUP_CONCAT(CONCAT('{ "id": ' ,id, ', "number": "', number, '" } r')) FROM nodejs
into @result;
SET @q = CONCAT('[ ', @result, ' ]');
SELECT @q INTO OUTFILE 'e:/json_data.json';
you can follow the same approch, to add remove or modify whatever fields you have and the output is perfectly valid .json file.
** Note: you may have permissions issues if you try to write your C: dirve, thats why i tried writing to my E: drive.
answered Jan 2 at 23:07
TarreqTarreq
603312
This solves your problem, using MySql CONACT, GROUP_CONCAT, and OUTFILE export method.
SELECT GROUP_CONCAT(CONCAT('{ "id": ' ,id, ', "number": "', number, '" } r')) FROM nodejs
into @result;
SET @q = CONCAT('[ ', @result, ' ]');
SELECT @q INTO OUTFILE 'e:/json_data.json';
you can follow the same approch, to add remove or modify whatever fields you have and the output is perfectly valid .json file.
** Note: you may have permissions issues if you try to write your C: dirve, thats why i tried writing to my E: drive.
answered Jan 2 at 23:07
TarreqTarreq
603312
answered Jan 2 at 23:07
TarreqTarreq
603312
answered Jan 2 at 23:07
TarreqTarreq
603312
answered Jan 2 at 23:07
TarreqTarreq
603312
603312
Thank you very much!
– fabios95
Jan 4 at 21:24
add a comment |
Thank you very much!
– fabios95
Jan 4 at 21:24
Thank you very much!
– fabios95
Jan 4 at 21:24
Thank you very much!
– fabios95
Jan 4 at 21:24
add a comment |
Hmm, that isn't meant to create JSON files. But maybe following trick works. Use json_arrayagg() and json_object() to create your JSON. Then export it into the file, setting all the delimiters to empty strings (or white spaces, that JSON ignores).
SELECT json_arrayagg(json_object('id', id, 'number', number))
FROM nodejs
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ''
ENCLOSED BY ''
LINES TERMINATED BY '';
answered Jan 2 at 23:11
sticky bitsticky bit
15.7k111733
add a comment |
Hmm, that isn't meant to create JSON files. But maybe following trick works. Use json_arrayagg() and json_object() to create your JSON. Then export it into the file, setting all the delimiters to empty strings (or white spaces, that JSON ignores).
SELECT json_arrayagg(json_object('id', id, 'number', number))
FROM nodejs
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ''
ENCLOSED BY ''
LINES TERMINATED BY '';
answered Jan 2 at 23:11
sticky bitsticky bit
15.7k111733
add a comment |
Hmm, that isn't meant to create JSON files. But maybe following trick works. Use json_arrayagg() and json_object() to create your JSON. Then export it into the file, setting all the delimiters to empty strings (or white spaces, that JSON ignores).
SELECT json_arrayagg(json_object('id', id, 'number', number))
FROM nodejs
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ''
ENCLOSED BY ''
LINES TERMINATED BY '';
answered Jan 2 at 23:11
sticky bitsticky bit
15.7k111733
Hmm, that isn't meant to create JSON files. But maybe following trick works. Use json_arrayagg() and json_object() to create your JSON. Then export it into the file, setting all the delimiters to empty strings (or white spaces, that JSON ignores).
SELECT json_arrayagg(json_object('id', id, 'number', number))
FROM nodejs
INTO OUTFILE '/opt/brackets/www/node_modules/server/prova.json'
FIELDS TERMINATED BY ''
ENCLOSED BY ''
LINES TERMINATED BY '';
answered Jan 2 at 23:11
sticky bitsticky bit
15.7k111733
answered Jan 2 at 23:11
sticky bitsticky bit
15.7k111733
answered Jan 2 at 23:11
sticky bitsticky bit
15.7k111733
answered Jan 2 at 23:11
sticky bitsticky bit
15.7k111733
15.7k111733
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013809%2fhow-to-convert-value-on-tables-with-mysql-into-json-file-with-trigger%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Note that you won't be able over write that file more than once. SELECT INTO OUTFILE won't overwrite a file. "
file_namecannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed." I suggest you need to do this data export in your app, not in a trigger.– Bill Karwin
Jan 2 at 23:42
Thank you. Now instead of using the trigger, I use a stored procedure that is launched by my web application.
– fabios95
Jan 6 at 19:41