Page History
To see how to examine a JSON object, please read the following article first: Accessing json objects in database schemas.
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:
Or:
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.
This can be done in two ways by either using a sub select:
Code Block | ||
---|---|---|
| ||
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):
Code Block | ||
---|---|---|
| ||
SELECT applications -> 'Communities' ->> 'user_count' as user_count_communities, * FROM cqx_data.community_activity_applications |
...
Note |
---|
Note! If the Object doesn't contain a nested object called "Communities" the value will simply be empty. |
...