Skip to content

Instantly share code, notes, and snippets.

@mitchellporter
Forked from lucdew/json_postgres.js
Created September 4, 2018 21:31
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mitchellporter/fa673e0c018b1e1262dbafb661119a33 to your computer and use it in GitHub Desktop.
Save mitchellporter/fa673e0c018b1e1262dbafb661119a33 to your computer and use it in GitHub Desktop.
Example of json document storage in postgresql and querying (with knex.js)
var connectionString = 'postgres://localhost:5432/postgres';
var Promise=require('bluebird');
var knex = require('knex')({
client: 'pg',
connection: {
user: 'postgres',
database: 'postgres',
port: 5432,
host: 'localhost',
password: 'admin'
},
debug: false,
pool: {
min: 1,
max: 2
}
});
var books = [
{ name: 'Da vinci', author: { 'first_name': 'Bob', last_name: "White" }},
{ name: '50 shades of blue', author: { 'first_name': 'Bob', last_name: "Heinzeberg" }}
];
// Create table with json data column
var schemaCreation =function() {
return knex.schema.dropTableIfExists('books')
.then(function() {
return knex.schema.createTable('books',function(table){
table.increments();
table.timestamps();
table.json('data').nullable();
});
});
};
// Insert the books
var inserts = function() {
var insertPromises = [];
books.forEach(function(book) {
insertPromises.push(
knex('books')
.insert({data: JSON.stringify(book), created_at : new Date(), updated_at: new Date() })
);
});
return Promise.all(insertPromises);
};
// Perform some selects on json
var selects = function() {
return knex('books').select(knex.raw("data->'author' as author")).whereRaw("data->'author'->>'first_name'=? ",[books[0].author.first_name])
.then(function(rows) {
console.log("Found "+rows.length+" books with authors first_name "+books[0].author.first_name);
rows.forEach(function(row){
console.log(row);
});
})
.then(function() {
return knex('books').select(knex.raw("data->'author' as book")).whereRaw("data->'author'->>'last_name'=? ",[books[0].author.last_name])
.then(function(rows) {
console.log("Found "+rows.length+" book(s) with last_name "+books[0].author.last_name);
console.log(rows[0]);
});
});
};
// Main flow
schemaCreation()
.then(function() {
console.log('Table created');
})
.then(inserts)
.then(function() {
console.log('Inserts done');
})
.then(selects)
.then(function() {
process.exit(0);
})
.catch(function(error){
console.log(error);
});
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment