Alasql - querying nested objects in json

Question

I have a json object like below.

Ebay Object

    {
    __v: 0
    _id: "56e192f0aea7131c15513328"
    headquarters: "New York"
    name: "Ebay"
    productCategories: [{
        _id: "56e193beaea7131c1551332d"
        name: "Footwear"
        products: [{
            name: 'Shiela',
            price: 420,
            totalSales: [10, 20]
        }, {
            name: 'Parry',
            price: 350,
            totalSales: [50, 20]
        }]
        totalSales: 100
    }, {
        1: Object
        _id: "56e193beaea7131c1551332e"
        name: "Clothes"
        products: [{
            name: 'Kurta',
            price: 210,
            totalSales: [60, 80]
        }, {
            name: 'Sun Glass',
            price: 785,
            totalSales: [5, 25]
        }],
        totalSales: 170
    }]
}

Amazon Object

{
        __v: 0
        _id: "56e192f0aea7131c15513328"
        headquarters: "New York"
        name: "Amazon"
        productCategories: [{
            _id: "56e193beaea7131c1551332d"
            name: "Footwear"
            products: [{
                name: 'Shiela',
                price: 280,
                totalSales: [10, 20]
            }, {
                name: 'Parry',
                price: 785,
                totalSales: [50, 20]
            }]
            totalSales: 100
        }, {
            1: Object
            _id: "56e193beaea7131c1551332e"
            name: "Clothes"
            products: [{
                name: 'Kurta',
                price: 150,
                totalSales: [60, 80]
            }, {
                name: 'Sun Glass',
                price: 485,
                totalSales: [5, 25]
            }],
            totalSales: 170
        }]
    }

I want to select each name inside product categories, that are common to both the companies.

Then, I want to select the products that are common to the common product categories.

Then I want to get the price of the common products (to both the companies) for comparison

I am able to run the below query

 alasql('SELECT products FROM ? AS CATEGORY1 JOIN ? AS CATEGORY2 USING [0]', [$scope.company1.productCategories, $scope.company2.productCategories], function(data) {
                    console.log("join query executed");
                    console.log(data);
                });

I want to find the products inside each product category. I want a query like

alasql('SELECT products.name,products.price FROM ? as category1 join ? as category2 using products.name', [$scope.company1.productcategories,$scope.company2.productcategories], function(data) {
                    console.log("Query executed");
                    console.log(data);
                });

But this errors out.

Please let me know the correct procedure.

Regards, Sabarisri

score 4 · Answer 1 · answered Mar 14 '16 at 23:10

4

First, when you are only working on local memory there is on reason to make it async. So your first change should be to use

var res = alasql('SELECT productCategories->0->name FROM ?',[$scope.selectedCompanies]);

You are very close. You basically want to query on only the productCategories data - so you should do

var res = alasql('SELECT name FROM ?',[$scope.selectedCompanies.productCategories]);

Bonus info: If you want to join with something you could do something like

var res = alasql('SELECT p.name, d.stock FROM ? p JOIN ? d ON p.name = d.company',[$scope.selectedCompanies.productCategories, $scope.otherData]);

answered Mar 14 '16 at 23:10

mathiasrw

610
4
10

In my case,I cannot each time, give the complete array object to the alasql function. The reason why i asked this question is, I actually have two companies. I want to list out only the products common to both the companies, for price comparison. So I want to get the price of each product , provided the product occurs in both the companies. I have editted my schema object . Please refer to the 'product' definition – sabari Mar 16 '16 at 08:55
I have editted the description part also. Please go through it once. and kindly let me know the answer. – sabari Mar 16 '16 at 09:23

Alasql - querying nested objects in json

1 Answers1