Append JSON array to an object in PostgreSQL

Question

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?

Answer 1

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.