search

AlaSQL / alasql

AlaSQL.js - JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or Excel.
http://alasql.org
MIT License
7.01k stars 651 forks source link

alasql query to parse a nested json array value and exporting it to excel #1011

Closed itzmearun closed 6 years ago

itzmearun commented 6 years ago

below is the snippet to replicate... Need a query in alasql to query the nested values in the array to export in xls.

0:{reservationId: 61, reservationName: "reseravtion name", startDate: "02/05/2014", endDate: "07/08/2014", PromoStrCount: 8, …}
    PromoStrCount:8
    endDate:"07/08/2014"
    promos:(3) [{…}, {…}, {…}]
        0:{promoId: 73525, promoName: "reservaton name", promostartDate: "02/05/2014", promoendDate: "02/09/2014", StrCount: 4, …}
            0:
            StrCount:4
            locations:(4) [{…}, {…}, {…}, {…}]
                0:{fixtureId: 95580, storeNbr: 3, storeName: "store name", reservationId: 61, reservationName: "reservation name", …}
                    0:
                    crossAdjacencyDesc:"FLOORING"
                    first_date_Available:null
                    fixtureEffDate:null
                    fixtureEndDate:null
                    fixtureId:95580
                    height:60
                    storeNbr:3
                    t1573FixtureId:0
                1:{fixtureId: 96209, storeNbr: 56, storeName: "LOWE'S OF NORWALK, CA  ", reservationId: 61, reservationName: "reservation name", …}
                2:{fixtureId: 99410, storeNbr: 598, storeName: "LOWE'S OF GREENVILLE, NC ", reservationId: 61, reservationName: "reservation name", …}
                3:{fixtureId: 89888, storeNbr: 1700, storeName: "LOWE'S OF N. FONTANA, CA ", reservationId: 61, reservationName: "reservation name", …}
            promoId:73525
            promoName:"promo name"
            promoendDate:"02/09/2014"
            promostartDate:"02/05/2014"
        1:{promoId: 78414, promoName: "promo name ", promostartDate: "02/10/2014", promoendDate: "07/08/2014", StrCount: 2, …}
        2:{promoId: 78415, promoName: "promo name ", promostartDate: "02/10/2014", promoendDate: "07/08/2014", StrCount: 2, …}
    reservationId:61
    reservationName:"reservation name "
    startDate:"02/05/2014"

I need to fetch the below data

itzmearun commented 6 years ago

@#need alasql query to access the inner json array values

mathiasrw commented 6 years ago

Please reopen this issue when you can provide an example of the desired output and examples of what you have tried until now.

itzmearun commented 6 years ago

This is the below query i tried to export to excel from the above json values

need to access the StrCount from promos array for each promoID for each reservationId

currently exporting to excel is only exporting first level. i,e no values are getting exported from promos and locations

alasql('SEARCH / AS @temp \
                promos AS @b \
                locations AS @c \
                RETURN(@temp ->reservationId AS Reservation_ID, @temp ->reservationName AS Reservation_Name, @temp ->startDate AS Reservation_Start_Date, \
                @temp ->endDate AS Reservation_End_Date, \
                @b->StrCount as StoreCount  @b->promoId as PromoId @b->promoName as PromoName \
                @c->crossAdjacencyDesc AS crsossAdj
            ) INTO XLSX("' + outputFile + '",{headers:true})\
            FROM ?',[$scope.data]);
mathiasrw commented 6 years ago

Please reopen this issue when you can provide an example of the desired output too.

itzmearun commented 6 years ago

I am not getting how to reopen this issue agian which you have closed @mathiasrw

itzmearun commented 6 years ago

desktop

mathiasrw commented 6 years ago

Please provide the input as json and expected output as json

itzmearun commented 6 years ago

in the above excel promostore count is not priniting

itzmearun commented 6 years ago

the input is json what is shared in the example i want to traverse and export the values to excel

itzmearun commented 6 years ago

