JSON Property Querying
- 1 What is a JSON Property Query?
- 2 JSON Operators
- 3 JSON Functions
- 3.1 to_json
- 3.2 json_array_length
- 3.3 json_object_keys
- 3.4 json_strip_nulls
- 3.5 json_set
- 4 The from Parameter
- 5 The where parameter
- 5.1 Using LIKE
- 6 Using ORDER BY
- 7 Property Joins
- 8 Query Parameters (prepared statements)
- 9 Date and Time Ranges
- 10 LIMIT and OFFSET (Pagination)
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.
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 parameterdialect
, the commandproperty.query
can be used with different language implementations. In this example thepostgres
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 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
).
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 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 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:
dialect: postgres
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 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.