Hazelcast SQL - JSON Functions

Giriş

Açıklaması şöyle

Use standard JSON SQL functions for OBJECT and ARRAY aggregations

Bunlar şöyle

JSON_QUERY

JSON_VALUE

JSON_ARRAY

JSON_OBJECT

1. JSON_QUERY

Açıklaması şöyle

The JSON_QUERY() function extracts a JSON value from a JSON document or a JSON-formatted string that matches a given JsonPath expression.

4 parametre alıyor. wrapperBehavior ve onClauseArg isteğe bağlı

jsonArg: JSON value or a JSON-formatted string.

jsonPath: A JsonPath expression that identifies the data that you want to get from the jsonArg parameter.

wrapperBehavior: What to do with return results.

  WITHOUT [ARRAY] WRAPPER (default): Up to one matched value is returned without wrapping in an array. Fails, if multiple values match.

  WITH [CONDITIONAL] [ARRAY] WRAPPER: Returns a single match directly without wrapping. If there are multiple matches, they are returned as a JSON array.

  WITH UNCONDITIONAL [ARRAY] WRAPPER: Always wrap matched values in a JSON array.

onClauseArg: A value to return in case of the given ON condition:

 EMPTY ARRAY

 EMPTY OBJECT

 ERROR

 NULL

Örnek

Elimizde şöyle bir JSON olsun

'{
    "CustomerId": "123456",
    "Orders": [{
            "OrderId": "852",
            "OrderManifests": [{
                    "ShippedProductId": 884,
                    "ProductId": 884
                }, {
                    "ShippedProductId": 951,
                    "ProductId": 2564
                }    
            ]
        }, {
            "OrderId": "5681",
            "OrderManifests": [{
                    "ShippedProductId": 198,
                    "ProductId": 4681
                }, {
                    "ShippedProductId": 8188,
                    "ProductId": 8188
                }, {
                    "ShippedProductId": 144,
                    "ProductId": 8487
                }
            ]
        }
    ]
}'

OrderManifest'leri almak için şöyle yaparız

"SELECT JSON_QUERY(this, '$..OrderManifests[*]' WITH ARRAY WRAPPER) 
  as OrderManifests FROM "  + mapName

OrderManifest'leri müşteri numarasına göre almak için şöyle yaparız

SELECT JSON_QUERY(this, '$..OrderManifests[*]' WITH ARRAY WRAPPER) 
  as OrderManifests FROM " + mapName 
  + " WHERE JSON_VALUE(this, '$.CustomerId' returning BIGINT) = customerID

müşteri numarası string ise şöyle yaparız

SELECT JSON_QUERY(this, '$..OrderManifests[*]' WITH ARRAY WRAPPER) 
  as OrderManifests FROM " + mapName 
  + " WHERE JSON_VALUE(this, '$.CustomerId' returning VARCHAR) = 'customerID'

2. JSON_VALUE

Gerçekleştirimi com.hazelcast.jet.sql.impl.expression.json.JsonValueFunction sınıfında

Açıklaması şöyle

The JSON_VALUE() function extracts a primitive value, such as a string, number, or boolean that matches a given JsonPath expression. This function returns NULL if a non-primitive value is matched, unless the ON ERROR behavior is changed.

4 parametre alıyor. RETURNING ve onClauseArg isteğe bağlı

jsonArg: JSON value or a JSON-formatted string

jsonPath: A JsonPath expression that identifies the data that you want to get from the jsonArg parameter.

RETURNING: Converts the result to the dataType (VARCHAR by default). If the value cannot be converted to the target type, throws an error.

onClauseArg: What to return in case of the ON condition:

  DEFAULT <literal | column | parameter>

  ERROR

  NULL

Örnek

Şöyle yaparız

IMap<Integer, HazelcastJsonValue> map = instance().getMap(mapName);
map.put(42, new HazelcastJsonValue("{\"first_name\":\"value\"}"));
SqlResult rows = instance().getSql().execute("select * from " + mapName + 
  " where JSON_VALUE(this, '$.\"first_name\"' returning VARCHAR) like 'v%'");