Postgresql – Append JSON array to an object

functionsjsonpostgresql

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:

{
  "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 calling the temp_table_from_fuction() again? Is that possible?

Best Answer

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.

Related Question