AdvancedJsonExamples.md

Advanced JSON

Redis JSON array filtering examples

Contents

1. Business Value Statement
2. Data Set
3. Data Loading
4. Array Filtering Examples
   1. All Properties of Array
   2. All Properties of a Field
   3. Relational - Equality
   4. Relational - Less Than
   5. Relational - Greater Than or Equal
   6. Logical AND
   7. Logical OR
   8. Regex - Contains Exact
   9. Regex - Contains, Case Insensitive
   10. Regex - Begins With

Business Value Statement

The ability to query within a JSON object unlocks further value to the underlying data. Redis supports JSONPath array filtering natively.

Data Set

{
    "city": "Boston",
    "location": "42.361145, -71.057083",
    "inventory": [
        {
            "id": 15970,
            "gender": "Men",
            "season":["Fall", "Winter"],
            "description": "Turtle Check Men Navy Blue Shirt",
            "price": 34.95
        },
        {
            "id": 59263,
            "gender": "Women",
            "season": ["Fall", "Winter", "Spring", "Summer"],
            "description": "Titan Women Silver Watch",
            "price": 129.99
        },
        {
            "id": 46885,
            "gender": "Boys",
            "season": ["Fall"],
            "description": "Ben 10 Boys Navy Blue Slippers",
            "price": 45.99
        }
    ]
}

Data Loading

JsonCommands json = db.JSON();
json.Set("warehouse:1", "$", new {
    city = "Boston",
    location = "42.361145, -71.057083",
    inventory = new[] {
        new {
            id = 15970,
            gender = "Men",
            season = new[] {"Fall", "Winter"},
            description = "Turtle Check Men Navy Blue Shirt",
            price = 34.95
        },
        new {
            id = 59263,
            gender = "Women",
            season = new[] {"Fall", "Winter", "Spring", "Summer"},
            description = "Titan Women Silver Watch",
            price = 129.99
        },
        new {
            id = 46885,
            gender = "Boys",
            season = new[] {"Fall"},
            description =  "Ben 10 Boys Navy Blue Slippers",
            price = 45.99
        }
    }
});

Array Filtering Examples

Syntax

JSON.GET

All Properties of Array

Fetch all properties of an array.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[*]",
    indent: "\t",
    newLine: "\n"
));

Result

[
        {
                "id":15970,
                "gender":"Men",
                "season":[
                        "Fall",
                        "Winter"
                ],
                "description":"Turtle Check Men Navy Blue Shirt",
                "price":34.95
        },
        {
                "id":59263,
                "gender":"Women",
                "season":[
                        "Fall",
                        "Winter",
                        "Spring",
                        "Summer"
                ],
                "description":"Titan Women Silver Watch",
                "price":129.99
        },
        {
                "id":46885,
                "gender":"Boys",
                "season":[
                        "Fall"
                ],
                "description":"Ben 10 Boys Navy Blue Slippers",
                "price":45.99
        }
]

All Properties of a Field

Fetch all values of a field within an array.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[*].price",
    indent: "\t",
    newLine: "\n"
));

Result

[
        34.95,
        129.99,
        45.99
]

Relational - Equality

Fetch all items within an array where a text field matches a given value.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[?(@.description==\"Turtle Check Men Navy Blue Shirt\")]",
    indent: "\t",
    newLine: "\n"
));

Result

[
        {
                "id":15970,
                "gender":"Men",
                "season":[
                        "Fall",
                        "Winter"
                ],
                "description":"Turtle Check Men Navy Blue Shirt",
                "price":34.95
        }
]

Relational - Less Than

Fetch all items within an array where a numeric field is less than a given value.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[?(@.price<100)]",
    indent: "\t",
    newLine: "\n"
));

Result

[
        {
                "id":15970,
                "gender":"Men",
                "season":[
                        "Fall",
                        "Winter"
                ],
                "description":"Turtle Check Men Navy Blue Shirt",
                "price":34.95
        },
        {
                "id":46885,
                "gender":"Boys",
                "season":[
                        "Fall"
                ],
                "description":"Ben 10 Boys Navy Blue Slippers",
                "price":45.99
        }
]

Relational - Greater Than or Equal

Fetch all items within an array where a numeric field is greater than or equal to a given value.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[?(@.id>=20000)]",
    indent: "\t",
    newLine: "\n"
));

Result

[
        {
                "id":59263,
                "gender":"Women",
                "season":[
                        "Fall",
                        "Winter",
                        "Spring",
                        "Summer"
                ],
                "description":"Titan Women Silver Watch",
                "price":129.99
        },
        {
                "id":46885,
                "gender":"Boys",
                "season":[
                        "Fall"
                ],
                "description":"Ben 10 Boys Navy Blue Slippers",
                "price":45.99
        }
]

Logical AND

Fetch all items within an array that meet two relational operations.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[?(@.gender==\"Men\"&&@.price>20)]",
    indent: "\t",
    newLine: "\n"
));

Result

[
        {
                "id":15970,
                "gender":"Men",
                "season":[
                        "Fall",
                        "Winter"
                ],
                "description":"Turtle Check Men Navy Blue Shirt",
                "price":34.95
        }
]

Logical OR

Fetch all items within an array that meet at least one relational operation. In this case, return only the ids of those items.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[?(@.price<100||@.gender==\"Women\")].id",
    indent: "\t",
    newLine: "\n"
));

Result

[
        15970,
        59263,
        46885
]

Regex - Contains Exact

Fetch all items within an array that match a given regex pattern.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[?(@.description =~ \"Blue\")]",
    indent: "\t",
    newLine: "\n"
));

Result

[
        {
                "id":15970,
                "gender":"Men",
                "season":[
                        "Fall",
                        "Winter"
                ],
                "description":"Turtle Check Men Navy Blue Shirt",
                "price":34.95
        },
        {
                "id":46885,
                "gender":"Boys",
                "season":[
                        "Fall"
                ],
                "description":"Ben 10 Boys Navy Blue Slippers",
                "price":45.99
        }
]

Regex - Contains, Case Insensitive

Fetch all items within an array where a field contains a term, case insensitive.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[?(@.description =~ \"(?i)watch\")]",
    indent: "\t",
    newLine: "\n"
));

Result

[
        {
                "id":59263,
                "gender":"Women",
                "season":[
                        "Fall",
                        "Winter",
                        "Spring",
                        "Summer"
                ],
                "description":"Titan Women Silver Watch",
                "price":129.99
        }
]

Regex - Begins With

Fetch all items within an array where a field begins with a given expression.

Command

Console.WriteLine(json.Get(key: "warehouse:1",
    path: "$.inventory[?(@.description =~ \"^T\")]",
    indent: "\t",
    newLine: "\n"
));

Result

[
        {
                "id":59263,
                "gender":"Women",
                "season":[
                        "Fall",
                        "Winter",
                        "Spring",
                        "Summer"
                ],
                "description":"Titan Women Silver Watch",
                "price":129.99
        }
]

*** Lab 4 - Regex - Begins With ***
[
        {
                "id":15970,
                "gender":"Men",
                "season":[
                        "Fall",
                        "Winter"
                ],
                "description":"Turtle Check Men Navy Blue Shirt",
                "price":34.95
        },
        {
                "id":59263,
                "gender":"Women",
                "season":[
                        "Fall",
                        "Winter",
                        "Spring",
                        "Summer"
                ],
                "description":"Titan Women Silver Watch",
                "price":129.99
        }
]