0

I need to get the values from a table in json format , directly using mysql query (no post processing in php etc at application level). Most of the field of the table are integer and its not hard to build a json string for the records , but one column is a text type and my contain all the garbage .

Is is possible to stringify that text type column data to build a valid json string ?

query:

 select concat('var sp_json= [',
    group_concat(
      '{"sp_no":',sp_no,',
        "sp_group_no":',sp_group_no,',
        "sp_weight":',sp_weight,',
        "dg_list":[',dg_list,'],
        "comments":\'',coalesce(comments,''),'\'}'
    order by sp_group_no
    ),
   ']') as sp_json

  from sp_dg where  pm_id=1 group by pm_id

This outputs a javascript json objects etc 

  var sp_json=[{},,,]

I need way to stringify comments using a built-in mysql function if there is one. Please share your thoughts .

sakhunzai
  • 13,900
  • 23
  • 98
  • 159
  • 1
    There is no JSON stringify function in mySQL as far as I know. – Pekka Sep 13 '13 at 04:39
  • @Pekka웃 thanks, what do you suggest ? – sakhunzai Sep 13 '13 at 04:41
  • Using server side post processing would be the most strightforward way. This *may* be possible in pure MySQL but it's going to be a gigantic pain if you want it able to deal with newlines, all kinds of quotes, etc. as you indicate in the comment below – Pekka Sep 13 '13 at 04:56

1 Answers1

0

Consider using MySQL's String REPLACE function with the concepts found in this SO answer.

E.g. (SQL):

REPLACE(
    REPLACE(
        coalesce(comments,''),'\\','\\\\'
    ),'"','\\"'
)
Community
  • 1
  • 1
zamnuts
  • 9,492
  • 3
  • 39
  • 46
  • thanks, do you think it will handle all the anti-json characters ? new lines, quotes (single , double ) etc .. – sakhunzai Sep 13 '13 at 04:48
  • @sakhunzai those characters you speak of are mentioned in the answer I have linked to (http://stackoverflow.com/questions/4253367/how-to-escape-a-json-string-containing-newline-characters-using-javascript/9204218#9204218). You'll need to nest several `REPLACE` functions to account for all characters (8 in total). – zamnuts Sep 14 '13 at 05:16
  • yes that is reasonable , but I have to write lots of json functions or a bulky large one. Giving you benefit of doubt I ll accept this answer as :) – sakhunzai Sep 14 '13 at 07:20