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": ""
}