JSON Property Querying and Searching
- 1 JSON Property Query (advanced)
- 1.1 Property Attributes
- 1.2 The select Parameter
- 1.3 Convert value to JSON
- 1.4 JSON Operators
- 1.5 JSON Functions
- 1.5.1 to_json
- 1.5.2 json_array_length
- 1.5.3 json_object_keys
- 1.5.4 json_strip_nulls
- 1.5.5 json_set
- 1.6 The from Parameter
- 1.7 The where parameter
- 1.7.1 Using LIKE
- 1.8 Using ORDER BY
- 1.9 Property Joins
- 1.10 Query Parameters (prepared statements)
- 1.11 Date and Time Ranges
- 1.12 LIMIT and OFFSET (Pagination)
- 2 JSON Property Search (simple)
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.
Even if it is also possible to run deep queries which are not related to JSON, most use-cases are related to JSON documents. Therefore, this will be the focus of these documentation pages. This means we will focus on properties with type
set to application/json
here.
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:
pipeline:
- property.query:
dialect: postgres
select: |
value::json
from: |
global/app/myapp/**
where: |
value::json ->> 'title' LIKE 'Admin %'
dialect
- Depending on the optional parameterdialect
, the commandproperty.query
can be used with different language implementations. In this example thepostgres
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 parametertype
, the expected property type isapplication/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 selecteddialect
.
Since in most cases, the property.query
command will be used in conjunction with querying JSON documents, if not stated otherwise, all sections below will assume the parameter type
is left out (= set to its default value application/json
).
Also the parameter dialect
is left out and therefore postgres
is used by default since this whole section is about PostgreSQL JSON querying.
Tip: Use the pipe |
character for the query parameters for better readability and to avoid quote and tick '"\"mixing-hells\""'.
Property Attributes
Keep in mind that a property has multiple property envelope attributes. See here https://logabit.atlassian.net/wiki/spaces/PA/pages/2545451009.
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 exampleapplication/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:
pipeline:
- property.query:
select: |
value
from: |
global/app/**
The result will look like this:
[
"{\"foo\":\"bar\"}",
"{\"hello\":\"world\"}"
]
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)
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: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:op
: The optional operator to apply on this condition. Can be one of:=
: Equals. This is the default ifop
parameter is is not given.!=
: Not equals.<
: Lesser than.>
: Greater than.<=
: Lesser or equal than.>=
: Greater or equal then.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 aslike
but with case-insensitive check.contains
: Similar tolike
but places%
at the beginning and the end of the value so it searches for any containment. Also additional%
wildcards are allowed.icontains
: Same ascontains
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:not in
: Negation ofin
.
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.
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 beasc
ordesc
. If this parameter is missing,asc
is used as default.type
: The type of ordering logic. Can be one ofstring
,integer
,number
,boolean
ordate
. 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.
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