Page History
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.
- To access an element in an object you use: ->>
- To access an object within an object you use: ->
Anchor _GoBack _GoBack
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 |
The result in both cases is:
Note |
---|
Note! If the Object doesn't contain a nested object called "Communities" the value will simply be empty. |