2do2go / json-sql Goto Github PK

Great library! Is there a way to do GROUP BY?

I need to make a query that looks like this:

select "stamp", DATE_TRUNC('week', stamp::timestamp) as "week", "location", sum("impression") as "impression", sum("click") as "click" from "testTable" where ("stamp" >= $1 and "stamp" < $2) and "DATE_TRUNC('week', stamp::timestamp)" in ($3) group by "stamp", "week", "location" order by "stamp";

I'm using js output of json like this:

  table: 'testTable',
  fields: 
   [ { name: 'stamp', display: 'Time' },
     { alias: 'week',
       expression: 'DATE_TRUNC(\'week\', stamp::timestamp)',
       display: 'Week',
       urlmap: 'week' },
     { name: 'location', display: 'Location' },
     { func: [Object], alias: 'impression', display: 'Impression' },
     { func: [Object], alias: 'click', display: 'Click' } ],
  condition: 
   { stamp: 
      { '$gte': '2017-01-01T00:00:00Z',
        '$lt': '2018-01-01T00:00:00Z' },
     'DATE_TRUNC(\'week\', stamp::timestamp)': { '$in': [Array] } },
  group: [ 'stamp', 'week', 'location' ],
  sort: [ 'stamp' ] }```


The problem is that this query won't run as `"DATE_TRUNC('week', stamp::timestamp)"` in condition is quoted. 

I've read unit tests and documentation and I've not seen a way to deal with this. 

thanks.

How do we write a query like below in json-sql:
select * from TableA as a inner join TableB as b on a.id = b.id and b.name = 'some value'.

How to execute a bulk insert? MySQL query like bellow.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

How can we pass options in below code:

var jsonSql = require('json-sql')(options);

I want to change the dailect to 'mssql' and separatedvalues to false

Please suggest

Hello! I have a hard sql query. This is part of sql
group by (cast(height / $delegates as integer) + (case when height % $delegates > 0 then 1 else 0 end)) having height <= $height

I made something like that:
group: {
expression: "(cast(height / $delegates as integer) + (case when height % $delegates > 0 then 1 else 0 end))",
having: {
height: {$lte: height}
}
}

Can you add this feature, I can´t make this.

Way too lazy to write a pull request, but I had to change line 50 in builder.js

this._values[this.options.valuesPrefix + placeholder] = value;

If I leave it as is, I get "SQLITE_RANGE: column index out of range"

The name of the properties in

Is there any way to use Top N records in MS SQL

for example : Select Top 10 userid, username from User;

Is limit keyword defined only for MySql

Please suggest... 👍

Great little library, works well for me.

Would it be possible to rename the "condition" template to "where"? Almost all other keywords follow standard SQL naming, but "where" does not and is very puzzling. What was the reasoning behind that design choice?

Hi, I'm trying to use your module to convert json objects into sql queries that I can output to a file,

So following your insert example, I eventually come up with

insert into test (check1, check2, check3) values ($p1, $p2, $p3);
{ p1: 'check1', p2: 'check2', p3: 'check3' }

Do you know how I could make that readable to SQL as part of a .sql file? where the values are part of the string?

Also do you have any plans to support foreign keys?

Otherwise thank you so much for this module. It is a life-saver.

Can we use have expression in Where condition

e.g
select s.totalAmount where (s.TaxAmount + s.CessAmount) > 0

We are not able to provide the (s.TaxAmount + s.CessAmount) > 0 in the where condition

I cannot find a way to generate a query like this:

SELECT ROW_NUMBER() OVER (ORDER BY id DESC), * FROM posts;

I am using PostgreSQL. Is it possible to use the ROW_NUMBER() value?

condition could accept string

[mariadb/mysql] does not support named placeholders like :col1 - use ? instead

const jsonSql = require("json-sql")({
  namedValues: false,
  valuesPrefix: "$$",
});
jsonSql.setDialect("mysql");
    const sql = jsonSql.build({
      type: "update",
      table: "table1",
      condition: {
        guid: id,
      },
      modifier: updateFields,
    });
/// CONVERT placeholders
const query1 = sql.query.replace(/\$\$\d+/g, "?");

Maybe this can be built into the mysql dialect?

I would like to execute the query on specific schema in postgreSQL. so where can I define the schema name?
tried bellow code but not working

jsonSql.build({
         type: 'select',
          table: 'user',
          schema: 'schemaName',
          fields: fields,
          condition: condition
})

working by this way table: 'schemaName.tableName'

jsonSql.build({
         type: 'select',
          table: 'schemaName.tableName',
          fields: fields,
          condition: condition
})

Is this valid way or have other way ?

I'm new to NPM so please bear with me.. Can this be used for my MS SQL DB? If yes, how do connect my application using your package? I do not see in the documents on how to connect it.

Thanks.

Hello,

This library is great, but there is one crucial PostgresSQL missing feature ; on conflict update.

How complicated would it be to add it ?

Select query with where != 'OPEN'

My object event:
"payload": {
"cpf": "12345678990",
"id": ""
}

My build:
var sql = jsonSql.build({
type: 'insert',
table: 'perfis_'+event.operation,
values: event.payload //object
}).query;

Stacktrace:
Error: Wrong value type "object"
at Builder.pushValue (/var/task/node_modules/json-sql/lib/builder.js:55:9)
at /var/task/node_modules/json-sql/lib/dialects/base/blocks.js:188:26
at Dialect.buildBlock (/var/task/node_modules/json-sql/lib/dialects/base/index.js:323:9)
at /var/task/node_modules/json-sql/lib/dialects/base/blocks.js:357:21
at Function..map..collect (/var/task/node_modules/underscore/underscore.js:164:24)
at .(anonymous function) as map
at /var/task/node_modules/json-sql/lib/dialects/base/blocks.js:356:22
at Function..map..collect (/var/task/node_modules/underscore/underscore.js:164:24)
at _.(anonymous function) as map
at /var/task/node_modules/json-sql/lib/dialects/base/blocks.js:355:27

I just can't figure out how returning should work.

Builder config:
{ dialect: 'mssql', separatedValues: false }

Query:

const query = {
        table: 'Messages',
        type: 'insert',
        returning: ['ID'],
        values: {
          Name: 'Blah',
          Text: 'Some text'
        }
      }

Yeilds:
insert into "Messages" ("Name", "Text") values ('Blah', 'Some text') returning "ID";

Error:
RequestError: Incorrect syntax near 'returning'.

Obviously this will fail, as is not valid MSSQL syntax.

Am just trying to output the inserted row's primary key. It doesn't seem like valid

Builder.prototype._getPlaceholder = function () {
	var placeholder = '';
	if (this.options.namedValues) placeholder += 'p';
	if (this.options.indexedValues) placeholder += this._placeholderId++;
	return placeholder;
};

The code at the beginning creates the naming of parameters in prepared statements. Unfortunately it creates prameters with the pattern <valuesPrefix>p<numberOfParameter>. PostgreSQL is not accepting this naming pattern but only<valuesPrefix><numberOfParameter> without the p. You might want to fix that.

I have a simple select statement with a condition having a column equaling to a Date instance. Please see the result. Does not seem like it's an expected behavior.

var date = new Date();
var result = jsonSql.build({
  table: 'users',
  condition: {
    insertdate: date
  }
});

// result.query: select * from "users" where "insertdate";
// result.values: []

Of course, running this results in an error.

I tried to poke around in the code, but could not quite see where to handle this. Any suggestions/help are appreciated.

when doing like condition I mast have the same case as the data, I want to case insensitive in the condition the way to do that is here
https://stackoverflow.com/questions/7005302/postgresql-how-to-make-case-insensitive-query

It would be great if we could have MongoDB BSON ObjectId values converted automatically to String instead of being removed from the query. I'd gladly make a PR if this is still maintained, let us know.

Right now it's not possible to use Buffers with this library due to type detection.

(e.g. you can't pass a Buffer to a builder query and then run it)

It will throw an error here since Buffer is an object:

Hello, I can't figure out the recommended way using this module to insert into multiple tables at once.

Eg. I have a chat msg from a user, I want to insert into the users table data about the user, and the msg into the msgs table at the same time.

Eg.

START TRANSACTION;
INSERT INTO table1 VALUES ('1','2','3');
INSERT INTO table2 VALUES ('bob','smith');
COMMIT;

Thanks!

Roo

Critical Arbitrary Code Execution in underscore
Package underscore
Patched in >=1.12.1
Dependency of json-sql

run npm audit

Similar to #56 this library doesn't yet support null values.

example:
SELECT a FROM table WHERE b = 1 OR c = 1
SELECT a FROM table WHERE b = 1 AND c = 1 AND (d like "dd" OR e like "ee")

Is it possible to use the MODULO operator (% or MOD()) with json-sql?

I need something like this (find all even block heights):

select *
from blocks
where height % 2 = 0

I searched the code and the tests I couldn't find it.

Thanks in advance!
Kind regards
a1300

Is it possible to do multiple inner join?
E.g. inner join with 3 tables

I have provided the options for MySQL database

var jsonSql = new (require('json-sql').Builder)(
{separatedValues: false},
{dailect: 'mysql'},
{wrappedIdentifiers: false
});

The complete code is:

//Declaring the paramters which are passed as arguments
var BuId;

//Getting the arguments from the command prompt.

//Removing the first two arguments from the list. First argument is the command "node" //and the second argument is the path of the script file.
var args = process.argv.slice(2);

//Assigning the arguments which are passed from command prompt to the variables
args.forEach(function (val, index, array) {
//console.log(index + ': ' + val);
if (index == 0)
{
BuId = val;
}
});

//To create new instance of json-sql builder. The script generated for which database can be specified here.
var jsonSql = new (require('json-sql').Builder)({separatedValues: false},{dailect: 'mysql'},{wrappedIdentifiers: false});

//Building the query
var sql = jsonSql.build({
type: 'select', //Defining the Query type - Whether Select, Insert etc...
table: 'businessunit', //Specifying the Table
alias: 'bu', //Specifying the alias name for the Table
//Specifying the fields for the tables and the alias name for each field.
fields: [{name: 'Name', table: 'bu', alias: 'BuName'},
{name: 'Company', table: 'bu', alias: 'Company'},
{name: 'UserName', table: 'usr', alias: 'UserName'},
{name: 'Email', table: 'usr', alias: 'Email'},
{name: 'PhoneNo', table: 'usr', alias: 'PhoneNo'},

        ],

//joining multiple tables by specifying the type of join and the alias name of the joining table
join: {
'userinfo': {type: 'inner', on: {'bu.BusinessUnitId': 'usr.BusinessUnitId'}, alias: 'usr'},
},
//Specifying the where conditions
condition: {

        //retrieving values based on the condition prc.PricingDate >= fromdate and prc.PricingDate <= todate
        'usr.BusinessUnitId': {$eq: BuId},  
        
  	}

});

sql.query

console.log(sql.query);
/console.log(sql.values);/

But on executing the JSON to SQL getting the double quotes against the identifiers in the result SQL as given below...
select "bu"."Name" as "BuName", "bu"."Company" as "Company", "usr"."UserName" as "UserName", "usr"."Email" as "Email", "usr"."PhoneNo" as "PhoneNo" from "businessunit" as "bu" inner join "userinfo" as "usr" on "bu"."BusinessUnitId" = "usr"."BusinessUnitId" where "usr"."BusinessUnitId" = '1';

Since there are double quotes for the alias and Field name, the query is not getting executed in MySQL....

Please help....

How to build json for having with aggregate function

eg:
select name, department, sum(attendance) group by name, department having sum(attendance) > 100

First of all, thanks for making this!

I'm trying to do something like select 'Done' as status or select null as account is there a way to do this without the values getting double quoted?

Hi,

We need JSON to Sql conversion for oracle. Do you have included this...

I am having issues generating valid mysql query when there is a . used in the table name.

        let jsonSql = require('json-sql')({
            dialect: 'mysql'
        });
        let sql = jsonSql.build({
            type: 'select',
            table: this.table,
            condition: {
                KundenNR: kundenNr
            }
        });
        console.log(sql.query);

Output:
select * from `dbo`.`clients` where `KundenNR` = $p1;
Expected Output:
select * from `dbo.clients` where `KundenNR` = $p1;

How to get top n records in MS Sql

I am using Postgres. Here is my table structure.

CREATE TABLE "public"."projects" (
	"id" int4 NOT NULL DEFAULT nextval('projects_id_seq'::regclass),
	"name" text NOT NULL COLLATE "default",
	"owner_id" int4
)
CREATE TABLE "public"."users" (
	"id" int4 NOT NULL DEFAULT nextval('users_id_seq'::regclass),
	"email" text NOT NULL COLLATE "default",
	"first_name" text COLLATE "default",
	"last_name" text COLLATE "default"
)
ALTER TABLE "public"."projects" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE "public"."projects" ADD CONSTRAINT "projects_name_key" UNIQUE ("name") NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE "public"."users" ADD PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE "public"."users" ADD CONSTRAINT "users_email_key" UNIQUE ("email") NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE "public"."projects" ADD CONSTRAINT "projects_owner_id_fkey" FOREIGN KEY ("owner_id") REFERENCES "public"."users" ("id") ON UPDATE NO ACTION ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE;

Here is how I am calling jsonSql.build:

jsonSql.build({
    type: 'select',
    table: 'users',
    distinct: undefined,
    group: '',
    sort: {},
    limit: 25,
    offset: 0,
    fields: ['users.id',
        'users.email',
        'users.first_name',
        'users.last_name'
    ],
    condition: {},
    join: {
        projects: {
            type: 'inner',
            on: {
                'users.id': 'projects.owner_id'
            },
            select: {
                table: 'projects',
                fields: ['projects.id', 'projects.name'],
                join: {},
                sort: {},
                group: '',
                distinct: undefined,
                limit: undefined,
                offset: undefined,
                condition: {}
            },
            alias: 'projects'
        }
    }
});

That produces following query:
select "users"."id", "users"."email", "users"."first_name", "users"."last_name" from "users" inner join (select "projects"."id", "projects"."name" from "projects") as "projects" on "users"."id" = "projects"."owner_id" limit 25 offset 0;

When executed, it throws following error:
error: column projects.owner_id does not exist

Great library！
I am having a question：
How to use escape symbols when single quotes appear in the value？

Is it possible to use json-sql for phpmyadmin, since that service uses backticks and my sql contsructed querys consist of double quotes. Besides: How can I retreive the query with the values instead of $p1 $p2 and so on?

Hey, I couldn't figure a way to do this - is there a way to have case conditions (case when x then ... etc) in a select query?

Thanks

Example:

SELECT a FROM table WHERE b >= 1000
SELECT a FROM table WHERE b <= 1000