0

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.