planetarydev / json-sql-builder2 Goto Github PK

Is there a method for doing a select all with columns? Ideally allowing for further columns. I've looked through the documentation but I don't seem to be able to find anything referencing this,

For example:

SELECT
	Table1.*,
	Table2.Column3
FROM
	Table1
	LEFT JOIN Table2 ON Table1.Column1 = Table2.Column2

Hoping to be able to do:

SELECT
	Table1.*,
	(
		SELECT
			COUNT(*)
		FROM
			Table1
		WHERE
			< some conditions >
	) `count`
FROM
	Table1
WHERE
	< some conditions >

I'm getting this below error on deploying aws lambda node 8.10.0

2019-05-09T04:23:22.996Z 6010d98c-2788-4c96-b972-49361876948c Error: ENOENT: no such file or directory, scandir '/sql/'
at Object.fs.readdirSync (fs.js:904:18)
at Object.walk (/var/task/src/index.js:144899:16)
at SQLBuilder._loadModules (/var/task/src/index.js:144709:23)
at new SQLBuilder (/var/task/src/index.js:144298:8)
at handler (/var/task/src/index.js:76189:15)

What is the state of this project regarding supporting MongoDB features? This is by far the most comprehensive project I have seen offering a somewhat compatible syntax, and I'd be willing to contribute in adding missing helpers and operators.

Was this why this project have been created, or was the idea to support yet another subset of JSON queries? And if so, why can't it be made to be an adapter layer between MongoDB queries and SQL queries?

I understand the limitations of both specifications, and I wouldn't expect everything to be supported, but having these features would allow projects to integrate SQL with NoSQL more easily, and it seems like this project has already the foundations for this.

• node v8.2.1

got an error:

/root/code/node-crx-mid/node_modules/json-sql-builder2/sql/helpers/functions/string/left/left.js:248
                                                        people_id: { $column: { $type: 'INT', ...NOT_NULL, ...PRIMARY_KEY } },
                                                                                              ^^^

SyntaxError: Unexpected token ...
    at createScript (vm.js:74:10)
    at Object.runInThisContext (vm.js:116:10)
    at Module._compile (module.js:533:28)
    at Object.Module._extensions..js (module.js:580:10)
    at Module.load (module.js:503:32)
    at tryModuleLoad (module.js:466:12)
    at Function.Module._load (module.js:458:3)
    at Module.require (module.js:513:17)
    at require (internal/module.js:11:18)
    at SQLBuilder._register (/root/code/node-crx-mid/node_modules/json-sql-builder2/lib/builder.js:474:19)

Because the object spread operatior is not supported in node v8.2.1:

If find this library excellent for my purpose, but it really emits wrong indices for SQL Server parameters.

I see there is a pull request for this already, and it needs to be fixed for this library to be usable.

Hi,

I am trying to use trim, upper, lower functions, but am unable to.
getting errors as:

The Helper or Operator with the name '$trim' is not defined
The Helper or Operator with the name '$upper' is not defined
The Helper or Operator with the name '$lower' is not defined

if json-sql-bulder2 supports these, please share examples for the same

The MySQL function LAST_INSERT_ID() should be supported to facilitate inserts.

sql.$select({ last_id: { $lastInsertedId: true } });

should produce

SELECT LAST_INSERT_ID() AS last_id

I am writing a database library for many basic functions for many database types. I found your library and it coincides a lot with my own methodology concerning query generation. I am leaving an Query.raw() function available for more advanced queries, but one thing that seems to be missing from your library is $alterTable as opposed to $createTable.

I am going to check out the forks of your library to see if someone else has added such functionality but I was hoping you might be spurred to create an update to this and npm. I am currently writing my own query language and translators to work with many database types from Mongo to MsSql, Couch, Cockroach, and even Oracle. I was going to use Knex.js, but its methodology is not the same.

In any case, Thank You for this cool library. Ill have a credits page somewhere with all the packages used in the projects.

Hello,

I am sending this json

{
    "$select": {
        "$columns": ["day", "firstname", "lastname"],
        "$from": "a.b.1"
    }
}

As a result I got:

{
  "sql": "SELECT day, firstname, lastname FROM a.b.\"1\"",
  "values": []
}

What I was expecting was:

{
  "sql": "SELECT day, firstname, lastname FROM a.b.1",
  "values": []
}

The point in the code that changes what I expected is in the following validation.

Is there any reason the identifier must not begin with numbers?

I could fix it, if there is no problem in changing this condition.

Best regards.

I'm trying to do a:

UPDATE ball SET [cmdList].modify('insert <val>1030</val> into (/cmdList)[1]'), ogga.modify('insert <val>30</val> into (/ogga)[1]') WHERE [ballId] = @param3"

