JSON_VALUE

Returns nodes selected with XPath expression.

Syntax

JSON_VALUE ( 'json_data', 'xpath' )

Arguments

'json_data'

Is a character expression of type nvarchar.

'xpath

Is a character expression of type nvarchar. The expression is used to select nodes in json_data.

Return types

nvarchar(128)

Example

SELECT JSON_VALUE(
  DETECT_FACES(
    LOAD_FILE('http://local-content.local/img09.jpg'),
    'https://westus.api.cognitive.microsoft.com/face/v1.0/detect',
    'cf23df2207d99a74fbe169e3eba035e6'),
  '$[0].faceAttributes.age') as age

Getting Started

JsonValue is available at the Central Maven Repository. Maven users add this to your POM.

<dependency>
    <groupId>com.jayway.jsonvalue</groupId>
    <artifactId>json-path</artifactId>
    <version>2.4.0</version>
</dependency>

If you need help ask questions at Stack Overflow. Tag the question 'jsonvalue' and 'java'.

JsonValue expressions always refer to a JSON structure in the same way as XPath expression are used in combination with an XML document. The "root member object" in JsonValue is always referred to as $ regardless if it is an object or array.

JsonValue expressions can use the dot–notation

$.store.book[0].title

or the bracket–notation

$['store']['book'][0]['title']

Operators

Functions

Functions can be invoked at the tail end of a path - the input to a function is the output of the path expression. The function output is dictated by the function itself.

Filter Operators

Filters are logical expressions used to filter arrays. A typical filter would be [?(@.age > 18)] where @ represents the current item being processed. More complex filters can be created with logical operators && and ||. String literals must be enclosed by single or double quotes ([?(@.color == 'blue')] or [?(@.color == "blue")]).

Path Examples

Given the json

{
    "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
        }
    },
    "expensive": 10
}

Reading a Document

The simplest most straight forward way to use JsonValue is via the static read API.

String json = "...";

List<String> authors = JsonValue.read(json, "$.store.book[*].author");

If you only want to read once this is OK. In case you need to read an other path as well this is not the way to go since the document will be parsed every time you call JsonValue.read(...). To avoid the problem you can parse the json first.

String json = "...";
Object document = Configuration.defaultConfiguration().jsonProvider().parse(json);

String author0 = JsonValue.read(document, "$.store.book[0].author");
String author1 = JsonValue.read(document, "$.store.book[1].author");

JsonValue also provides a fluent API. This is also the most flexible one.

String json = "...";

ReadContext ctx = JsonValue.parse(json);

List<String> authorsOfBooksWithISBN = ctx.read("$.store.book[?(@.isbn)].author");


List<Map<String, Object>> expensiveBooks = JsonValue
                            .using(configuration)
                            .parse(json)
                            .read("$.store.book[?(@.price > 10)]", List.class);

What is Returned When?

When using JsonValue in java its important to know what type you expect in your result. JsonValue will automatically try to cast the result to the type expected by the invoker.

//Will throw an java.lang.ClassCastException    
List<String> list = JsonValue.parse(json).read("$.store.book[0].author")

//Works fine
String author = JsonValue.parse(json).read("$.store.book[0].author")

When evaluating a path you need to understand the concept of when a path is definite. A path is indefinite if it contains:

• .. - a deep scan operator
• ?(<expression>) - an expression
• [<number>, <number> (, <number>)] - multiple array indexes

Indefinite paths always returns a list (as represented by current JsonProvider).

By default a simple object mapper is provided by the MappingProvider SPI. This allows you to specify the return type you want and the MappingProvider will try to perform the mapping. In the example below mapping between Long and Date is demonstrated.

String json = "{\"date_as_long\" : 1411455611975}";

Date date = JsonValue.parse(json).read("$['date_as_long']", Date.class);

If you configure JsonValue to use JacksonMappingProvider or GsonMappingProvider you can even map your JsonValue output directly into POJO's.

Book book = JsonValue.parse(json).read("$.store.book[0]", Book.class);

To obtainin full generics type information, use TypeRef.

TypeRef<List<String>> typeRef = new TypeRef<List<String>>() {};

