I'm trying to transform a table that has a rows with the some attributes that are the same, but others that differ like the table below to a specific JSON structure.
+----+------------+----------+-------+-------+-------+-------+
| id | name | language | attr1 | attr2 | attr3 | attr4 |
+----+------------+----------+-------+-------+-------+-------+
| 13 | Example 1 | en | 0 | 80 | 10 | 100 |
| 13 | Ejemplo 1 | es | 0 | 80 | 10 | 100 |
| 21 | Beispiel 2 | de | 35 | 80 | 20 | 75 |
| 21 | Exemple 2 | fr | 35 | 80 | 20 | 75 |
+----+------------+----------+-------+-------+-------+-------+
As some of the colums have the same value, I want them inside an object, and the columns that have different content as an array, like this:
{
"accounts": [
{
"id": 13,
"attr1": 0,
"attr2": 80,
"attr3": 10,
"attr4": 100,
"names": [
{
"language": "en",
"name": "Example 1"
},
{
"language": "es",
"name": "Ejemplo 1"
}
]
},
{
"id": 21,
"attr1": 35,
"attr2": 80,
"attr3": 20,
"attr4": 75,
"names": [
{
"language": "de",
"name": "Beispiel 2"
},
{
"language": "fr",
"name": "Exemple 2"
}
]
}
]
}
I made a function that generates the accounts array without the names array in every object:
SELECT array_to_json(array_agg(accounts)) AS accounts
FROM (
SELECT DISTINCT id, attr1, attr2, attr3, attr4
FROM temp_table_from_fuction()
ORDER BY attr1, attr2
) accounts
(temp_table_from_fuction()
returns the first table)
This is the result:
[
{
"id": 13,
"attr1": 0,
"attr2": 80,
"attr3": 10,
"attr4": 100
},
{
"id": 21,
"attr1": 35,
"attr2": 80,
"attr3": 20,
"attr4": 75
}
]
How can I append an array to a JSON object with calling again temp_table_from_fuction()
? Is that possible?