Jd/Ops read

From J Wiki
Jump to: navigation, search
Jd | Overview | General | Ops | Admin | Guide | Technical | Release | License | Support


key [/in] table pairs

return rows in table for pairs.

elided /n returns last row in table that matches each data row and _1 if no match

with /in all rows in table are returned that match any data row

col types int1/int2/int4/float/varbyte not allowed

NB. example key
jd'gen test f 5'
jd'key f';'int';104 102
jd'key f';'int';104 102 120
jd'key     f';'int';104 102 120;'byte';'ECx'  NB. data order - _1 for not found
jd'key /in f';'int';104 102 120;'byte';'ECx'  NB. sorted - no_1
jd'key     f';'boolean';1 NB. last row that matches
jd'key /in f';'boolean';1 NB. all rows that match
jdaccess olddan


Same as reads with /lr option


reads [options]   [ SELECT ]   from FROM  [where WHERE ] [order by ORDER BY]
  /lr - labeled rows instead of labeled cols
  /e - edate... cols as epochdt rather than iso 8601

parsing read ops can be complicated

returns select col(s) from table(s) with rows that satisfy where

aggregation [by key1[,key2]]

For ideas on working with reads results, see essay Inverted Table.

NB. example reads
jd'gen ref2 a 3 1 b 2'
jd'reads from a'
jd'reads from b'
jd'reads akey,adata from a'
jd'reads from a,a.b'
jd'reads from a where akey<3'
jd'reads from a,a.b where akey<3 and b.bref=0'
jd'reads asdf:b.bb12 from a,a.b where akey<3 and b.bref=0'
jd'reads sum adata from a where akey>0'
jd'reads sum adata by aref from a'
jdaccess olddan


readtc ... jdtc sentence

Same as reads except for the jdtc clause.

J sentence is run first to create temp cols.

See tutorial tempcol.


Parsing read ops can be complicated. There are known issues that can cause problems. Keep things simple to avoid unknown problems.

Clauses where, order by, jdset, jdtc are stripped from the end and must appear, if present, in that order.

Use blanks before and after keywords.