I need a alasql query which can traverse this json and parse the value.

suppose if i want to export crossAdjacencyDesc value to excel from location array in the promos array which is in the reservation array. as shown in example how can i parse that with alasql query?

mathiasrw commented 6 years ago

The code provided in the first input is not valid json. Please provide valid json.

Use this if in doubt: https://jsonlint.com

itzmearun commented 6 years ago 
0:
    PromoStrCount:8
    endDate:"07/08/2014"
    promos:(3) [{…}, {…}, {…}]
             0:
            StrCount:4
            locations:(4) [{…}, {…}, {…}, {…}]
                0:{fixtureId: 95580, storeNbr: 3, storeName: "store name", reservationId: 61, reservationName: "reservation name", …}
                    0:
                    crossAdjacencyDesc:"FLOORING"
                    first_date_Available:null
                    fixtureEffDate:null
                    fixtureEndDate:null
                    fixtureId:95580
                    height:60
                    storeNbr:3
                    t1573FixtureId:0
                1:{fixtureId: 96209, storeNbr: 56, storeName: "LOWE'S OF NORWALK, CA  ", reservationId: 61, reservationName: "reservation name", …}
                2:{fixtureId: 99410, storeNbr: 598, storeName: "LOWE'S OF GREENVILLE, NC ", reservationId: 61, reservationName: "reservation name", …}
                3:{fixtureId: 89888, storeNbr: 1700, storeName: "LOWE'S OF N. FONTANA, CA ", reservationId: 61, reservationName: "reservation name", …}
            promoId:73525
            promoName:"promo name"
            promoendDate:"02/09/2014"
            promostartDate:"02/05/2014"
        1:{promoId: 78414, promoName: "promo name ", promostartDate: "02/10/2014", promoendDate: "07/08/2014", StrCount: 2, …}
        2:{promoId: 78415, promoName: "promo name ", promostartDate: "02/10/2014", promoendDate: "07/08/2014", StrCount: 2, …}
    reservationId:61
    reservationName:"reservation name "
    startDate:"02/05/2014"
mathiasrw commented 6 years ago

From jsonlint.com

Error: Parse error on line 1: 0: PromoStrCount: 8 -^ Expecting 'EOF', '}', ',', ']', got ':'

itzmearun commented 6 years ago 
{
    "PromoStrCount": 8,
    "endDate": "07/08/2018",
    "reservation": [{
        "reservationId": 61,
        "reservationName": "FLEX ECRT FLOORING 1 (VALUE RUGS)",
        "startDate": "02/05/2018",
        "promos": [{
                "StrCount": 4,
                "promoId": 73525,
                "promoName": "0218EC*FLEX ECRT FLOORING 1 (VALUE RUGS)",
                "promoendDate": "02/09/2018",
                "promostartDate": "02/05/2018"
            },
            {
                "StrCount": 5,
                "promoId": 73526,
                "promoName": "flooring",
                "promoendDate": "03/08/2017",
                "promostartDate": "03/06/2017"
            }
        ]

    }]
}
itzmearun commented 6 years ago

how to access the inner value StrCount which is in nested array in Json

the query what I have written is this to parse the json 

alasql('SEARCH / AS @a \
               promos AS @b \
                RETURN(@a->reservationId AS Reservation_ID, @a->reservationName AS Reservation_Name, @a->startDate AS Reservation_Start_Date, @a->endDate AS Reservation_End_Date, \
               @b->StrCount as Promostrcount \
            ) INTO XLSX("' + outputFile + '",{headers:true})\
            FROM ?',[$scope.data]);
mathiasrw commented 6 years ago

Please provide expected json output.

itzmearun commented 6 years ago

expected output: I want to export the StrCount 4 and StrCount 5 respectively for both promoId 73525 and promoId 73526 for reservationId 64.

basically i want a query to traverse the inner json array values so that i can export those values to excel

mathiasrw commented 6 years ago

Please provide an example of the exact json output you are looking for from the input 

