JSON_EVALPATH

Applies a JSON path expression to a JSON column to return attribute values from a JSON column.

You can use this function as a SelectItem in a query to project attribute values from a JSON column. Include the function in a WHERE clause with an IS NOT NULL or IS NULL expression to filter query results based on whether the JSON path returns an attribute value.

Syntax

JSON_EVALPATH (json-column, json-path-expression ) 
json-column
The name of a table column of JSON datatype. See Data Types.
json-path-expression
A string-formatted JSON path (similar to an XPath for XML data) to apply to the referenced column. The path can use either dot notation or bracket notation as well as other syntax elements described in JSONPath - Xpath for JSON.

GemFire XD supports using the && or || logical operators in a json-path-expression. However, you cannot use both operators in the same expression.

The result of the function is the attribute value(s) in string format. To use the function in a WHERE clause to filter results, include the IS NOT NULL or IS NULL expression to test whether the JSON path matched any values in the column.

Example

Note: Use the gfxd MaximumDisplayWidth command to increase the display width before you query JSON columns. This helps to ensure that query results are not truncated.
Using the sample JSON data, extract one or more attribute values:
-- Create the sample table with JSON column
CREATE TABLE t1 (col1 INT, col2 JSON) PERSISTENT; 

–- Insert sample JSON data
INSERT INTO t1 VALUES ( 1, 
'{ "store": {
    "book": [
      { "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      },
      { "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99
      },
      { "category": "fiction",
        "author": "Herman Melville",
        "title": "Moby Dick",
        "isbn": "0-553-21311-3",
        "price": 8.99
      },
      { "category": "fiction",
        "author": "J. R. R. Tolkien",
        "title": "The Lord of the Rings",
        "isbn": "0-395-19395-8",
        "price": 22.99
      }
    ],
    "bicycle": {
      "color": "red",
      "price": 19.95
    }
  }
}' );

-- Return the authors of all books in the store
SELECT json_evalPath(col2, '$.store.book[*].author') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
Nigel Rees,Evelyn Waugh,Herman Melville,J. R. R. Tolkien

-- Return all authors
SELECT json_evalPath(col2, '$..author') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
Nigel Rees,Evelyn Waugh,Herman Melville,J. R. R. Tolkien

-- Return all items in the store (books, as well as the bicycle)
SELECT json_evalPath(col2, '$.store.*') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
{
  "book" : [ {
    "category" : "reference",
    "author" : "Nigel Rees",
    "title" : "Sayings of the Century",
    "price" : 8.95
  }, {
    "category" : "fiction",
    "author" : "Evelyn Waugh",
    "title" : "Sword of Honour",
    "price" : 12.99
  }, {
    "category" : "fiction",
    "author" : "Herman Melville",
    "title" : "Moby Dick",
    "isbn" : "0-553-21311-3",
    "price" : 8.99
  }, {
    "category" : "fiction",
    "author" : "J. R. R. Tolkien",
    "title" : "The Lord of the Rings",
    "isbn" : "0-395-19395-8",
    "price" : 22.99
  } ],
  "bicycle" : {
    "color" : "red",
    "price" : 19.95
  }
}


-- Return the price of all items in the store
SELECT json_evalPath(col2, '$.store..price') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
8.95,12.99,8.99,22.99,19.95

-- Return all attributes for the third book
SELECT json_evalPath(col2, '$..book[2]') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
{ "category" : "fiction", "author" : "Herman Melville", "title" : "Moby Dick", "isbn" : "0-553-21311-3", "price" : 8.99 }

-- Return attributes for the last book in the order
SELECT json_evalPath(col2, '$..book[(@.length-1)]') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
{
  "category" : "fiction",
  "author" : "J. R. R. Tolkien",
  "title" : "The Lord of the Rings",
  "isbn" : "0-395-19395-8",
  "price" : 22.99
}

-- Return attributes for the first two books
SELECT json_evalPath(col2, '$..book[0,1]') FROM T1 WHERE COL1 = 1;
-- or
SELECT json_evalPath(col2, '$..book[:2]') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
{
  "category" : "reference",
  "author" : "Nigel Rees",
  "title" : "Sayings of the Century",
  "price" : 8.95
},
{
  "category" : "fiction",
  "author" : "Evelyn Waugh",
  "title" : "Sword of Honour",
  "price" : 12.99
}

-- Return attributes for all books with an ISBN number
SELECT json_evalPath(col2, '$..book[?(@.isbn)]') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
{
  "category" : "fiction",
  "author" : "Herman Melville",
  "title" : "Moby Dick",
  "isbn" : "0-553-21311-3",
  "price" : 8.99
},
{
  "category" : "fiction",
  "author" : "J. R. R. Tolkien",
  "title" : "The Lord of the Rings",
  "isbn" : "0-395-19395-8",
  "price" : 22.99
}

-- Return all books costing less than 10
SELECT json_evalPath(col2, '$..book[?(@.price<10)]') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
{
  "category" : "reference",
  "author" : "Nigel Rees",
  "title" : "Sayings of the Century",
  "price" : 8.95
},
{
  "category" : "fiction",
  "author" : "Herman Melville",
  "title" : "Moby Dick",
  "isbn" : "0-553-21311-3",
  "price" : 8.99
}
        
Using logical operators in the JSON path:
Note: Remember that you cannot use both the && and || operators in the same query
-- Use the && operator to return fiction books costing greater than 10
SELECT json_evalPath(col2, '$.store.book[?(@.category==fiction && @.price > 10)]') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
{
  "category" : "fiction",
  "author" : "Evelyn Waugh",
  "title" : "Sword of Honour",
  "price" : 12.99
},
{
  "category" : "fiction",
  "author" : "J. R. R. Tolkien",
  "title" : "The Lord of the Rings",
  "isbn" : "0-395-19395-8",
  "price" : 22.99
}

-- Use the || operator to return reference books or books costing greater than 10
SELECT json_evalPath(col2, '$.store.book[?(@.category==reference || @.price > 10)]') FROM T1 WHERE COL1 = 1;
1                                                                                           	
----------------------------------------------------------------------------------------------------
{
  "category" : "reference",
  "author" : "Nigel Rees",
  "title" : "Sayings of the Century",
  "price" : 8.95
},
{
  "category" : "fiction",
  "author" : "Evelyn Waugh",
  "title" : "Sword of Honour",
  "price" : 12.99
},
{
  "category" : "fiction",
  "author" : "J. R. R. Tolkien",
  "title" : "The Lord of the Rings",
  "isbn" : "0-395-19395-8",
  "price" : 22.99
} 
Using the function in a WHERE clause:
-- Return all authors from the JSON row where a book costs more than 20.
SELECT json_evalPath(col2, '$..author') from T1 WHERE json_evalPath(col2, '$..book[?(@.price>20)]' ) IS NOT NULL;
1                                                                                           	
----------------------------------------------------------------------------------------------------
Nigel Rees,Evelyn Waugh,Herman Melville,J. R. R. Tolkien

Note that the above statement does not return only the authors of books that cost more than 20, although that might be expected when first reading the SELECT statement. The JSON path $..book[?(@.price>20)] in the WHERE clause only filters the table rows where the first JSON path, $..author, is then applied. The sample table used here has a single row, so WHERE clause examples are limited. However, if the table included many rows having similar JSON store data, then the function in the above WHERE would ensure that only those rows having books priced 20 or higher are selected in the query. The JSON path in the first function would then be applied to the JSON store data in each of the selected rows.