Or actually, I wanted the inserts to be sql arguments as well, but that does not seem to fit very well with SQL server rigid regime.

I ended up assigning the modify string to the json key in the $set, and setting the value (sql argument) to null. Then I had to do a nasty regex to ret rid of the argument part and some misplaced brackets:

const nastyModifyRegex = /\[modify\((.+?)\[1\]\]?\)\]\s?=\s?@param\d+/g

const correctNastyModifyFunction = (output: ISQLlyThings) => ({...output, sql: output.sql.replace(nastyModifyRegex, "modify($1[1])")})

It would be nice to have support for modify in the library. I tried to read up on operators, but it seems this needs to be built into the library, no plugin-system available.

how can I achieve select name from foo order by upper(name);? I can only find select upper(name) as uname, name from foo order by uname; but it overloads select.

Hi,

Do you have any operator for lower /upper case conversion for any columns in select or in where clauses?

Thanks

The following code is fails on Windows.

const SQLBuilder = require('json-sql-builder2');
const sql = new SQLBuilder('MySQL');

The details of error is:

Debugger listening on ws://127.0.0.1:36065/9492f253-abbe-409b-b76d-178e0321aa1b
Debugger attached.
path.js:28
    throw new TypeError('Path must be a string. Received ' + inspect(path));
    ^

TypeError: Path must be a string. Received undefined
    at assertPath (path.js:28:11)
    at Object.join (path.js:489:7)
    at SQLBuilder._register (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\json-sql-builder2\lib\builder.js:485:33)
    at _.forEach (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\json-sql-builder2\lib\builder.js:459:10)
    at arrayEach (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\lodash\lodash.js:516:11)
    at Function.forEach (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\lodash\lodash.js:9342:14)
    at SQLBuilder._loadModules (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\json-sql-builder2\lib\builder.js:457:5)
    at new SQLBuilder (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\json-sql-builder2\lib\builder.js:48:8)
    at Object.<anonymous> (c:\Users\hidori\GitHub\node-json-sql-builder2-test\index.js:2:13)
    at Module._compile (module.js:649:14)
Waiting for the debugger to disconnect...
TypeError: Path must be a string. Received undefined
path.js:28
    at assertPath (path.js:28:11)
    at Object.join (path.js:489:7)
    at SQLBuilder._register (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\json-sql-builder2\lib\builder.js:485:33)
    at _.forEach (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\json-sql-builder2\lib\builder.js:459:10)
    at arrayEach (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\lodash\lodash.js:516:11)
    at Function.forEach (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\lodash\lodash.js:9342:14)
    at SQLBuilder._loadModules (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\json-sql-builder2\lib\builder.js:457:5)
    at new SQLBuilder (c:\Users\hidori\GitHub\node-json-sql-builder2-test\node_modules\json-sql-builder2\lib\builder.js:48:8)
    at Object.<anonymous> (c:\Users\hidori\GitHub\node-json-sql-builder2-test\index.js:2:13)
    at Module._compile (module.js:649:14)

So, builder.js contains following codes:

		this.currentModulePath = path.join('../sql/', file.split('/sql')[1]);

		op.sqlPath = './' + this.currentModulePath.split('/sql/')[1];

these are NOT GOOD for Windows.

On Windows, the file path is separated by \. not /.

need to write/create sqls with all the possible complex constructs in a single/same sql.

Example as follows:

select distinct
cast(NULL as string) as c1
,case when t1.c2 is null then lower(trim(t2.c1)) else lower(trim(t1.c2)) end as c2
,regexp_replace(t2.c2,"^0+","") as c3
,t2.c4 as c4
,cast(NULL as string) as c4
,from_unixtime(unix_timestamp(t4.c6,'dd/MMM/yyyy'),'yyyy-MM-dd') as c5

from
db.t21 t2

left outer join db.t1 on

lower(TRIM(t1.c1)) = lower('XYZS') AND
lower(TRIM(t2.c1)) = lower(TRIM(t1.c1))

left outer join
(select ---------------------)

max(case when c1 = 'value' then c1 end) as c1 from db.t1

do we have relevant scenarios covered in json-sql-builder2?
does it support all these?
Awiating response!
Thanks!

Thanks for the wonderful library.
What is the best way to define the call to the psql [postgresql] function using json-sql-builder2?

for example, we would like to call

select * from analytics.compute_salary($1, $2, $3)

where as $1,$2,$3 might be values/params in json-sql-builder2.

Any help would be helpful here.

i want to cast any column type while using it in
where.

E.g.
Select * from table1
Where cast(date) = '2019-09-01'

E.g. avg(...) over ( partition by ...)

