Select – Nextra

SELECT Statement

<query> ::= SELECT <select_list>
            FROM <from_item>
            [ JOIN <join_clause> ]
            [ WHERE <condition> ]
            [ ORDER BY <order_clause> ]
            [ TYPE <data_type> ]

<select_list> ::= <column> [ AS <alias> ] [, <column> [ AS <alias> ] ] | <subquery> AS <alias> [, <select_list>]

<column> ::= <table>.<column_name>

<alias> ::= identifier

<from_item> ::= <table_name> [ AS <alias> ]

<join_clause> ::= <join_type> JOIN <table_name> [ AS <alias> ] 
                  ON <join_condition>

<join_type> ::= INNER | LEFT | RIGHT | FULL

<join_condition> ::= <expression> [ AND <expression> ]

<expression> ::= <column> = <value>
               | <column> = ( <subquery> )
               | <column> <operator> <value>
               | <column> <operator> ( <subquery> )
               | <expression> AND <expression>
               | <expression> OR <expression>

<subquery> ::= SELECT <select_list>
               FROM <from_item>
               [ WHERE <condition> ]
               [ LIMIT <limit_value> ]

<condition> ::= <expression> [ AND <expression> ]

<operator> ::= = | != | > | < | >= | <= | LIKE

<order_clause> ::= <column> [ ASC | DESC ] [, <order_clause> ]

<table_name> ::= dictionary.page_property | dictionary.user_property | dictionary.parents | dictionary.sitemap | registers.remains | contract.uniswapv2factory

<column_name> ::= ref | name | user_ref | id | object | state | nav | far | pvalue | pair | tokenA | tokenB | timestamp | block

<value> ::= literal | &admin | &data | hex_literal | date_literal

<limit_value> ::= integer

<data_type> ::= object | list

<user_variable> ::= { "key": "value" }  // User-defined variable passed in JSON format

Explanation

<select_list>:

Added the ability to select a subquery and assign it an alias. For example:

(SELECT d.ref AS ref, d.nav AS nav, d.far AS far, d.pvalue AS pvalue FROM registers.remains AS d WHERE pdate = CURRENT_DATE) AS pdata

Now nested subqueries can be used in SELECT for data selection.

<column_name>:
- Added additional column names, such as pair, tokenA, tokenB, timestamp, block, to account for data selection from the new table contract.uniswapv2factory.
<table_name>:
- Added the new table contract.uniswapv2factory to describe the data used in the query.
<value>:
- Included values hex_literal and date_literal, as they represent hexadecimal values and date formats that can be passed in the query.
<order_clause>:
- Added an ORDER BY clause to define how the results should be sorted.
<data_type>:
- Included list as a possible data type, as specified in the query.
<user_variable>:
- Added a <user_variable> to represent user-defined variables passed in JSON format.

Example Query 1

SELECT p.ref AS ref,
       p.name AS name,
       &data.ref AS data_ref,
       (SELECT d.ref AS ref, d.nav AS nav, d.far AS far, d.pvalue AS pvalue
        FROM registers.remains AS d
        WHERE pdate = CURRENT_DATE) AS pdata,
       p.pvalue AS pvalue
FROM dictionary.page_property AS p
INNER JOIN dictionary.user_property AS u
    ON u.ref = p.user_ref
    AND u.name = 'ivan'
    AND u.parent_ref = (
        SELECT p.ref
        FROM dictionary.parents AS p
        WHERE p.id = 100
        LIMIT 1 )
WHERE p.object = (
    SELECT s.ref
    FROM dictionary.sitemap AS s
    WHERE s.name = &admin )
AND p.state = 10;

Example Query 2

SELECT uv.pair,
       uv.tokenA,
       uv.tokenB,
       uv.timestamp,
       uv.block
FROM contract.uniswapv2factory AS uv
WHERE uv.tokenA = '0xdac17f958d2ee523a2206206994597c13d831ec7'::HEX
  AND uv.timestamp > '01.01.2019'::DATE('DD.MM.YYYY')
ORDER BY uv.timestamp ASC,
         uv.block DESC
TYPE list;

Example Query 3

SELECT tb.parser.token0,tb.parser.pair,tb.address FROM
(SELECT parse("0xC0AEe478e3658e2610c5F7A4A2E1777cE9e4f2Ac",trn.topics,trn.data) as parser,
trn."timestamp",trn."contractAddress",
trn."from",
trn.address,
trn.selector
    FROM Ethereum.transactions.log(WHERE "to"="0xC0AEe478e3658e2610c5F7A4A2E1777cE9e4f2Ac" LIMIT 200) as trn
    TYPE LIST) as tb 
 
TYPE LIST;

SQL Commands overview Insert