MYSQL如何从两列(如键/值对)生成组结果

Question

我必须在同一列中返回与其父项(field_name(相关的所有值。目前我有以下查询：

SELECT dicval.id, dic.field_name, dic.description AS field_description, 
dicval.value,dicval.description FROM adenvie_data_cloud.dictionary_values dicval
INNER JOIN dictionary dic ON dicval.field_name_id = dic.id
ORDER BY dicval.id;

返回如下：

{
"id": "1",
"field_name": "address_type",
"field_description": "Address Type Indicator",
"value": "F",
"description": "Company"
},
{
"id": "2",
"field_name": "address_type",
"field_description": "Address Type Indicator",
"value": "G",
"description": "General Delivery"
},
{
"id": "3",
"field_name": "address_type",
"field_description": "Address Type Indicator",
"value": "H",
"description": "Highway"
},
{
"id": "4",
"field_name": "address_type",
"field_description": "Address Type Indicator",
"value": "P",
"description": "PO Box"
},
...

在SQL语句中，是否可以通过field_name对结果进行分组，以返回类似的内容？：

{
"id": "3",
"name": "addressType",
"description": "Address Type Indicator",
"values": {
"F": "Company",
"G": "General Delivery" ,
"H": "Highway",
"P": "PO Box",                                    
"R": "Rural Route ",
"S": "Street"  
}

PD:

我在SQL语句末尾尝试过GROUP BY dicval.id, dic.field_name，但没有成功。

Answer 1

最终解决方案

为了获得作为键/值对的分组值，最准确的查询如下：

SELECT d.*,
(SELECT CAST(
CONCAT('[',
GROUP_CONCAT(
JSON_OBJECT(
dv2.value ,dv2.description 
)
),
']')
AS JSON) FROM (SELECT * FROM dictionary_values dv WHERE dv.field_name_id = d.id) AS dv2) 
AS d_value
FROM dictionary d; 
END

最后显示分组数据：

{
"id": "1",
"field_name": "address_type",
"description": "Address Type Indicator",
"category": "base",
"d_value": "[{"F": "Company"}, {"G": "General Delivery"}, {"H": "Highway"}, {"P": "PO Box"}, {"R": "Rural Route"}, {"S": "Street"}]"
}

只剩下用str_replace解析d_value字符串删除\并用str_split(PHP(转换成数组