【MySQL】select -> Json

日常工作需求，有h5_apps和h5_company两张表

create table h5_apps(
    uuid text,
    company_id text,
    name text
);

create table h5_company(
    uuid text,
    name text
);

希望通过select语句直接输出以下格式

    {
        "companyName": "A",
        "companyUuid": "B",
        "apps": [
          {
            "name": "C",
            "uuid": "D",
            "company_id": "B"
          },
          {
            "name": "CC",
            "uuid": "DD",
            "company_id": "B"
          }
        ]
      },
    {
        "companyName": "a",
        "companyUuid": "b",
        "apps": [
          "name": "c",
          "uuid": "d",
          "company_id": "b"
        ]
      }

解题

（1）函数JSON_OBJECT():将一个键值对列表转换成json对象
（2）GROUP_CONCAT(xxx)：是将分组中括号里对应的字符串进行连接.如果分组中括号里的参数xxx有多行，那么就会将这多行的字符串连接，每个字符串之间会有特定的符号进行分隔。

select json_object('companyName', companyName, 'companyUuid', companyUuid, 'apps', apps)
from(
SELECT distinct company.name as companyName,
       company.uuid as companyUuid,
       group_concat(json_object('uuid', app.uuid, 'name', app.name, 'company_id', app.company_id)) apps
FROM h5_apps app
    INNER JOIN h5_company company
        ON app.company_id = company.uuid
group by company.name, company.uuid)t;