Page History

Accessing data in JSON Objects within DataMiner schema's

Through the database schema's page ("<appliance url>/dm/schema") it is possible to see the various tables and fields that can be queried. In certain cases it is possible that the table contain JSON objects identified as "jsonb" type fields. These are collections of additional fields.
Image Modified
To see what is contained in such an object in your DataMiner query use: SELECT

SELECT
library_properties::TEXT

...

FROM

...

 
cqx_data.file_library 

With the text in red  

With "library_properties" being the object and table names"cqx_data.file_library" being the table name. This will show all columns & values in the object as a single string.
The result would then look like:
Image Modified
Alternatively you can also run: SELECT
*
FROM

SELECT
*
FROM 
cqx_data.file_library 

But not run it as grid but as JSON.
Image Removeduse the Load Query as JSON instead of grid: 
Image Added
This will show the properties field in a JSON format in most browsers. :
Image Modified
Once you know what is in the object you can also retrieve individual elements from it. To get an individual column from the object use the "->>" operator: SELECT

SELECT 
library_properties ->> 'size' AS lib_size

...

FROM

...

 
cqx_data.file_library

With the text in red being: The object name, the item name (in the object) and SELECT statement referencing the object and the individual column name and the FROM statement the table name. To determine the item column name use the first option described above to see what items columns are contained within the JSON object. Nested JSON OBJECTS:
In some cases the JSON objects can also hold JSON objects themselves (nested objects). These can be identified because the text that appears on running an <OBJECT>::TEXT will have the following structure:
{"<NESTED OBJECT NAME1>":{"COLUMN NAME 1": <value>, "COLUMN NAME 2": <value>, … etc}, {"<NESTED OBJECT NAME2>":{"COLUMN NAME 1": <value>, "COLUMN NAME 2": <value>, … etc}, … etc}
Example:
Image Removed
Or:
Image Removed
To access elements of the nested elements you first need to get to the nested object itself and identify what information you want to get.

...

In the above screenprint we see for instance that "Communities" is a nested object from the "applications" object in the "cqx_data.community_activity_applications" table that contains a field called "user_count". To get the specific user_count of community activity I therefor first need to access the "Applications" object to get to the "Communities" object to then retrieve the specific "user_count" field value.
Image Removed
This can be done in two ways by either using a sub select:
SELECT
split_communities_object ->> 'user_count' as user_count_communities,
*
FROM
(SELECT
*,
applications -> 'Communities' AS split_communities_object
FROM
cqx_data.community_activity_applications
) splitObject
Or by splitting it all in one go (more direct option):
SELECT
applications -> 'Communities' ->> 'user_count' as user_count_communities,
*
FROM
cqx_data.community_activity_applications
The result in both cases is:
Image Removed
In the example I only lifted out one column from the application object, you can of course add multiple.
Note!
If the Object doesn't contain a nested object called "Communities" the value will simply be empty.See also Nested JSON OBJECTS