CODEWITHSUNDEEP
sqlnode.jssqliteapi
goodpixels
goodpixels

Reputation: 523

How to prepare JSON objects and arrays from related database tables?

I am working on a simple REST API in node and I want to create a query in SQLite which will return all products along with their possible options.

This is my database:

BEGIN TRANSACTION;
PRAGMA foreign_keys = OFF;
CREATE TABLE IF NOT EXISTS "product_options" (
    "id"    INTEGER,
    "product_id"    INTEGER,
    "name"  TEXT NOT NULL,
    "value" TEXT NOT NULL,
    PRIMARY KEY("id"),
    FOREIGN KEY("product_id") REFERENCES "products"("id")
);
CREATE TABLE IF NOT EXISTS "products" (
    "id"    INTEGER,
    "name"  TEXT NOT NULL,
    "brand_id"  INTEGER,
    PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "brands" (
    "id"    INTEGER,
    "name"  TEXT NOT NULL,
    PRIMARY KEY("id")
);
INSERT INTO "product_options" VALUES (1,1,'color','black');
INSERT INTO "product_options" VALUES (2,1,'color','white');
INSERT INTO "product_options" VALUES (3,2,'color','indigo');
INSERT INTO "product_options" VALUES (4,3,'color','black');
INSERT INTO "product_options" VALUES (5,1,'size','9');
INSERT INTO "products" VALUES (1,'t-shirt',1);
INSERT INTO "products" VALUES (2,'jeans',3);
INSERT INTO "products" VALUES (3,'shoes',2);
INSERT INTO "products" VALUES (4,'shirt',NULL);
INSERT INTO "brands" VALUES (1,'acme');
INSERT INTO "brands" VALUES (2,'wonka');
INSERT INTO "brands" VALUES (3,'gekko');
INSERT INTO "brands" VALUES (4,'stark');
PRAGMA foreign_keys = ON;
COMMIT;

And this is a sample JSON output that my API endpoint should produce, when queried for a product:

{
    "id": 1,
    "name": "t-shirt",
    "brand": "acme",
    "options": {
        "color": ["black", "white"],
        "size": ["9"]
    }
}

So far I have come up with the first part of the query, but I cannot figure out how to output options:

SELECT DISTINCT
    products.id,
    products.name,
    brands.name AS brand,
    product_options.value AS option_value
FROM products
INNER JOIN brands on brands.id = products.brand_id
INNER JOIN product_options on product_options.product_id = products.id
WHERE products.id = 1

This returns the following result:

id  name    brand   option_value
1   t-shirt acme    black
1   t-shirt acme    white
1   t-shirt acme    9

However, the output I want is:

id  name    brand   color           size
1   t-shirt acme    black, white    9

Of course the query has to be "dynamic", so any other options added to the product in the future should be returned as well, for example:

id  name    brand   color           size    condition
1   t-shirt acme    black, white    9       new, used

Any ideas how I can achieve this? I know I can simply create two endpoints, one to retrieve the product list, and the other to return its options, but having all in one document seems like a nicer solution.

Any help will be much appreciated, thanks!

Upvotes: 0

Views: 353

Answers (1)

Shawn
Shawn

Reputation: 52336

You can use the standard JSON1 module and a bunch of aggregate subqueries to generate the JSON directly from sqlite:

SELECT
  json_object('id', p.id
            , 'name', p.name
            , 'brand', b.name
            , 'options', (SELECT json_group_object(name, ja)
                          FROM (SELECT po.name
                                     , json_group_array(po.value) AS ja
                                FROM product_options AS po
                                WHERE po.product_id = p.id
                                GROUP BY po.name)))
FROM products AS p
JOIN brands AS b ON p.brand_id = b.id
WHERE p.id = 1;

{"id":1,"name":"t-shirt","brand":"acme","options":{"color":["black","white"],"size":["9"]}}

For best results, add an index on product_options(product_id, name).

Upvotes: 2

Related Questions