Append JSON array to an object 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 columns have the same value, I want them inside an object, and the columns that have different content as an array, like this:
{
  &quot;accounts&quot;: [
    {
      &quot;id&quot;: 13,
      &quot;attr1&quot;: 0,
      &quot;attr2&quot;: 80,
      &quot;attr3&quot;: 10,
      &quot;attr4&quot;: 100,
      &quot;names&quot;: [
        {
          &quot;language&quot;: &quot;en&quot;,
          &quot;name&quot;: &quot;Example 1&quot;
        },
        {
          &quot;language&quot;: &quot;es&quot;,
          &quot;name&quot;: &quot;Ejemplo 1&quot;
        }
      ]
    },
    {
      &quot;id&quot;: 21,
      &quot;attr1&quot;: 35,
      &quot;attr2&quot;: 80,
      &quot;attr3&quot;: 20,
      &quot;attr4&quot;: 75,
      &quot;names&quot;: [
        {
          &quot;language&quot;: &quot;de&quot;,
          &quot;name&quot;: &quot;Beispiel 2&quot;
        },
        {
          &quot;language&quot;: &quot;fr&quot;,
          &quot;name&quot;: &quot;Exemple 2&quot;
        }
      ]
    }
  ]
}

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:
[
  {
    &quot;id&quot;: 13,
    &quot;attr1&quot;: 0,
    &quot;attr2&quot;: 80,
    &quot;attr3&quot;: 10,
    &quot;attr4&quot;: 100
  },
  {
    &quot;id&quot;: 21,
    &quot;attr1&quot;: 35,
    &quot;attr2&quot;: 80,
    &quot;attr3&quot;: 20,
    &quot;attr4&quot;: 75
  }
]

How can I append an array to a JSON object calling the temp_table_from_fuction() again? Is that possible?
You didn't put a PostgreSQL version tag, but there are functions added around 9.4 that will help with this: you can use jsonb_object_agg(), jsonb_build_object() and jsonb_build_array() (or their equivalent json_ functions if you don't want jsonb).

See documentation here: https://www.postgresql.org/docs/10/functions-json.html

As a side note, you should normalize that table if it really is a table and not a view.  Specifically, have a look at second normal form.