Operator
values — emit values from expressions
Synopsis
[values] <expr> [, <expr>...]
Description
The values operator produces output values by evaluating one or more
comma-separated
expressions on each input value and sending each result to the output
in left-to-right order. Each <expr> may be any valid
expression.
The input order convolved with left-to-right evaluation order is preserved at the output.
The values operator name is optional since it can be used as a
shortcut. When used as a shortcut, only one expression
may be present.
The values abstraction is also available as the SQL VALUES clause,
where the tuples that comprise
this form must all adhere to a common type signature.
The pipe form of values here is differentiated from the SQL form
by the absence of parenthesized expressions in the comma-separated list
of expressions, i.e., the expressions in a comma-separated list never
require top-level parentheses and the resulting values need not conform
to a common type.
The values operator is a go to tool in SuperSQL queries as it allows
the flexible creation of arbitrary values from its inputs while the
SQL VALUES clause is a go to building block for creating constant tables
to insert into or operate upon a database. That said, the SQL VALUES clause
can also be comprised of dynamic expressions though it is less often used
in this fashion. Nonetheless, this motivated the naming of the more general
SuperSQL values operator.
For example, this query uses SQL VALUES to
create a static table called points then operate upon
each row of points using expressions embodied in
dynamic VALUES subqueries placed in a lateral join as follows:
WITH points(x,y) AS (
VALUES (2,1),(4,2),(6,3)
)
SELECT vals
FROM points CROSS JOIN LATERAL (VALUES (x+y), (x-y)) t(vals)
which produces
3
1
6
2
9
3
Using the values pipe operator, this can be written simply as
values {x:2,y:1},{x:4,y:2},{x:6,y:3}
| values x+y, x-y
Examples
Hello, world
# spq
values "hello, world"
# input
# expected output
"hello, world"
Values evaluates each expression for every input value
# spq
values 1,2
# input
null
null
null
# expected output
1
2
1
2
1
2
Values typically operates on its input
# spq
values this*2+1
# input
1
2
3
# expected output
3
5
7
Values is often used to transform records
# spq
values [a,b],[b,a] | collect(this)
# input
{a:1,b:2}
{a:3,b:4}
# expected output
[[1,2],[2,1],[3,4],[4,3]]