1

I have a MySQL 5.7 database table that stores a piece of information as JSON. The JSON structure for these records is something like this, stored in a column called, for instance my_json:

{
  "someString": "string",
  "someArray:": ["array", "of", "stuff"]
}

I can select the individual parts of this easily with a query like this:

SELECT mt.some_field AS some_field,
       JSON_EXTRACT(mt.my_json, "$.someString") AS some_string,
       JSON_EXTRACT(mt.my_json, "$.someArray") AS some_array
  FROM my_table AS mt

and I get a nice response with my JSON object split up into two separate columns.

What I would like to do though, is convert the JSON_EXTRACTed someArray property into a comma separated string. So rather than a column called some_array with a value like ["array", "of", "stuff"], I want to have a column called some_array with a value like array, of, stuff.

Can anyone help me figure out the right SQL to do this? (Again, using MySQL 5.7)

Thanks!!

C. Norwood
  • 109
  • 5
  • 1
    Convert JSON to string and remove doublequotes and square brackets. – Akina Jan 23 '20 at 19:44
  • 1
    I linked this to a duplicate question, but honestly I would just return the JSON array to your app and decode the JSON there. It's bound to be easier. – Bill Karwin Jan 23 '20 at 19:46

0 Answers0