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:

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: ->

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:
 

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: 

See also Accessing json objects in database schemas