I'd like to convert result table to JSON Array of Object in MySQL using preferably only plain MySQL commands. For example with querySELECT name, phone FROM person; | name | phone | | Jack | 12345 | | John | 23455 | the expected JSON output would be as below as per my requirement: [ { "name": "Jack", "phone": 12345 }, { "name": "John", "phone": 23455 } ] |
The maximum value for group_concat_max_len is 18446744073709551615. To set the variable forever use SET GLOBAL group_concat_max_len=4294967295; |
Example 1:
SELECT i.id, JSON_OBJECT('id', ig.id, "name", ig.name) AS 'group_json' FROM item i LEFT JOIN item_group ig ON i.item_group_id=ig.id ORDER BY i.id DESC LIMIT 10; |
Example 2 (Group Concat):
SELECT ig.item_sales_rate_id, GROUP_CONCAT(DISTICT JSON_OBJECT('id', ig.id, "status", ig.status)) AS 'group_json' FROM item_sales_rate i LEFT JOIN item_sales_uom ig ON i.id=ig.item_sales_rate_id WHERE ig.item_sales_rate_id IS NOT NULL GROUP BY ig.item_sales_rate_id ORDER BY i.id ASC LIMIT 10; |
Friday, October 28, 2022
How to convert result table to JSON Array or JSON Object or JSON String in MySQL
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment