How create json format with group-concat mysql?

by

With the newer versions of MySQL, you can use JSON_OBJECT function to achieve the desired result, like so:

GROUP_CONCAT(
  JSON_OBJECT(
    'name', name,
    'phone', phone
  )
) AS list

To get the SQL response ready to be parsed as an array:

CONCAT(
  '[',
  GROUP_CONCAT(
    JSON_OBJECT(
      'name', name,
      'phone', phone
    )
  ),
  ']'
) AS list

This will give you a string like: [{name: 'ABC', phone: '111'}, {name: 'DEF', phone: '222'}] which can be JSON parsed. Hope this helps.

More Related Contents:

  1. Can I concatenate multiple MySQL rows into one field?
  2. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  3. is there a length limit to group_concat or another reason why it would not work on a text field
  4. get first occurrence of last changed value of a column [closed]
  5. MySQL – Rows to Columns
  6. ROW_NUMBER() in MySQL
  7. multiple query same table but in different columns mysql
  8. How can I make SQL case sensitive string comparison on MySQL?
  9. How can I temporarily disable a foreign key constraint in MySQL?
  10. How to do a batch insert in MySQL
  11. Count the occurrences of DISTINCT values
  12. MySQL offset infinite rows
  13. Ordering by the order of values in a SQL IN() clause
  14. Creating a “Numbers Table” in MySQL
  15. SQL Selecting from two Tables with inner join and limit [duplicate]
  16. MySQL Group by SUM
  17. Set NOW() as Default Value for datetime datatype?
  18. In SQL, how to select the top 2 rows for each group
  19. Mysql query to dynamically convert rows to columns on the basis of two columns
  20. SQL DELETE with JOIN another table for WHERE condition
  21. mysql trigger stored trigger is already used by statement which invoked stored trigger
  22. Special Characters in MySQL Table Name
  23. MySQL error 1241: Operand should contain 1 column(s)
  24. How to set a MySQL row to READ-ONLY?
  25. How to create dummy variable columns for thousands of categories in Google BigQuery?
  26. Mysql: Order by like?
  27. Most efficient way to store IP Address in MySQL [duplicate]
  28. Getting first day of the week in MySql using Week No
  29. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  30. mySQL query to search all tables within a database for a string?

Leave a Comment