Question 268316 in PostgreSQL Question posted in
The official documentation can be found here.

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

3

Answers


  1. 0

    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"'), ('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.

    Login or Signup to reply.
  2. 0

    You can use the jsonb_array_elements() function to turn a JSON array into rows :

    select option_tb, count(opt->'object'->'someText')
from tableT
  cross join jsonb_array_elements(tableT.option_tb->'options') AS opt
group by option_tb

    Demo here

    Login or Signup to reply.
  3. 0

    Simply you can use operators and functions to achieve this for example:

    SELECT COUNT(DISTINCT option_tb->>'someText') AS uniqueOptions
FROM tableT
WHERE option_tb->'options' IS NOT NULL;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search