{
    "PromoStrCount": 8,
    "endDate": "07/08/2018",
    "reservation": [{
        "reservationId": 61,
        "reservationName": "FLEX ECRT FLOORING 1 (VALUE RUGS)",
        "startDate": "02/05/2018",
        "promos": [{
                "StrCount": 4,
                "promoId": 73525,
                "promoName": "0218EC*FLEX ECRT FLOORING 1 (VALUE RUGS)",
                "promoendDate": "02/09/2018",
                "promostartDate": "02/05/2018"
            },
            {
                "StrCount": 5,
                "promoId": 73526,
                "promoName": "flooring",
                "promoendDate": "03/08/2017",
                "promostartDate": "03/06/2017"
            }
        ]

    }]
}
itzmearun commented 6 years ago

I dont need an json output i want the content which is present in the above json should be exported to an excel file using alasql query. The query what i have written is only capable of exporting the first level of the json.

The inner level values in the promos i am not able to travarse through my query. so help me out with a query to traverse so that i can export whatever content i need to.

suppose i need to export promoName from above json for both promoid 73525 and 73526 for reservationId 61 how the query should be written?

mathiasrw commented 6 years ago

Please provide a valid json that represents the data you wish to export to excel.

itzmearun commented 6 years ago

{

"reserv": [{
        "endDate": "07 / 08 / 2018",
        "promoId": 73525,
        "promoName": "promo name",
        "promoStoreCount": 4,
        "promodisplayDesc": 20142014,
        "reservationDesc": "dsfds ",
        "reservationId": 61,
        "reservationName": "reseervation name",
        "startDate": "02 / 05 / 2018",
        "statusCode": 2
    },
    {
        "endDate": "07 / 08 / 2018",
        "promoId": 73526,
        "promoName": "promo name",
        "promoStoreCount": 8,
        "promodisplayDesc": 20142014,
        "reservationDesc": "dsfds ",
        "reservationId": 61,
        "reservationName": "reseervation name",
        "startDate": "02 / 05 / 2018",
        "statusCode": 3
    },
    {
        "endDate": "05 / 04 / 2018",
        "promoId": 73527,
        "promoName": "promo name",
        "promoStoreCount": 5,
        "promodisplayDesc": 20142014,
        "reservationDesc": "dsfds ",
        "reservationId": 61,
        "reservationName": "reseervation name",
        "startDate": "05 / 08 / 2018",
        "statusCode": 3
    }

]

}

mathiasrw commented 6 years ago

There is no statusCode in the original input, so this can not be done.

itzmearun commented 6 years ago 
{
"reserv": [{
        "endDate": "07 / 08 / 2018",
        "promoId": 73525,
        "promoName": "promo name",
        "promoStoreCount": 4,
        "promodisplayDesc": 20142014,
        "reservationDesc": "dsfds ",
        "reservationId": 61,
        "reservationName": "reseervation name",
        "startDate": "02 / 05 / 2018"

    },
    {
        "endDate": "07 / 08 / 2018",
        "promoId": 73526,
        "promoName": "promo name",
        "promoStoreCount": 8,
        "promodisplayDesc": 20142014,
        "reservationDesc": "dsfds ",
        "reservationId": 61,
        "reservationName": "reseervation name",
        "startDate": "02 / 05 / 2018"

    },
    {
        "endDate": "05 / 04 / 2018",
        "promoId": 73527,
        "promoName": "promo name",
        "promoStoreCount": 5,
        "promodisplayDesc": 20142014,
        "reservationDesc": "dsfds ",
        "reservationId": 61,
        "reservationName": "reseervation name",
        "startDate": "05 / 08 / 2018"

    }

]
}
itzmearun commented 6 years ago

ok i dont need that filed statusCode in my output json

mathiasrw commented 6 years ago

In the input data you have provided there is no value of 05 / 04 / 2018.

Please provide valid json of input and desired output.

itzmearun commented 6 years ago

This issue have been solved