JSON Property Query (advanced)

In order to select and return properties from the property store you usually will use the property.value.get command using a path pattern to select those properties to return based on its path attribute, since it is the most simple way of selecting and loading property values.

But sometimes you need to select properties based on conditions applied on the JSON documents stored as value inside the properties. For example: Return me all properties having a JSON document which contains an attribute firstName with a value of Sam.

You can do so by applying deep querying also known as advanced querying. This means executing the query on JSON data which matches a defined set of conditions. The query is executed directly inside the database and therefore optimized for best performance.

You can execute deep queries by using the command property.query.

This is one of the most advanced and powerful ways to create "SQL-style" filters and joins for properties which can be evaluated directly in the database and therefore are in most cases scale much better than other approaches.

Here is a first example using the command property.query, which loads "recursively" the value of all properties of the app myapp, being a JSON document with attribute title having a value which starts with the text Admin and converts the final result to a JSON in order to use it in the pipeline for further processing.

As you can imagine, this usually becomes a very complex native query since recursive key filtering is combined with JSON path selections, conversions and a LIKE search. The command property.query simplifies this a lot for you. The example will look like this:

• dialect - Depending on the optional parameter dialect, the command property.query can be used with different language implementations. In this example the postgres language is used (which is the default if this parameter is missing). Therefore, you can use most of the native PostgreSQL syntax elements and functions as documented here.

• type - If not specified otherwise by the optional parameter type, the expected property type is application/json by default. So only properties matching this type will automatically be selected for querying.

• select, from, where - These parameters are based on the typical SQL query structure even if their value can differ from the ordinary SQL syntax, based on the selected dialect.

Property Attributes​

Keep in mind that a property has multiple property envelope attributes. See here Property Store (Document Database).

Each of them can be used in the query:

• key - The unique key of the property (modifiable).

• uuid - The unique id of the property (non-modifiable).

• value - The payload of the property (for example a JSON document).

• type - The mime type of the payload. (for example application/json).

• created - The timestamp in ms when this property has been created.

• updated - The timestamp in ms when this property has been changed last.

• timeToLive - The time to live in minutes of this property.

The select Parameter​

The parameter select of the command property.query defines the values, elements or aggregation results to be returned from a property, similar as it is in SQL. The difference to SQL is, that here it is also possible to select and aggregate values from JSON documents in the result set using a special JSON syntax defined by PostgreSQL.

Example 1

Return the property value as string of all app properties having a JSON document as value, recursively:

The result will look like this:

Example 2

Now lets additionally add the property path and the property uuid to the select condition:

The result will look like this:

Aggregate Functions​

Its also possible to use aggregation functions like count(), sum(), avg(), max(), min() for example in the select parameter.

This example counts all JSON properties inside global/app/..., recursively:

The result will look like this:

Convert value to JSON​

Whenever you would like to do a JSON operation on a value using the property.query command, you need to convert the value to a JSON type first. For this, PostgreSQL provides the data types json and jsonb.

The conversion is done by adding the suffix ::json or ::jsonb at the end of the value to convert. Some examples of valid conversions are:

Example

Let's do again an example and output the value attribute of all JSON properties inside global/app/**, but now converted to JSON using the PostgreSQL json type:

The result will look like this:

As you can see, instead of a JSON string for each result row, a well formatted JSON structure is returned instead. Since the result is a JSON, you can directly use it in the pipeline by subsequent commands, without any additional JSON or Map conversions required:

Now, lets additionally add the property attributes path and uuid to the select parameter:

The result will look like this:

As you can see, the text values of the property attributes path and uuid are combined with the JSON object of the value field on each result row. Type conversion to json is done by ::json applied on the value field. Since the value type is set to application/json by default, this can be done without any problem here.

JSON Operators​

After a field was converted to a JSON object using ::json or ::jsonb, you can apply JSON operators and functions on it. See the PostgreSQL documentation about JSON Functions and Operations.

Let's assume a pattern like globa/app/** will return a list of JSON documents like this:

Then, here is an example which outputs the value of of the field foo on any of the selected JSON documents:

This will result in an output like this:

As you can see, the operator -> is used to select an field value from a given JSON result. Only the first JSON has the field foo. The second doesn't. And therefore null is returned here, but no error happened.

It the next chapters you will learn more about the most important JSON operators.

Operator ->​ (select as JSON item)

Also see: https://www.postgresql.org/docs/11/functions-json.html

The JSON operator -> can be used to select an item from a JSON array (if right part is an int) or an JSON object (if right part is a string). It returns the result as JSON type. Supported on jsonb and json inputs.

Example 1 - Select JSON field (json)

Select a JSON field and return the result as JSON text.

