CODEWITHSUNDEEP
node.jsjsonpostgresql
Brogan McPartland
Brogan McPartland

Reputation: 45

Format Data from SQL Query as JSON

I have a database table of the form

CREATE TABLE products(
 id SERIAL PRIMARY KEY,
 category TEXT,
 name TEXT,
 price REAL,
 weight REAL);

and I want to output JSON for my API in the form 

[{"category":"category name", 
"products":[{"name":"product name one","price":4.44,"weight":0.5}, 
{"name":"product name two","price":6.45,"weight":1.0}]}]

So if my table has the following entries:

INSERT INTO products (category, name, price, weight) 
VALUES ("toothpaste","Chrest Pro-White",4.99,6.4),
("toothpaste","Colgate Cavity Prevention",3.99,6.4), 
("body wash","Old Spice Hydro",5.99,16.0), 
("body wash","Axe Apollo",8.99,22.0);

the API should return 

[{"category":"toothpaste", 
"products":[{"name":"Chrest Pro-White","price":4.99,"weight":6.4}, 
{"name":"Colgate Cavity Prevention","price":3.99,"weight":6.4}]},
{"category":"body wash", 
"products":[{"name":"Old Spice Hydro","price":5.99,"weight":16.0}, 
{"name":"Axe Apollo","price":8.99,"weight":22.0}]}]

I could loop through all of the rows in the table and manually create the json object for each unique category name, but I was hoping there was a better way to do it.

Upvotes: 0

Views: 132

Answers (2)

Jeremy
Jeremy

Reputation: 6723

There are plenty of ways to do this by using an ORM and creating the json from the API server, but you could also do this directly in postgres:

select json_agg(categories) 
FROM (
  SELECT json_build_object(
         'category', category,
         'products', json_agg(
                        json_build_object(
                           'name', name, 
                           'price', price, 
                           'weight', weight)
                        )
       ) as categories
FROM products
GROUP BY category) sub;
[{"category" : "toothpaste", "products" : [{"name" : "Chrest Pro-White", "price" : 4.99, "weight" : 6.4}, {"name" : "Colgate Cavity Prevention", "price" : 3.99, "weight" : 6.4}]}, {"category" : "body wash", "products" : [{"name" : "Old Spi
ce Hydro", "price" : 5.99, "weight" : 16}, {"name" : "Axe Apollo", "price" : 8.99, "weight" : 22}]}]

Upvotes: 1

Nitesh Mangla
Nitesh Mangla

Reputation: 119

You can use this console.log(JSON.stringify(data));

Upvotes: 0

Related Questions