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.
To see what is contained in such an object in your DataMiner query use:
Code Block | ||
---|---|---|
| ||
SELECT library_properties::TEXT FROM cqx_data.file_library |
With the text in red "library_properties" being the object and "cqx_data.file_library" table names. This will show all columns & values in the object as a single string.
The result would then look like:
Alternatively you can also run:
Code Block | ||
---|---|---|
| ||
SELECT * FROM cqx_data.file_library |
But not run it as grid but as JSON. use the Load Query as JSON instead of grid:
This will show the properties field in a JSON format in most browsers. :
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:
Code Block | ||
---|---|---|
| ||
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 SELECT statement referencing the object ) and the FROM statement the table name. To determine the item name use the first option to see what items are contained within the JSON object.
...