What is a JSON Property Query?

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 powerful command allows you 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 compared to doing it in memory.

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 parameter dialect, the command property.query can be used with different language implementations. In this example the postgres language is used (which is currently the only implementation available). 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.

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 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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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: .

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:

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.