Grouping of result sets, postgres or jsonata?

[flipzz]

Hi,

I have a question on json, jsonata and postgres.

I am fetching result sets from postgresql. The data in Postgres is stored in tables where I do joins (e.g. one table called group, one with people jointed through a table called group_has_people). Usually I expose the joins as pre-defined Postgres-views. Often the joins consists of multiple tables.

What I want to do often is to structure the json so that every "group has their own people" (following the example above). But the problem is that the result-sets and their parsing comes out like "flat" structures with each element containing the group and the person.

So: What is a good pattern to achieve this? Jsonata? Something else? Something in postgres? The data is already "normalized" in postgres so it feels like I am introducing too much functions in Node-red using jsonata to do the groupings.

I have this in postgres:

I get this resultset in Node-RED using a ordinary select statement:

{
  "_msgid": "392f1e7854aeb82b",
  "payload": [
    {
      "groupname": "Testgroup A",
      "personname": "Person 1"
    },
    {
      "groupname": "Testgroup A",
      "personname": "Person 2"
    },
    {
      "groupname": "Testgroup B",
      "personname": "Person 3"
    },
    {
      "groupname": "Testgroup B",
      "personname": "Person 4"
    }
  ],
  "topic": ""
}

But I want something like this:

{
  "_msgid": "392f1e7854aeb82b",
  "payload": [
    {
      "groupname": "Testgroup A",
      "People": {
        "personname": "Person 1",
        "personname": "Person 2"
      }
    },
    {
      "groupname": "Testgroup B",
      "People": {
        "personname": "Person 3",
        "personname": "Person 4"
      }
    }
  ],
  "topic": ""
}