The Query Yields Multifaceted Results in Postgres

Is it possible to create a multidimensional array from a single Postgres Query?

I have 3 tables with the following columns:

  • tb_school (id, school_name) eg: 

    [
    {id:"1", school_name:"School1"},
    {id:"2", school_name:"School2"}
]

  • tb_profile (id, profile_name, school_id) eg: 

    [
    {id:"1", profile_name:"John", school_id:"1"},
    {id:"2", profile_name:"Peter", school_id:"1"},
    {id:"3", profile_name:"Sam", school_id:"1"},
    {id:"4", profile_name:"Susan", school_id:"2"},
    {id:"5", profile_name:"Jude", school_id:"2"},
    {id:"6", profile_name:"Kim", school_id:"2"}
]

  • tb_article (id, article_name, profile_id) eg: 

    [
    {id:"1", article_name:"Headline News", profile_id:"1"},
    {id:"2", article_name:"Sports Recap", profile_id:"2"},
    {id:"3", article_name:"Weather", profile_id:"3"},
    {id:"4", article_name:"Arts", profile_id:"4"},
    {id:"5", article_name:"Other", profile_id:"5"},
    {id:"6", article_name:"Example", profile_id:"6"}
]

I am looking for the query result in this format:

[
    {school_name:"School1", people:[
        {profile_name:"John", articles:[
            article_name:"Headline News"
        ]},
        {profile_name:"Peter", articles:[
            article_name:"Sports Recap"
        ]},
        {profile_name:"Sam", articles:[
            article_name:"Weather"
        ]},
    ]},
    {school_name:"School2", people:[
        {profile_name:"Susan", articles:[
            article_name:"Arts"
        ]},
        {profile_name:"Jude", articles:[
            article_name:"Other"
        ]},
        {profile_name:"Kim", articles:[
            article_name:"Example"
        ]},
    ]}
]

Instead of using nested for loops and separate select queries, is there a way to achieve this with just one postgres query?

javascriptsqlarrayspostgresqlmultidimensional-array

Answer №1

Disclaimer: My expertise in PostgreSQL is limited, and I may have made incorrect assumptions or written code that is not suitable for production use.

I believe that directly querying a nested object might not be possible in PostgreSQL. However, you can retrieve data in JSON format instead. Here is an example:

SELECT 
json_build_object(
  'school_name', school_name,
  'people', (
  SELECT 
  json_agg(json_build_object(
  'profile_name', profile_name,
  'articles', (
  SELECT 
  json_agg(json_build_object(
  'article_name', article_name
  ))
  FROM tb_article
  WHERE profile_id = tb_profile.facid
  ) 
  ))
  FROM tb_profile
  WHERE school_id = tb_school.id
 )
) AS result
FROM tb_school

This query will return the row as JSON, which can then be parsed using JSON.parse.

If you wish to test this functionality, you can try using this educational platform. I ran tests with a similar code snippet provided there.

SELECT 
json_build_object(
  'name', firstname,
  'slots', (
  SELECT 
  json_agg(json_build_object(
  'number', slots,
  'facility', (
  SELECT 
  json_agg(json_build_object(
  'name', name
  ))
  FROM cd.facilities
  WHERE facid = cd.bookings.facid
  ) 
  ))
  FROM cd.bookings
  WHERE memid = cd.members.memid
 )
) AS result
FROM cd.members

These examples should serve as a helpful reference or potentially spark new ideas for your PostgreSQL queries.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Is there a way in MySQL to compare JSON data from one table with a set of ID values from another table using JSON_OVERLAPS function?

I'm dealing with two MySQL tables structured like this: first_table: the "json_field" column contains lists of integers, but is defined as type "json": id json_field 1 [1,2,3] 2 [2,3] 3 [1,3] second_table: a straightforward table with 2 c ...

mysqlsqljson

Node.js offers a simple and effective way to redirect users to another page after they have

