How to format node.js sqlite3 record set as a JSON object of record arrays

Question

I'd like to format my query results as a single JSON object containing an array object for each record. Need help writing the script though - the JSON.stringify function is building an array of objects (My JSON is inside out!).

I can always write a function to build the JSON manually but I have a feeling there's already a function to do what I'm looking for. I just can't find it.

The JSON string I want to get:

{["id":1,"info":"Ipsum 0"], ["id":2,"info":"Ipsum 1"], 
["id":3,"info":"Ipsum 2"], ["id":4,"info":"Ipsum 3"] (and so on) }

Actual Results

[{"id":1,"info":"Ipsum 0"},{"id":2,"info":"Ipsum 1"},
{"id":3,"info":"Ipsum 2"},{"id":4,"info":"Ipsum 3"},
{"id":5,"info":"Ipsum 4"},{"id":6,"info":"Ipsum 5"},
{"id":7,"info":"Ipsum 6"},{"id":8,"info":"Ipsum 7"},
{"id":9,"info":"Ipsum 8"},{"id":10,"info":"Ipsum 9"}]

My code so far (based on this example)

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database(':memory:');

db.serialize(function() {
  db.run("CREATE TABLE lorem (info TEXT)");

  var stmt = db.prepare("INSERT INTO lorem VALUES (?)");
  for (var i = 0; i < 10; i++) {
      stmt.run("Ipsum " + i);
  }
  stmt.finalize();

  var sql = "SELECT rowid AS id, info FROM lorem";

   // Print the records as JSON
    db.all(sql, function(err, rows) {
      console.log(JSON.stringify(rows));
    });

});

db.close();

What's surprising about the result being an array of objects? — Frederick Cheung
– Frederick Cheung, Commented Jul 18, 2013 at 18:44
Maybe I am converting it to JSON with the wrong function. I would like to see the results in the format I described. — Brad Hein
– Brad Hein, Commented Jul 18, 2013 at 18:56
Again: what's surprising about this? Do you have good knowledge of the JSON format? — gustavohenke
– gustavohenke, Commented Jul 18, 2013 at 19:00

Peter Lyons · Accepted Answer · 2013-07-18 19:06:22Z

2

Based on what I know of JSON I was expecting the whole recordset to be enclosed with curly brackets, and each record to be enclosed with a square bracket. However I'm seeing the opposite.

Nope, you have it backward. Database results will be modeled as an array of objects - 1 array represents the results of the entire query, and each object in that array represents a single result record. In JSON, Arrays use square brackets, objects use curly braces (Same as actual JavaScript code).

answered Jul 18, 2013 at 19:06

Peter Lyons

147k32 gold badges285 silver badges281 bronze badges

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

Collectives™ on Stack Overflow

How to format node.js sqlite3 record set as a JSON object of record arrays

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