How do you get the right side of a left join to be added as another 'column' in PostgreSQL-CodePudding

I'm not sure how possible this is as I don't have much formal training with PostgreSQL. I can do most things, and google the rest. But I am unsure how to word what I'm trying to do, so I haven't found anything really. I probably could just use Javascript to make multiple queries and join them that way, but I feel like there is a better way.

Currently in my queries to the database, I have tables with Foreign keys to the id of rows in other tables. A simple 1 to 1 relationship, so I have been currently testing with a simple left join.

But this has required me to add the columns of the right side row as aliased name columns of the left side.

So for example my data might look like this when it gets back to my NodeJs app:

[{id: 1, name: 'blah', other_table_id: 2, other_table_name: 'blah blah', other_table_date: '12-12-2022'}]

What I would hope to have is it look more like:

[{'id': 1, name: 'blah', 'other_table_id': 2, 'other_table': {'id': 2, name:'blah blah', date:'12-12-2022'}}]

Is this possible to do with a query statement, and if so, how would I go about it?

P.S. Not sure it's relevant but I am using the library node-postgres to do the querying.

As stated, I currently only know how to get my data back in the format:

[{id: 1, name: 'blah', other_table_id: 2, other_table_name: 'blah blah', other_table_date: '12-12-2022'}]

I am making queries in the format:

SELECT table1.id, table1.name, table1.other_table_id, table2.name AS other_table_name... FROM table1 LEFT JOIN table2 ON (table1.other_table_id = table2.id) ORDER BY table1.name

I would like it in the format:

[{'id': 1, name: 'blah', 'other_table_id': 2, 'other_table': {'id': 2, name:'blah blah', date:'12-12-2022'}}]

Base case scenario the format would be simplified to something like below? (this isn't legal SQL):

SELECT table1.*, table2 AS table1.table2 FROM table1 LEFT JOIN table2 ON (table1.other_table_id = table2.id) ORDER BY table1.name

Just not sure how.

CodePudding user response：

I prefer to sanitize and doing data transformation inside the application instead of do it in SQL/database. Because it would be more easier to change the database in the future if you want it.

But, to help you in this specific problem, follow this query sta tement using PostgreSQL's with json_build_object and json_build_array functions. You can use both functions to generate the JSON output like you desire:

SELECT
  table1.id,
  table1.name,
  json_build_object(
    'id', table2.id,
    'name', table2.name,
    'date', table2.date
  ) AS other_table
FROM table1
LEFT JOIN table2 ON (table1.other_table_id = table2.id)
ORDER BY table1.name

This will generate output that looks like this:

[
  {
    "id": 1,
    "name": "blah",
    "other_table": { "id": 2, "name": "blah blah", "date": "12-12-2022" }
  }
]

If you want to get an output as array, you change the query with this:

 json_build_array(
    json_build_object(
      'id', table2.id,
      'name', table2.name,
      'date', table2.date
    )
  ) AS other_table

I get this info in the postgresql documentation:

https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-BUILDING-ARRAYS-TABLE

Obviously it's a trade-off, but think in mind that now your application is tightly-coupled with your database.