Querying deep nested json value from postgresql

I would like to count distinct values from someText property from nested jbson object, but I can't figure out how properly do it. Any suggestions are appreciated.

This is my jbson object

{ options: [    
        {
            "object": { "someText": "example1"}
        },
        {
            "object": { "someText": "example2"}
        },
        {
            "object": { "someText": "example3"}
        }
    ]
}

This is my query:

SELECT COUNT(distinct option_tb) AS uniqueOptions FROM tableT
WHERE option_tb->'options'->0 IS NOT NULL
AND option_tb->'options'->>'object'-> 'someText' is not null

Solution

You can use set-returning jsonb_path_query():

SELECT COUNT(distinct v) AS uniqueOptions FROM tableT,
jsonb_path_query(option_tb,'$.options[*].object.someText?(@!=null)') as _(v);

This produces implicit lateral join with your table, each option_tb spawning one row per match found by the function. No match means no rows returned. You will end up counting all possible distinct values of someText in all rows. This also doesn't mind varying jsonb structures with missing elements in some rows: demo

create table tableT (option_tb jsonb);
insert into tableT values
('{ "options": [ {"object": { "someText": "example1"}},
                 {"object": { "someText": "example2"}},
                 {"object": { "someText": "example3"}},
                 {"object": { "someText": null}}      ]}'),
('{ "options": [ {"object": {"someText": "example4"}} ]}'),
('{ "options": [ {"differentObject": { }            } ]}'),
('{ "options": []}'), ('{ "options": {}}'),
('{ "options": "hello"}'), ('{ "notEvenOptions": 1}'),
('[]'),('{}'),('1'),('"a"'),('false'),('"null"'),('null'),(null);

SELECT COUNT(distinct v) AS uniqueOptions,array_agg(distinct v) FROM tableT,
jsonb_path_query(option_tb,'$.options[*].object.someText?(@!=null)') as _(v);

uniqueoptions	array_agg
4	{""example1"",""example2"",""example3"",""example4""}

Note that null comparison in JSONPath expressions works differently from plain SQL: in SQL v=null yields null regardless of the v value. In JSONPath, it works like v is null.

To get the same result in versions 9.4 to 11 that didn't yet support JSONPath expressions, with a bit of care you can use jsonb_array_elements(), similar to what @SelVazi suggested: demo

SELECT count(distinct v) AS uniqueOptions, array_agg(v) FROM (
    SELECT v#>'{object,someText}' AS v FROM tableT,
    jsonb_array_elements( case when 'array'=jsonb_typeof(option_tb->'options')
                          then option_tb->'options'
                          else '[]'::jsonb end) as _(v) 
) as _(v) WHERE jsonb_typeof(v)<>'null';

It's good to remember that your tableT is actually tablet, unless you double-quote it.