"WHERE" clause needs an explicit function

[neilotoole]

Is your feature request related to a problem? Please describe.

The current mechanism to implement the WHERE clause has got problems.

Currently, it's possible to write a sq query like this:

$ sq '.actor | .actor_id > 10 | .actor_id, .first_name'
               ^^^ WHERE ^^^^

This becomes a SQL query:

SELECT actor_id, first_name FROM actor WHERE actor_id > 10

We want to be able to implement the ability to select literal values, e.g.

$ sq '.actor | true, .first_name, 77, "hello"'

This would become:

SELECT true, first_name, 77, 'hello' FROM actor

So far, so good. But these literals could instead be expressions:

$ sq '.actor | .first_name, actor_id, .actor_id > 2'

Which becomes:

SELECT first_name, actor_id, actor_id > 2 FROM actor;

Result:

PENELOPE,1,false
NICK,2,false
ED,3,true
JENNIFER,4,true

Let's simplify that query to this:

$ sq '.actor | .actor_id > 2'

Here's the problem. That query is ambiguous. sq can't tell which of these two SQL queries is wanted:

SELECT actor_id > 2 FROM actor

-- or

SELECT * FROM actor WHERE actor_id > 2

Describe the solution you'd like

The WHERE clause mechanism needs to be explicit.

$ sq '.actor | where(.actor_id > 2) | .actor_id > 2'

This would translate to:

SELECT actor_id > 2 FROM actor WHERE actor_id > 2

Design choice: where() vs select()

The where() mechanism described above is pretty much ideal. There's one fly in the ointment though. It is a primary design goal for the SLQ query language to imitate jq's query language where possible. And jq already has a similar mechanism: select()

$ jq '.[] | select(.id == "second")'

Ordinarily, we would just go with select() to align with jq. However, this is semantically ugly. It is unfortunate that jq chose select() instead of where(). For users coming from a SQL background, select means "SELECT these columns", not "select the rows that match this expression". That is to say, it seems likely that implementing select() to mean WHERE may confuse sq users.

As an unsatisfactory compromise, the first implementation will probably use where(), but will also allow the synonym select(). But I'm open to feedback on this.

[neilotoole]

Implemented in v0.38.0.

[331000738]

It's not a good suggestion where(...)It's too ugly.This suggestion should not be adopted.Should: 1. first test if there is a conditional statement, if there is, all the conditions are put together and must be in the front 2. if it so happens that there is only one field that you want to check, and the field is a comparative expression, figure out how to solve it by yourself, e.g.: @db '.table | true | .id>100' Don't see why you should complicate a simple problem.Ease of use is key for software.