Anything can appear in WHERE between quotes ("s). This is handled and will not cause parse errors.

Anything can appear in col/table alias. / at start of SELECT will be treated as an option. Keywords (e.g. order by) in alias can cause problems.

Col alias can include blanks, RESERVEDCHARS, and RESERVEDWORDS and this is useful as they are result col labels.

Table alias can include blanks, RESERVEDCHARS, and RESERVEDWORDS but this is not useful and can cause parse problems.

Some problems are avoided as:

RESERVEDWORDS_jd_ are not allowed as col/table names
RESERVEDCHARS_jd_ are not allowed in col/table names


read SELECT from ...

Select determines which columns are in the result:

  sum cola
 sum cola,avg colb
 sum cola by key1
 sum cola by key1,key2
   jd'info agg' NB. aggregation functions
Select: Column(, Column)*
      | AggCol(, AggCol)*
      | AggCol(, AggCol)* by Column(, Column)*
Column: [alias:] [Tab.]Col
AggCol: [alias:] agg [Tab.]Col
Tab: table  | "*"
Col: column | "*"

Tab is name of a table in the from clause
(alias, name, or the column used to reference it)
root table is used if there is no TAB

Col is name of a column in the corresponding table

alias is a name

agg is an aggregation function

if not an aggregation then * can be used for Tab and Col
if Tab is *, then that column will be shown for all tables
if Col is *, then all columns from the table will be used
Tab expanded first so *.* gives all columns from all tables
If no Columns are given, then *.* is used, or * if only one table

result name for a col is its alias, or how it was identified

by keyword groups rows by the key columns that follow it
each aggregator is called on the rows of its column in each group

result col label does not include agg, so an alias is desirable


A.B indicates A is joined to B by the A ref col that refers to B

read ... from FROM ...
read ... from A , A.B ...
read ... from A,A.jdref_id_B_id ... NB. distinguish multiple refs
read ... from A , A.B , A.C ...
read ... from A , A.B , B.D ...
read ... from A.B , A ...           NB. order does not matter
read ... from X:A , X.B , X.C ...   NB. X alias used in all subsequent uses
A.B is left1 join
 each A row with 1 matching B row or FILLS
 if multiple matching B rows, the last one is used
 not a standard join, but is fast and adequate in many cases
 same as inner join if 1 and only 1 B row matches
A>B is left join
 same as outer except B rows that did not match are excluded
 same as left1 except all B rows that match are included
A-B is inner join
 same as left except rows with FILLS are excluded
 same as outer except rows with FILLS are excluded
A=B is outer join - this join is not implemented
  all possible rows
  A rows duplicated for each match in B
  B rows duplicated for each match in A
  A or B FILLS provided where no match
A<B is right join - this join is not implemented
  same as outer except A rows that did not match are excluded 

joins are done in an order based on a sampling that picks representative rows and tests how restrictive the queries are

read ... from exact f,f.g,g.h where h.a<100' NB. exact to use the order as given

See tutorials from and join


read ... from ... where WHERE

 b in (23,24,25)
 b lt 23 && a eq "abc"
 a like "ab*" 
 f.a=23 && g.b="def"
 a range (1000,2000)
 a=1 and b=2
 a,b = 1,2          - same as above but uses a,b hash if available
 (a=1 and b=2) or (a=3 and b=4)
 a,b in (1,2),(3,4) - same as above but uses a,b hash if available


 >= <= <> =  >  <<br/>
 ge le ne eq gt lt range<br/>
 in notin like unlike sample sample.
 sample n returns n random rows<br/>
 sample. users ?. instead of ?

 non-symbol must be blank delimited

 byte data must be in "s and has C escapes (\" \t \n \0 \x09 ...)<br/>
 numeric data must not be in "s

 like/unlike regex applies to all types

 relationals have implied parens (which must not be present)
 a=c (c not a number and not in "s) is a relation between 2 cols

 t (no col/relational) selects rows from table t not created by join


 !   &&  ||<br/>
 not and or
  • must be blank delimited
  • logicals evaluate as J (right to left):
  not a=23 && b=24 || b=25  ===  not((a=23)&&((b=24)||(b=25)))

parens can change the order of evaluation


W:  ("!" | "not ")* W1
W1: W2 (" " ("and"|"or"|"&&"|"||") " " W)?
W2: "(" W ")"  |  Q
Q:  col relational dat  |  col relational col  |  tab

col is col name as in select and tab is table name

Q expressions are in implicit parens, which must not be present.

dat represents a J noun; typically this must have the same type as the items of the column, but its type depends on the operation being used. If dat is a string or byte, it must be quoted; otherwise it will be interpreted as numeric data, a column name, or an error.

Strings are in "s and C escapes (\", \n, etc.) can be used.

Logicals (! $$ || not and or) are grouped from right to left as in J, with parentheses allowed for grouping.

The three types of simple query:
- For an operation on a col and data, the data is fixed so that it has the appropriate type and shape. For most operations, this is the same type and shape as an item in the column. For in and notin, its items will have that type and shape. For sample and sample., it will be converted to a single number. See the section on data conversion below. After conversion, the operation will be applied to select qualifying rows in the column.
- For an operation on two columns, the operation will be applied to pairs of rows from the two columns to select qualifying rows. The operations in, notin, like, unlike, sample, and sample. are not available on columns.
- The single table query selects rows that are not null. Null rows are created by some joins and do not correspond to a row in the table. They are indicated with fills.

The relationals:
- (>= <= > < range) are for numeric cols
- range arg is list of numbers, which is split into pairs if an odd number, the last range extends to _ matches are in the range(s) (inclusive)
- in and notin test whether col value is an element of the list
- like and unlike take a regex and test if the col data matches
- sample and sample. require a data argument that is a single number

Data can be converted either to string (or byte) or numbers.
Numbers must include only the characters -_. and digits.
Strings must be quoted to avoid confusion with keywords and col names.

Following escapes are allowed in quoted strings:

\"     double quote
\\     backslash
\a     alert (BEL)
\b     backspace
\e     escape
\f     form feed
\n     new line
\r     carriage return
\t     horizontal tab
\v     vertical tab
\xHH   byte with hexadecimal value HH

Following escapes may be supported but their use is discouraged:

\NNN        byte with octal value NNN (1 to 3 digits)
\xHH        byte with hexadecimal value HH (1 to 2 digits)
\uHHHH      Unicode  (ISO/IEC  10646)  character with hex value HHHH (4 digits)
\UHHHHHHHH  Unicode character with hex value HHHHHHHH (8 digits)

Data is adjusted to the shape of the column. A list of data can be provided by separating items with spaces (this works with numbers or strings). If the data is smaller than the column shape, it will be filled with the fill element corresponding to the column type. If it is larger, an error is signalled.

For in and notin, data is given in a comma-separated list, optionally enclosed by parens.

In Q expression of the form "col = dat" or "col in dat", col can be a comma-separated list of cols. In this case each item in dat is a comma-separated list with the same length as the number of cols. If the relational is "in", each item in dat must be parenthesized. The col values are compared with the items in dat. If there is a hash columns it will be used for the query.


One or more cols separated by commas used to sort the final result. Ascending by default. Add desc at end for descending.