phpmyadmin Question posted in
The official documentation can be found here.

How to convert value on tables with mysql into json file with trigger? – Phpmyadmin

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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 '';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search