Add SQL-style operators (#1322)

4 files changed, 125 insertions, 0 deletions

diff --git a/docs/content/3.manual/manual.yml b/docs/content/3.manual/manual.yml
index 94aa2476..ae7a9d39 100644
--- a/docs/content/3.manual/manual.yml
+++ b/docs/content/3.manual/manual.yml
@@ -1835,6 +1835,46 @@ sections:
             input: '"2015-03-05T23:51:47Z"'
             output: ['1425599507']
 
+      - title: "SQL-Style Operators"
+        body: |
+
+          jq provides a few SQL-style operators.
+
+           * INDEX(stream; index_expression):
+
+             This builtin produces an object whose keys are computed by
+             the given index expression applied to each value from the
+             given stream.
+
+           * JOIN($idx; stream; idx_expr; join_expr):
+
+             This builtin joins the values from the given stream to the
+             given index.  The index's keys are computed by applying the
+             given index expression to each value from the given stream.
+             An array of the value in the stream and the corresponding
+             value from the index is fed to the given join expression to
+             produce each result.
+
+           * JOIN($idx; stream; idx_expr):
+
+             Same as `JOIN($idx; stream; idx_expr; .)`.
+
+           * JOIN($idx; idx_expr):
+
+             This builtin joins the input `.` to the given index, applying
+             the given index expression to `.` to compute the index key.
+             The join operation is as described above.
+
+           * IN(s):
+
+             This builtin outputs `true` if `.` appears in the given
+             stream, otherwise it outputs `false`.
+
+           * IN(source; s):
+
+             This builtin outputs `true` if any value in the source stream
+             appears in the second stream, otherwise it outputs `false`.
+
   - title: Conditionals and Comparisons
     entries:
       - title: "`==`, `!=`"
diff --git a/jq.1.prebuilt b/jq.1.prebuilt
index 995b40e9..615b033c 100644
--- a/jq.1.prebuilt
+++ b/jq.1.prebuilt
@@ -2005,6 +2005,45 @@ jq \'strptime("%Y\-%m\-%dT%H:%M:%SZ")|mktime\'
 .
 .IP "" 0
 .
+.SS "SQL\-Style Operators"
+jq provides a few SQL\-style operators\.
+.
+.TP
+INDEX(stream; index_expression):
+.
+.IP
+This builtin produces an object whose keys are computed by the given index expression applied to each value from the given stream\.
+.
+.TP
+JOIN($idx; stream; idx_expr; join_expr):
+.
+.IP
+This builtin joins the values from the given stream to the given index\. The index\'s keys are computed by applying the given index expression to each value from the given stream\. An array of the value in the stream and the corresponding value from the index is fed to the given join expression to produce each result\.
+.
+.TP
+JOIN($idx; stream; idx_expr):
+.
+.IP
+Same as \fBJOIN($idx; stream; idx_expr; \.)\fR\.
+.
+.TP
+JOIN($idx; idx_expr):
+.
+.IP
+This builtin joins the input \fB\.\fR to the given index, applying the given index expression to \fB\.\fR to compute the index key\. The join operation is as described above\.
+.
+.TP
+IN(s):
+.
+.IP
+This builtin outputs \fBtrue\fR if \fB\.\fR appears in the given stream, otherwise it outputs \fBfalse\fR\.
+.
+.TP
+IN(source; s):
+.
+.IP
+This builtin outputs \fBtrue\fR if any value in the source stream appears in the second stream, otherwise it outputs \fBfalse\fR\.
+.
 .SH "CONDITIONALS AND COMPARISONS"
 .
 .SS "==, !="
diff --git a/src/builtin.jq b/src/builtin.jq
index 9f6e656c..223cdcad 100644
--- a/src/builtin.jq
+++ b/src/builtin.jq
@@ -287,3 +287,20 @@ def walk(f):
   elif type == "array" then map( walk(f) ) | f
   else f
   end;
+
+# SQL-ish operators here:
+def INDEX(stream; idx_expr):
+  reduce stream as $row ({};
+    .[$row|idx_expr|
+      if type != "string" then tojson
+      else .
+      end] |= $row);
+def INDEX(idx_expr): INDEX(.[]; idx_expr);
+def JOIN($idx; idx_expr):
+  [.[] | [., $idx[idx_expr]]];
+def JOIN($idx; stream; idx_expr):
+  stream | [., $idx[idx_expr]];
+def JOIN($idx; stream; idx_expr; join_expr):
+  stream | [., $idx[idx_expr]] | join_expr;
+def IN(s): reduce (first(select(. == s)) | true) as $v (false; if . or $v then true else false end);
+def IN(src; s): reduce (src|IN(s)) as $v (false; if . or $v then true else false end);
diff --git a/tests/jq.test b/tests/jq.test
index 302952d5..fa02b6d7 100644
--- a/tests/jq.test
+++ b/tests/jq.test
@@ -1322,3 +1322,32 @@ jq: error: syntax error, unexpected INVALID_CHARACTER, expecting $end (Unix shel
 (.[{}] = 0)?
 null
 
+INDEX(range(5)|[., "foo\(.)"]; .[0])
+null
+{"0":[0,"foo0"],"1":[1,"foo1"],"2":[2,"foo2"],"3":[3,"foo3"],"4":[4,"foo4"]}
+
+JOIN({"0":[0,"abc"],"1":[1,"bcd"],"2":[2,"def"],"3":[3,"efg"],"4":[4,"fgh"]}; .[0]|tostring)
+[[5,"foo"],[3,"bar"],[1,"foobar"]]
+[[[5,"foo"],null],[[3,"bar"],[3,"efg"]],[[1,"foobar"],[1,"bcd"]]]
+
+range(5;10)|IN(range(10))
+null
+true
+true
+true
+true
+true
+
+range(10;12)|IN(range(10))
+null
+false
+false
+
+IN(range(10;20); range(10))
+null
+false
+
+IN(range(5;20); range(10))
+null
+true
+