Creating json dumps of sql records - safer than extracting csv

json_dumps.sql

/* example of clients table */

/* without use of json_object */

SELECT table2.* FROM (      SELECT 'Name', 'added_on', 'phone_no', 'local_id' , 'Email', 'clients_user_id'       UNION ALL     ( select company_name as Name,CONCAT(replace(DATE_FORMAT(added_on,'%Y-%m-%d %T'),' ','T'),"+05:30") as added_on,(select case when ifnull(phone_no,'') <> 0 then phone_no  else '' end) as phone_no,id as local_id,email as Email,user_id as clients_user_id from clients     ) ) table2;
 
set @json_file2 = (
    select group_concat(table1.data1, table1.data2 ) from
    (
      select concat('{"','clients_user_id','"',':','"',user_id,'",') as data1 ,concat('"','Name','"',':','"',company_name,'"}',char(10)) as data2 from clients
    ) table1
    );


set @json_file3 = (
    select group_concat(table1.data1, table1.data2 ) from
    (
      select concat('{"','clients_user_id','"',':','"',user_id,'",') as data1 ,concat('"','Name','"',':','"',company_name,'"}') as data2 from clients
    ) table1
    );
    
    
/* faster version below using group_concat with json_object */
set @json_ob_2 = ( select REGEXP_REPLACE( GROUP_CONCAT(json_object ( 'clients_user_id' , user_id , 'local_id' , id, 'phone_no' , (select case when ifnull(phone_no,'') <> 0 then phone_no  else '' end), 'added_on' , concat(CONCAT(replace(DATE_FORMAT(added_on,'%Y-%m-%d %T'),' ','T'),"+05:30")))), '(})', concat('}','')) from clients );
select concat('{','"','data','"',':','[',@json_ob_2,']','}') into outfile 'clients_sample_2.json';

csv exoprts always lead to error even with flags

• extra columns
• wrong datatype
• missing commas
• open quotes (unclosed)

hence much safer to write a json object to be used as a payload in API