Example 2 - Select JSON field (jsonb)

Same as Example 1 but with jsonb.

Example 3 - Select JSON integer field

Select a JSON object and return the result as JSON int.

Example 4 - Select JSON array item

Select a JSON array item and return the result as JSON text.

Example 5 - Select nested JSON array item

Select a JSON array item, nested inside a JSON object and return the result as JSON text.

Example 7 - Select object from JSON array

Select a JSON array item, nested inside a JSON object and return it as JSON object.

Example 8 - Select nested JSON array

Select a JSON array, nested inside a JSON object and return it as JSON array.

Operator ->>​ (select as string)

Also see: https://www.postgresql.org/docs/11/functions-json.html

The JSON operator ->> is similar to -> except that it returns the result always as a string instead of a corresponding JSON type. Supported on jsonb and json inputs.

Example 1 - Select JSON field as string

Select a JSON object and return the result as ordinary string.

Example 2 - Select JSON int field as string

Select a JSON integer field and return the result as string.

Operator #>​ (select as JSON item)

Also see: https://www.postgresql.org/docs/11/functions-json.html

Similar to ->, the JSON operator #> can be used to select an item from a JSON array or an JSON object by applying a path to it. It returns the result as JSON type. Supported on jsonb and json inputs.

Example 1 - Select nested JSON array item

Select a JSON array item, nested inside a JSON object and return it as JSON object.

Operator #>>​ (select as string)

Also see: https://www.postgresql.org/docs/11/functions-json.html

This operator is similar to #> except that it returns the result always as a string. Supported on jsonb and json inputs.

Example 1 - Select nested JSON array item

Select a JSON array item, nested inside a JSON object and return it as string.

Operator ?​ (contains attribute)

Also see: https://www.postgresql.org/docs/11/functions-json.html

The operator ? tests whether a given JSON object contains an attribute with given name at its top level. It returns true or false. Supported only on jsonb inputs.

Example 1 - Attribute exists at top level

Test whether a JSON object contains the given attribute with given name at top level.

Now with non-existing attribute:

JSON Functions​

It is also possible to apply functions on json and jsonb converted data. See the official PostgreSQL documentation about JSON Functions and Operations.

to_json​

Also see: https://www.postgresql.org/docs/11/functions-json.html

The functions to_json(data) to_jsonb(data) try to convert the input data into a JSON type.

Example

Convert the given text data into a jsonb type.

json_array_length​

Also see: https://www.postgresql.org/docs/11/functions-json.html

The functions json_array_length(json) and jsonb_array_length(jsonb) return the length of the given array.

Example

Return the length of a json array.

json_object_keys​

Also see: https://www.postgresql.org/docs/11/functions-json.html

The functions json_object_keys(json) and jsonb_object_keys(jsonb) return the JSON keys of current level.

Example

Return the keys of a json object.

json_strip_nulls​

Also see: https://www.postgresql.org/docs/11/functions-json.html

The functions json_strip_nulls(json) and jsonb_strip_nulls(jsonb) strip all fields containg a null value. This gets applied only for JSON objects and not for JSON arrays.

Example

Remove the the fields with null value from jsons object.

json_set​

Also see: https://www.postgresql.org/docs/11/functions-json.html

The functions json_set(targetjson, path, valuejson, create) and jsonb_set(targetjson, path, valuejson, create) set the valuejson on a JSON object given by targetjson at location defined by path. If no entry exists at path, creates a new element there in case create is set to true (default).

Example 1

Add a new attribute to a given JSON object.

Example 2

Add a new attribute to a given JSON object with explicit conversion using to_json.

The from Parameter​

The from parameter of the command property.query defines one or more path patterns of those properties to return for further filtering. It is similar to the SQL FROM, but instead of tables, it selects properties.

The path patterns are checked whether the currently executing user has the permission to read from these (sub-) paths.

Example:

This example will return "recursively" all the properties of type application/json (= default) inside the app myapp.

In case you would like to define multiple patterns, you need to separate them by comma , and assign each to a variable name using the as keyword.

The variable name can be used to reference the patterns later in the where and select parameters.

Example:

The where parameter​

The where parameter can be used to specify join, filter, group and order conditions for the result, similar to the SQL WHERE part.

You can use any operation here, the PostgreSQL database supports in the WHERE clause.

See: https://www.postgresql.org/docs/11/queries.html

Example

Let's assume, we have a list of customer JSON documents stored in the Property Store under global/app/myapp/data/customer/<uuid> each:

And now we would like to return only those JSON documents where lastName is Meyers, then we could write this query:

This would return a result like this:

Using LIKE​