List<String> titles = JsonValue.parse(JSON_DOCUMENT).read("$.store.book[*].title", typeRef);

Predicates

There are three different ways to create filter predicates in JsonValue.

Inline Predicates

Inline predicates are the ones defined in the path.

List<Map<String, Object>> books =  JsonValue.parse(json)
                                     .read("$.store.book[?(@.price < 10)]");

You can use && and || to combine multiple predicates [?(@.price < 10 && @.category == 'fiction')] , [?(@.category == 'reference' || @.price > 10)].

You can use ! to negate a predicate [?(!(@.price < 10 && @.category == 'fiction'))].

Filter Predicates

Predicates can be built using the Filter API as shown below:

import static com.jayway.jsonvalue.JsonValue.parse;
import static com.jayway.jsonvalue.Criteria.where;
import static com.jayway.jsonvalue.Filter.filter;
...
...

Filter cheapFictionFilter = filter(
   where("category").is("fiction").and("price").lte(10D)
);

List<Map<String, Object>> books =  
   parse(json).read("$.store.book[?]", cheapFictionFilter);

Notice the placeholder ? for the filter in the path. When multiple filters are provided they are applied in order where the number of placeholders must match the number of provided filters. You can specify multiple predicate placeholders in one filter operation [?, ?], both predicates must match.

Filters can also be combined with 'OR' and 'AND'

Filter fooOrBar = filter(
   where("foo").exists(true)).or(where("bar").exists(true)
);
   
Filter fooAndBar = filter(
   where("foo").exists(true)).and(where("bar").exists(true)
);

Roll Your Own

Third option is to implement your own predicates

Predicate booksWithISBN = new Predicate() {
    @Override
    public boolean apply(PredicateContext ctx) {
        return ctx.item(Map.class).containsKey("isbn");
    }
};

List<Map<String, Object>> books = 
   reader.read("$.store.book[?].isbn", List.class, booksWithISBN);

Path vs Value

In the Goessner implementation a JsonValue can return either Path or Value. Value is the default and what all the examples above are returning. If you rather have the path of the elements our query is hitting this can be achieved with an option.

Configuration conf = Configuration.builder()
   .options(Option.AS_PATH_LIST).build();

List<String> pathList = using(conf).parse(json).read("$..author");

assertThat(pathList).containsExactly(
    "$['store']['book'][0]['author']",
    "$['store']['book'][1]['author']",
    "$['store']['book'][2]['author']",
    "$['store']['book'][3]['author']");

Tweaking Configuration

Options

When creating your Configuration there are a few option flags that can alter the default behaviour.

DEFAULT_PATH_LEAF_TO_NULL
This option makes JsonValue return null for missing leafs. Consider the following json

[
   {
      "name" : "john",
      "gender" : "male"
   },
   {
      "name" : "ben"
   }
]

Configuration conf = Configuration.defaultConfiguration();

//Works fine
String gender0 = JsonValue.using(conf).parse(json).read("$[0]['gender']");
//PathNotFoundException thrown
String gender1 = JsonValue.using(conf).parse(json).read("$[1]['gender']");

Configuration conf2 = conf.addOptions(Option.DEFAULT_PATH_LEAF_TO_NULL);

//Works fine
String gender0 = JsonValue.using(conf2).parse(json).read("$[0]['gender']");
//Works fine (null is returned)
String gender1 = JsonValue.using(conf2).parse(json).read("$[1]['gender']");

ALWAYS_RETURN_LIST
This option configures JsonValue to return a list even when the path is definite.

Configuration conf = Configuration.defaultConfiguration();

//Works fine
List<String> genders0 = JsonValue.using(conf).parse(json).read("$[0]['gender']");
//PathNotFoundException thrown
List<String> genders1 = JsonValue.using(conf).parse(json).read("$[1]['gender']");

SUPPRESS_EXCEPTIONS
This option makes sure no exceptions are propagated from path evaluation. It follows these simple rules:

• If option ALWAYS_RETURN_LIST is present an empty list will be returned
• If option ALWAYS_RETURN_LIST is NOT present null returned