# Grouping of result sets, postgres or jsonata?

**URL:** <https://discourse.nodered.org/t/grouping-of-result-sets-postgres-or-jsonata/70115>\
**Category:** General\
**Tags:** database, function-node\
**Created:** [5 November 2022 08:55 UTC](https://discourse.nodered.org/t/grouping-of-result-sets-postgres-or-jsonata/70115 "2022-11-05T08:55:06Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![flipzz](https://avatars.discourse-cdn.com/v4/letter/f/bb73d2/32.png) [@flipzz](https://discourse.nodered.org/u/flipzz)\
**Post date:** [5 November 2022 08:55 UTC](https://discourse.nodered.org/t/grouping-of-result-sets-postgres-or-jsonata/70115/1 "2022-11-05T08:55:06Z")

</div>

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:**

 ![Screenshot 2022-11-05 at 09.42.20](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/9/99f30d73bf67d93ada7a4fdd35f0d7cbb7047072.png)

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

```auto
{
  "_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:**

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

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [4 January 2023 08:55 UTC](https://discourse.nodered.org/t/grouping-of-result-sets-postgres-or-jsonata/70115/2 "2023-01-04T08:55:58Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.