It is also possible to use the LIKE expression combined with JSON queries in the where parameter. With this expression you can do pattern matching on strings.

See PostgreSQL documentation for more information about LIKE expressions: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-LIKE

Example

Let's assume we would like to rewrite the query from the previous example and return all customers those lastName starts with M:

The result will be:

Using ORDER BY​

The ORDER BY can be used to sort the result set.

See the official PostgreSQL documentation for details about this clause: https://www.postgresql.org/docs/11/queries-order.html

Example

Let's assume, we would like to sort the example JSON documents from the previous example by firstName:

The result will be:

Property Joins​

You can also do joins between properties, similar as you would do between multiple tables in SQL.

To do so, you need to define the key patterns of the properties you would like to join, each separated by a comma , and assigned to a variable using the as keyword. Example:

This example joins all properties matching the pattern global/app/myapp/** with those, matching global/app/anotherapp/** and assigns each to variable names. In the where clause you can then define the join conditions.

Example

Let's assume, we have a list of customer entities and address with multiple instances stored as JSON in the Property Store, whereas each person has a one to one relationship to an address entity:

JSON documents stored in property value under global/app/myapp/data/customer/<uuid>:

JSON documents stored in property value under global/app/myapp/data/address/<uuid>:

Now, let's further assume you would like to return all customers living in New York. For this you could do a join like this:

This will result in a single output:

You can also return the address document and the customer document:

This will output the person and the address JSON in each result row:

Merging join results into a JSON​

Sometimes you would like to combine the join results from different JSON properties into a a single JSON. This can be done for example using the json_set function.

Example 1

In order to merge the result from the example above, you could rewrite the select parameter of the query like this:

In the select parameter we set the address JSON to the customer JSON under new key currentAddress which will create a merged JSON as result like this:

Example 2

You can also merge property attributes outside of the (JSON) value into the resulting JSONs. Let's assume you would like to merge the property attribute key as new entry propertyKey into the result JSON. For this you could define a query like this:

The result will look like this, then:

As you can see, an additional field with name propertyKey was added and its value was set to the value coming from the property attribute key.

Remember that each property has an attribute structure like this:

So you can access each of these fields (called "attributes") also in your queries using their names.

Query Parameters (prepared statements)​

The command property.query also supports parameters ("prepared statements") so values coming from users are secured properly.

Here is an example how to define the parameters using the params keyword:

As you can see, the query parameter is defined in the query using :theName and in params the value for it gets assigned.

Note: Using the query parameter inside quotes will not work. So instead of this

use

Date and Time Ranges​

PostgreSQL offers a huge set of date and time functions and operations: https://www.postgresql.org/docs/11/functions-datetime.html .

You can use them in the property.query command.

Additionally, PIPEFORCE has many utils, which can help you to create date and time based queries. The most important utils can be found in the @date util.

Below you can find some query examples, how to use these tools with the property.query command.

Example 1 - Return all properties created today

Example 2 - Return all properties created yesterday

Example 3 - Return all properties created this week

Example 4 - Return all properties created last week

Example 5 - Return all properties created this month

Example 6 - Return all properties created last month

Example 7 - Return all properties created in the last 6 months

Example 8 - Return all properties created in the last 12 months

Example 9 - Return all properties created last year

LIMIT and OFFSET (Pagination)

In order to return only a subset of the data, you can use OFFSET and LIMIT in the PostgreSQL syntax as described here: https://www.postgresql.org/docs/current/queries-limit.html

Example:

As you can see, the LIMIT and OFFSET should always be used in combination with ORDER BY so consistent value orders are returned for the subset calculation.

Additionally, make sure that LIMIT and OFFSET are always passed as parameters if they have been set from external for security reasons.

JSON Property Search (simple)

status:Since 10.0.3

While the command property.query is the most powerful command to query for JSON data from the property store database, this power also comes with complexity.

Therefore, the command property.json.search was introduced. This simplified command is focusing on basic search cases on JSON documents inside the property store and therefore it is the best choice in case you would just search for documents based on standard search conditions. Otherwise, for advanced queries with joins, in-database calculations and dynamic JSON creations for example, you should consider to use property.query instead.

Here is an example how to use the property.json.search command in an automation pipeline which shows all options:

This advanced example returns all JSON properties inside global/app/io.pipeforce.myapp/data/person/ with values having firstName set to Max and lastName set to Smith. In case no op parameter is given, the default operator = is used. In case a property has no field mentioned in the condition or orderBy section, it will be ignored.

Parameters

pattern

This is a path pattern and selects the properties to search inside. All of these matching properties must be of type application/json otherwise, they will be ignored and not included in the search.

This parameter is mandatory.

condition

This parameter defines the search condition to be applied. It can be a nested set of and / or conditions.

• and: Defines an array of field conditions. All entries of the array will be combined as AND.

• or: Defines an array of field conditions. All entries of the array will be combined as OR.

• field: The mandatory name of the field to select in the condition. If this field is missing in the selected JSON, this condition is ignored.
  The field name can also point to a nested field using the dot operator. For example:

  person.address.streetName

  The backslash \\. can be used to escape the dot in case it is part of a field name.
  If the field name is set to *, then the whole property value (all JSON field names and all values) will be searched by given condition. For example in order to do a “quicksearch” for a given text in all pattern selected JSON properties, you could use a condition in YAML format like this:

  fieldName: * op: ilike value: "%searchtext%"

• op: The optional operator to apply on this condition. Can be one of:

  • = : Equals. This is the default if op parameter is is not given.

  • != : Not equals.

  • < : Lesser than.

  • > : Greater than.

  • <= : Lesser or equal than.

  • >= : Greater or equal then.

  • between: To check if the JSON field is in range between the two values given by value condition array where first value is the starting point and the second value is the end point. Example where the value of field myNumber is expected to be between 0 and 100 (Note: This also works with date and number type):

    {"field":"myNumber", "op":"between", "value":[0, 100]}

  • like : A like search by given value. You can also use % as wildcard in the value the same ways as it is used in SQL.

  • ilike : Same as like but with case-insensitive check.

  • contains : Similar to like but places % at the beginning and the end of the value so it searches for any containment. Also additional % wildcards are allowed.

  • icontains : Same as contains but searches case-insensitive.

  • in : Expects a an array of values to check whether the given field contains at least one of the entries. Example in JSON format:

    {"field":"city", "op":"in", "value":["berlin", "munich", "hamburg"]}

  • not in : Negation of in.

• value: The value to search for. This field is mandatory and can be a literal or an array of literals, depending what the operator expects.

• type: The optional type of the JSON field value. Can be one of: string, boolean, number, integer, date. If not given, the type of the JSON field will be derived from the type of the value condition.

• format: If type is number or date, this field is mandatory and defines the format pattern. Also see the PostgreSQL format pattern documentation: https://www.postgresql.org/docs/current/functions-formatting.html Example:

  {"field":"birthDate", "type":"date", "format": "MM/DD/YYYY", "op":">", "value": "01/01/1990"}

• zone: If type is set to data, this optional field defines the time zone to be used for the comparison. Default is UTC if not given. See PostgreSQL documentation for valid zone strings.

Every condition can be written also as JSON. Here is an example where the condition part of the automation pipeline at the beginning is now rewritten as JSON:

It is also possible to nest the conditions like this example shows:

orderBy

This is a list of fields, to order the result on, applied from top to down on the result. If this parameter is not applied, the result will be ordered by created date of the properties.

• field: The name of the JSON field to order by. If this field doesn’t exist in a JSON, this entry will be ignored. This parameter is mandatory.

• direction: The direction of order which can be asc or desc. If this parameter is missing, asc is used as default.

• type: The type of ordering logic. Can be one of string, integer, number, boolean or date. This tells the database how to convert the data and which ordering logic to apply. If this parameter is missing, string is used as default.

• format: If type is date, this field is mandatory and defines the format pattern. Also see the PostgreSQL format pattern documentation: https://www.postgresql.org/docs/current/functions-formatting.html Example:

  "orderBy": [{"field":"birthDate", "type":"date", "format": "MM/DD/YYYY"}]

• zone: If type is set to data, this optional field defines the time zone to be used for the comparison. Default is UTC if not given. See PostgreSQL documentation for valid zone strings.

Also the orderBy parameter can be written as JSON. See here an example:

Note: Since no direction nor the type attribute is given in this example, the defaults asc and string will be used.

max

The maximum number of results to return. This value cannot be bigger than 100. If this parameter is missing, 100 will be used as default.

offset

The 0-based offset inside the result in order to return just a subset. This is especially useful for pagination. If this parameter is missing, 0 will be used as default.

Response structure

The structure of the result of calling this command is like this example shows:

• path = The path of the matching JSON property.

• created = The creation timestamp in millis when this matching JSON property was created.

• uuid = The uuid of the matching JSON property.

• value = Contains the result value payload as JSON object or array.

Calling via command REST API

In case you would like to execute the property.json.search command using the RESTful endpoint, you can do so by executing it via POST as this HTTP script example shows:

Note the request parameter ?params=body which puts all command parameters into the body as JSON.

Also see: https://pipeforce.github.io/api.html#/Command%20API%20-%20property/postcommand_property_json_search