JSON Expressions

Source Dereference () The source dereference operator can be used to specify columns by dereferencing the source stream or table. The -> dereference selects a key in a nested JSON object.

SELECT demo.age FROM demo
{"age" : 37}

SELECT demo.name->first FROM demo
{"first" : "Tom"}

SELECT name->first AS fname FROM demo
{"fname": "Tom"}

Index Expressions allow you to select a specific element in a list. It should look similar to array access in common programming languages.The index value starts with 0, -1 is the starting position from the end, and so on.

SELECT children FROM demo
{
    "children": ["Sara","Alex","Jack"]
}

Slices allow you to select a contiguous subset of an array.

SELECT children[0:1] FROM demo
{
    "children": ["Sara"]
}
SELECT children[1:-1] FROM demo
{
    "children": ["Alex"]
}
SELECT children[0:-1] FROM demo
{
    "children": ["Sara","Alex"]
}

SELECT children[:] FROM demo == SELECT children FROM demo
{
    "children": ["Sara","Alex","Jack"]
}

SELECT children[:2] FROM demo
{
    "children": ["Sara","Alex"]
}

SELECT followers->Group1[:1]->first FROM demo
{
    "first": ["John"]
}

Json Path functions

eKuiper provides a list of functions to allow to execute json path over struct or array columns or values. The functions are:

Please refer to for detail.

All these functions share the same parameter signatures, among which, the second parameter is a jsonpath string. The jsonpath grammer used by eKuiper is based on JsonPath.

• Dots . to move into a tree
• Brackets [] for access to a given array member coupled with a position. It can also access to a map field.

So for example, when applied to the previous JSON data sample we can reach the following parts of the tree with these expressions:

• refers to 37.
• $.friends.first refers to “dale”.
• $.friends refers to the full array of friends.
• $.friends[0] refers to the first friend listed in the previous array (contrary to arrays members are zero-based).
• $.friends[0][lastname] refers to the lastname of the first friend listed. Use bracket if or special characters (such as space ‘ ‘, ‘.’ and Chinese etc) in the field key.
• $.friends[? @.age>60].first or $.friends[? (@.age>60)].first refers to the first name of the friends whose age is bigger than 60. Notice that the space between ? and the condition is required even the condition is with braces.

Developers can use the json functions in the SQL statement. Here are some examples.

• Select the lastname of group1 followers ```sql SELECT json_path_query(followers, “$.Group1[*].last”) FROM demo

[“Shavor”,”Miller”]

- Select the lastname if any of the group1 followers is older than 60
```sql
SELECT name->last FROM demo where json_path_exists(followers, "$.Group1[? @.age>30]")
"Anderson"

• Select the follower’s lastname from group1 whose age is bigger than 30 ```sql SELECT json_path_exists(followers, “$.Group1[? @.age>30].last”) FROM demo

- Assume there is a field in follows with reserved words or chars like dot `my.follower`, use bracket to access it.
```sql
SELECT json_path_exists(followers, "$[\"my.follower\"]") FROM demo
["Miller"]

List & Slice projections

A wildcard expression creates a list projection, which is a projection over a JSON array.

SELECT demo.friends[*]->first FROM demo
{
    "first": ["Dale", "Roger", "Jane"]
}

SELECT friends[:1]->first FROM demo
{
}

Object projections