Working with a PostgreSQL database, I utilize node.js along with the pg module to query data. However, the resulting array in my server-side Javascript code contains duplicate values due to the way the data is returned. The duplicates are a result of multiple INNER JOINS with independent tables. For example, a combination of A and B from one table (qa_layers) with C and D from another table (qa_cases), alongside a shared value E from yet another table (qa_settings) will be represented as:
| settings_name | layer_name | case_name |
| E | A | C |
| E | A | D |
| E | B | C |
| E | B | D |
To filter out unique data for each column as arrays with distinct values (e.g. [A, B] and [C, D] for layer_name and case_name), there are various approaches that could be taken. However, each method presents its own challenges unless there is a specific library or functionality that can simplify the process.
- One option is to parse the array from the database in Javascript, which may involve multiple loops but could be more manageable with the help of a suitable library.
- Alternatively, adjusting the
PostgreSQLquery to minimize parsing requirements might be beneficial. By structuring the resulting array with easily parsable objects, the parsing step could be less cumbersome. See the query below for reference. - Another approach involves running multiple queries on the database to obtain the desired data. While this is deemed the least scalable solution, it may reduce overhead compared to parsing an array after executing multiple individual queries.
My PostgreSQL query:
SELECT run.id, settings.name AS settings_name, layer.name AS layer_name, qa_case.name AS case_name FROM qa_runs AS run
INNER JOIN qa_composites_in_run AS cr
ON cr.run_id = run.id
INNER JOIN qa_layers_in_composite AS lc
ON lc.composite_name = cr.composite_name
INNER JOIN qa_layers AS layer
ON layer.name = lc.layer_name
INNER JOIN qa_cases_in_run AS case_run
ON case_run.run_id = run.id
INNER JOIN qa_cases AS qa_case
ON qa_case.name = case_run.case_name
INNER JOIN qa_settings AS settings
ON settings.name = run.settings_name
WHERE run.id IN (27,28,29);
Current JavaScript array output:
[
{
"id": 29,
"settings_name": "Test Default",
"layer_name": "OpenStreetMapService",
"case_name": "VisitLondon"
},
...
]
Desired JSON structure:
[
{
"id": 27,
"settings_name": "QA Default",
"layer_names": [
{
"layer_name": "OpenStreetMapService"
},
...
]