This JSON:

{
    "$select": {
        "$column1": true,
        "$from": "tbl1"
    },
    "$view": "view1",
    "$ine": true
}

Produces the following SQL:

CREATE VIEW  IF NOT EXISTSview1 AS SELECT column1 FROM tbl1

Quick question, do you support a mysql query like this:
select * from event where data->"$.foo" = 'bar'

The data column is of type JSON, I tried a few different approaches through the json builder, but no success.

Hi, I'm new to json-sql-builder2 :-)

I'm trying to write the flowing query by JSON

select
	*
from
	label, label_article_rel
where
	label.label_id = label_article_rel.label_id

I wrote the following JSON

const SqlBuilder = require('json-sql-builder2');
const builder= new SqlBuilder('MySQL');
const query = builder.$select({
    $from: ['label', 'label_article_rel'],
    $where: {
        'label.label_id': 'label_article_rel.label_id',
    }});

It's fails. the error is:

Using Helper '$from' must be type of 'Object, String', but got Type 'Array' with value '["label","label_article_rel"]'

when i use to select with “$orderBy”:"id desc"
it become to id desc!!!

Hello, we have a use case, in which we have to divide a column with an integer value. To do that I am not able to find any arithmetic helper. There exists two for addition and multiplication, but we also have to apply division operation on a column.

Example: Select * from column1 * 1, column2 / 2 from Table

Kindly let me know if there is any way to achieve this. Thanks

What's the easiest way to run select to_timestamp(foo.datetime) from foo?Is there way to pass a raw "to_timestamp(foo.datetime)" expression? Same question about now()

Is there any support, or planned support for unsigned values currently?

I've searched through the code and I'm not finding any implementation.

how can we add 'and' 'or' and 'not' condition in where clause for example

Select * from 'table_name' where date >= ' ' and date <= ' '
Select * from 'table_name' where number = 5 or number = 10
Select * from 'table_name' where date != 6

SQLite does not support the CONCAT() function, so $concat does not work.
Instead, the SQL implementation for CONCAT should seperate strings with pipes.

So MySQL:

SELECT
    CONCAT("one", "two", column1)
FROM
    ...

Should be:

SELECT
    "one" || "two" || column1
FROM
    ...

We need to add a virtual column in select statement while composing the sql query.

Use Case:
We are trying to build a query using postgresql dialect. The query then will be executed on AWS Athena Service. And in case of virtual columns athena requires string values in single qoutes i.e. 'value' as "Column_Name". So the issue is json-sql-builder2 does not add single qoutes for the value of virtual column. Let me know if there is any way. Thanks

Hi,

fantastic work on this library, it really helps me.

I noticed that for the oracle dialect there is a syntax error in the aliasing of tables (for from, and join clauses). In oracle tables are aliased without the "AS" keyword (https://www.techonthenet.com/oracle/alias.php) but json-sql-builder2 doesn't seem to take this into account.

I'm looking at the doc to implement a PR with this modification for the oracle dialect.

How can I output the raw text query without having to rely on separate software to reconstruct it from sql and values (that is arguments) array?

exists is probably the most common

• https://www.postgresql.org/docs/11/functions-subquery.html

Superficially looks like a function. Any hints on crossdialect syntax?

promising library.

is there a preexisting sql statement or .sql file parser into a SQLBuilder object? How do I do it? A builder without a preexisting parser may be a incomplete for me here

Hi. The documentation doesn't contains information about how I put the user, password, database, etc. Can you help me?

const SQLBuilder = require('json-sql-builder2');
// create a new instance of the SQLBuilder and load the language extension for mysql
var sql = new SQLBuilder('MySQL');

// lets start some query fun
var totalSalary = sql.$select({
    job_title: true,
    total_salary: { $sum: 'salary' },
    $from: 'people',
    $where: {
        job_title: { $in: ['Sales Manager', 'Account Manager'] },
        age: { $gte: 18 },
        country_code: 'US',
    },
    $groupBy: 'job_title',
});

comma missing in total_salary line

const SQLBuilder = require('json-sql-builder2');

const sql = new SQLBuilder('SQLServer');
sql.$select({ job_title: true, total_salary: { $sum: 'salary' }, $from: 'people', $where: { job_title: { $in: ['Sales Manager', 'Account Manager'] }, age: { $gte: 18 }, country_code: 'US', }, $groupBy: 'job_title', })
Converted to

{"sql":"SELECT job_title, SUM(salary) AS total_salary FROM people WHERE job_title IN (@param1, @param2) AND age >= @param3 AND country_code = @param4 GROUP BY job_title","values":{"param1":"Sales Manager","param2":"Account Manager","param3":18,"param4":"US"}}