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.

I'm wondering why you would use SQL to query JSON in a javascript context. — Shilly, Dec 29 '17 at 18:30
@Shilly I'm saving these values to a JSON file as I am using the js file to be used as the node. And I would need these values to remain the same even if the node gets shut down so I decided to save them to a JSON file. — qtt qtt, Dec 29 '17 at 18:33
That's not what I asked. I just wonder why you would use an SQL query in alasql over just parsing the JSON with JSON.parse() and then loop through the object until you find the correct server. — Shilly, Dec 29 '17 at 18:36
I could try that I suppose, do you have any relevant examples for this? — qtt qtt, Dec 29 '17 at 18:39

michalh · Accepted Answer · 2017-12-29T19:35:06.830

2

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"
    }
}

edited Dec 29 '17 at 19:35

answered Dec 29 '17 at 19:18

michalh

2,907
22
29

Thanks Michal, I tried this but got the following error `var values = Object.values(testfile); ^ TypeError: Object.values is not a function` – qtt qtt Dec 29 '17 at 19:31
I'm using v6.10.3. I will try updating it now – qtt qtt Dec 29 '17 at 19:36
Updated and it run with no errors. However..... it returned `[]` i see from your screenshot it should be working. I don't know why its not working for me. – qtt qtt Dec 29 '17 at 19:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/162186/discussion-between-qtt-qtt-and-michal-hainc). – qtt qtt Dec 29 '17 at 19:44

Shilly · Answer 2 · 2017-12-29T18:55:02.940

1

I was thinking of something like this. Since you have a json file and want to console.log a javascript object, it makes sense to me to just use javascript to get that object instead of using SQL.

/* Uncomment this so you use the actual json file
const testfile = require("./test.json");
const records = JSON.parse( testfile );
*/
// replace the following records definition by the code above
const records = {
    "15432213213564": {
        "server": "1234756783612",
        "time": "infinite",
        "reason": "not provided"
    },
    "4567863243123": {
        "server": "2343262364124",
        "time": "infinite",
        "reason": "not provided"
    },
    "5763542345345": {
        "server": "2343262364124",
        "time": "20",
        "reason": "test"
    }
};

const findAllServers = ( id_server, records ) => Object.values( records ).filter( record => record.server === id_server );
const all_servers_2343262364124 = findAllServers ( '2343262364124', records );

// will log an array
console.log( all_servers_2343262364124 );

edited Dec 29 '17 at 18:55

answered Dec 29 '17 at 18:48

Shilly

8,511
1
18
24

Thanks Shilly, how would I go about making it output all the servers found that matches? – qtt qtt Dec 29 '17 at 18:51
1

remove the [ 0 ], so you get an array with all the servers with that number. Since `.filter()` returns an array and I didn't know if the server number was unique or not. Once you get the array, you can use a for loop or `.forEach()` to loop through the servers. I've updated the code to return all the servers. ( changed one of the server numbers so one of the 3 shown wont match ) – Shilly Dec 29 '17 at 18:53
@Shiily Hmm, when I tried to implement this in my code I got this error: `[object Object] ^ SyntaxError: Unexpected token o in JSON at position 1` – qtt qtt Dec 29 '17 at 19:17
Also @Shilly how would I get the values of these: "15432213213564","4567863243123","5763542345345" – qtt qtt Dec 29 '17 at 19:22

score 0 · Answer 3 · answered Jan 03 '18 at 04:30

You can also try to use AlaSQL's SEARCH operator:

    const data = require('./test606a.json');
    let res = alasql('SEARCH AS @a \
        KEYS() AS @b \
        EX(@a->(@b)) \
        WHERE(server = ?) \
        RETURN(@b AS user, time, reason)\
        FROM ?',["45645645654645",data]);

The SEARCH operator was designed to walk over complex JSON structures.

Here:

AS @a - save current object to variable a
KEYS() - walk over the keys
AS @b - save each key into variable b
EX(@a->(@b)) - get value of the key
WHERE(server=?) - pass only records with required server parameter
RETURN(...) - create output structure
FROM ? - standard from clause

How to SQL SELECT from JSON and dynamically assign to variables

3 Answers3