I am experiencing an issue when trying to redirect the client to the confirm page after a successful login process. I keep encountering some errors. view screenshot router.post('/sign_in', urlend, function(req, res) { var email = req.body.user ...

javascriptnode.js

experimenting with adding fresh choices to dropdown menu using ajax and jquery

When attempting to load a list of locations through ajax/jQuery, I encounter an issue. After typing a letter into the input field, the first response is displayed but subsequent responses are simply appended to it. I have tried using .html('') an ...

javascriptjqueryhtmlajaxhtml-select

Steps to hide the sidenav when clicking on a hyperlink

Everything is working smoothly with the navigation, but there's a small glitch to address: the side navigation bar should close automatically when a link is clicked on this one-page website. Can anyone assist me with this issue? Here is the code snip ...

javascriptjquerycsshtmlsidenav

V-model prevents checkbox from being checked upon clicking

codesandbox: https://codesandbox.io/s/github/Tapialj/AimJavaUnit6?file=/src/frontend/src/components/AddMovieForm.vue I am currently working on implementing a feature where I need to collect an array of selected actors using checkboxes. I have set up a v-m ...

javascripthtmlvue.jsv-model

Retrieve events triggered by each element

Currently, my research centers around digital marketing and user experience. In order to gather the necessary data for this study, I must collect event logs from all components within a UI to create datasets on usability patterns. In a web interface, such ...

javascriptreactjsreact-native

Occasionally, Node.js is throwing an UnhandledPromiseRejection error, and it seems like my catch block is

Here is a snippet of my code: router.get('/myapi/someotherapi/:id', (request, response) => { console.log('api: GET /admin/myapi/someotherapi/:id'); console.log('Reject star redeem requests by id'); auth.verifyTo ...

javascriptnode.jsexpresserror-handlingtry-catch

Database displaying CharField as IntegerField

We are currently developing a movie database application using Django 1.7 with PostgreSQL. Let's take a look at our models.py file: class Movies(models.Model): name = models.CharField(max_length=50) lang = models.ForeignKey(Language, null=Tr ...

pythondjangopostgresql

organizing various kinds of data values within an array

Within this array, I have two types of variables - an integer and a string. My goal is to sort the array either alphabetically or by the length of the string. However, it seems that the sorting algorithm is prioritizing the integer over the string. ...

javascriptarraysreactjs

Identify the user's default character encoding in order to provide the appropriate CSV file

Is there a way for the website to automatically serve CSV files encoded as windows-1252 to users on Windows workstations, and encoded as UTF-8 to other users? Currently, two URL links are used which requires the user to decide which one to click. Most use ...

javascriptcsvbrowserdownload

Expanding the size of a textarea using JavaScript

JavaScript: function adjustRows() { var value = document.getElementById("test1").value.length; if (value <= 18) { value.rows = 1; } else if (value > 18 && value < 36) { value.rows = 2; } else if (value > 36 && v ...

javascriptjqueryhtmlcsstextarea

The functionality of Ajax is not compatible with Internet Explorer

I am currently managing a project on an e-commerce website that involves allowing visitors to rate different products using a star rating script. Everything is functioning correctly, but I need to ensure that each product can only be rated once per visito ...

phpjavascriptajax

Tips for adding a progress bar to your sidebar

I have a query regarding how to expand the width of a div within an li element. Essentially, as a user scrolls, the sidebar on the right-hand side will cause the span element (highlighted with a red background color) inside the li to transition from 0 to ...

javascriptjqueryhtmlcss

"Combining the power of Angularjs and the state

I've been delving into Redux, mainly in the context of using it with React. However, I use AngularJS. Is there a compelling advantage to implementing Redux instead of handling state within AngularJS scope and letting Angular manage the bindings? ...

javascriptangularjsangularjs-scoperedux

Guide to appending two strings into an array and showcasing them in a vertical format

Looking to add two strings to an array and display them one below the other: const str1 = "SamsungGalaxy A52" const str2 = "SamsungGalaxy A53" let arr=[] arr.push(str1,str2) This will output as follows: SamsungGalaxy A52 SamsungGalaxy A53 ...

javascriptarraysecmascript-6

Shortcutting assignments with unnamed or arrow function callbacks

Here is a code snippet that I need help with: before('get all users', h => { return getAllUsers().then(function (users) { return h.supply.users = users; }); }); Is there a more concise way to write this? Any alternate assign ...

javascriptnode.jsarrow-functions

``There seems to be an issue with disapplying a CSS class using JavaScript

I've designed a website that appears to have multiple pages, but in reality, all the content is contained within one HTML file: index.html. Using javascript and CSS, I have divided the site into a "home" position and an "inside" position similar to a ...

javascriptcssremoveclass

"Exploring the power of arrays in the world

Hello everyone, I need some assistance. My goal is to have the array "ABD" as my output. I provided the array "AAABD" as input and attempted to achieve this by comparing two consecutive elements and removing one if they are equal. Can anyone pinpoint what ...

carrays

Creating every potential expression that satisfies a given grammar can be achieved by following these steps

I have developed a grammar for my application that includes the following expressions: (FIND, SEARCH, Lookup) [a, the, an, for] ITEM [in, at] (NEST, SHELF, DESK) The expressions consist of required items in round brackets "()", optional items in square b ...

pythonarraysparsinggrammar

Determine the current week within the month

Is there a way to determine the week number of the month using either javascript or jquery? For instance: In the first week: 5th July, 2010. / Week Number = First Monday In the previous week: 12th July, 2010. / Week Number = Second Monday Current Date: ...

javascriptjquerydate