mysql - 是否可以使用mysql脚本获取列表对象的数据？

Question

有一个 json 查询，它为我提供了人和宠物的连接表的 json:

SELECT 
    json_object(
        'personId', p.id,
        'firstPetName', p.firstPetName,
        'pets', json_arrayagg(
                   json_object(
                   'petId', pt.id,
                   'petName', pt.name
                  )
                 )
                )
     FROM person p
     LEFT JOIN pets pt ON p.id = pt.person_id
     GROUP BY p.id;

我想添加一个条件，如果p.firstPetName为null或空字符串，则将第一个宠物的pt.petName放入宠物列表中，是这样的可能吗？

示例:

如果这是结果:

{
  "personId": 1,
  "firstPetName": null // or ""
  "pets": [
    {
      "petName": "walker"
    },
    {
      "petName": "roxi"
    }
  ]
}

我想要得到:

{
  "personId": 1,
  "firstPetName": "walker"
  "pets": [
    {
      "petName": "walker"
    },
    {
      "petName": "roxi"
    }
  ]
}

如果可以做像 pt[0].petName 这样的事情，那就太棒了

Answer 1

来自mysql documentation :

JSON_ARRAYAGG(col_or_expr) [over_clause]

Aggregates a result set as a single JSON array whose elements consist of the rows. The order of elements in this array is undefined.

因此，在您的用例中，列表中宠物的顺序未定义。您无法确定哪只宠物将首先出现在 json_arrayagg 调用生成的 json 数组中。

以下查询给出的答案默认为具有最小名称的 pet。然后使用 COALESCE 函数为 p.firstPetName 提供默认值。

SELECT 
    json_object(
        'personId', p.id,
        'firstPetName', COALESCE(p.firstPetName, MIN(pt.name))
        'pets', json_arrayagg(
            json_object(
                'petId', pt.id,
                'petName', pt.name
            )
        )
    )
FROM 
    person p
    LEFT JOIN pets pt ON p.id = pt.person_id
GROUP BY p.id;