How to SQL SELECT from JSON and dynamically assign to variables

Question

TL;DR - I would like to SQL from the format of "test.json" (example below) and I would like to assign each of the variables dynamically so that depending on how many entries there are it will output that amount (So it would look like the output example below)

I have this in a separate file named "test.json"

{
    "12321534232354": {
        "server": "2343262364124",
        "time": "infinite",
        "reason": "not provided"
    },
    "155149108183695360": {
        "server": "2343262364124",
        "time": "infinite",
        "reason": "not provided"
    }
}

And I am using the alasq module to SQL select from this file like so:

const alasql = require("alasql");
testfile = require("./test.json");
console.log(alasql("SELECT * FROM ? WHERE server='2343262364124'",[testfile]));

However, this returns [] and not the values that I selected. My ultimate goal in this is to be able to select a specific server and get the first line of each entry (in this case "12321534232354","155149108183695360") along with the "time" and "reason" and and assign them to variables/an array depending on how many there are. So I can console.log all these values

Desired outcome: For example:

"test.json" contains:

{
    "15432213213564": {
        "server": "45645645654645",
        "time": "infinite",
        "reason": "not provided"
    },
    "4567863243123": {
        "server": "45645645654645",
        "time": "infinite",
        "reason": "not provided"
    },
    "5763542345345": {
        "server": "45645645654645",
        "time": "20",
        "reason": "test"
    }
}

If i queried the server "45645645654645" I would like the Output to be: (for example: according to this example)

the user is 12321534232354
the time is infinite
the reason is not provided

the user is 155149108183695360
the time is infinite
the reason is not provided

the user is 5763542345345
the time is 20
the reason is test

Which is why I thought that I should assigning the users,times,reasons dynamically to variables?

Please let me know how I can do this, or better an example with code.

Thanks.

Answer 1

You have to post process the object loaded from the json file. I have constructed a working sample. I think alasql is not able to process the object because it is not array, in fact it is key value pair dictionary, my piece of code removes the keys, and makes an array of the values, then I am able to select:

const alasql = require("alasql");
var testfile = require("./test.json");
//get array of values of the object loaded from file
var values =Object.values(testfile);
//select
console.log(alasql("SELECT * FROM ? WHERE server = \"45645645654645\"", [values]));

Update: simplified it with Object.values()

I worked with this content of test.json:

{
    "15432213213564": {
        "server": "45645645654645",
        "time": "infinite",
        "reason": "not provided"
    },
    "4567863243123": {
        "server": "45645645654645",
        "time": "infinite",
        "reason": "not provided"
    },
    "5763542345345": {
        "server": "45645645654645",
        "time": "20",
        "reason": "test"
    }
}