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

Question

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!

"Nicer" is very subjective and since you are asking for an open-ended solution, this question is probably too broad (and opinionated). To output multiple columns each with delimited list is possible, but involves complicated (nested queries, advanced operations) SQL queries. Honestly, I think a nicer solution would be to query and iterate over each product, then separately query related options and use the host language to build separate lists for inclusion in the JSON. — C Perkins
– C Perkins, Commented Sep 24, 2019 at 18:35
Actually I take some of that back. SQLite does not support any TRANSFORM operation which is required to convert row data into new, dynamic columns. Sqlite SQL can generate delimited lists using the group_concat() window functions, but you would still get multiple rows per products, one row per type of option. It would still require some iteration over the returned rows in the host language. — C Perkins
– C Perkins, Commented Sep 24, 2019 at 18:42
Thank you for the input. How would you approach this dataset? Can you recommend an alternative solution? What does best practice say? Maybe I am indeed trying to do something that shouldn’t be done. Please suggest the best approach. — goodpixels
– goodpixels, Commented Sep 24, 2019 at 18:47

Shawn · Accepted Answer · 2019-09-24 21:58:08Z

2

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).

answered Sep 24, 2019 at 21:58

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related