JSONB - Sub-queries within embedded data

Question

I am using PostgreSQL (v11.6) that has a table with the JSONB column that contains the value below. There can be multiple values in the column i.e. there can be ID's from 1 to 100 e.g. id:1 -100, I have only shown 2 entries in my sample.

I have a query currently that returns the number of elements for each row in the column shown below which works well.

SELECT  jsonb_array_elements(my_column::jsonb) FROM my_table

I want to get the number of elements that meet a particular condition for each row e.g. where chosen is not null, in this sample case below the count would be 1. I have researched this for a while now and have not found a solid way to put in a filter. Advice appreciated.

[
  {
    "id": 1,
    "choices": [
      {
        "id": 100,
        "misc_data": [
          {
            "id": 1000
          },
          {
            "id": 1002
          }
        ]
      }
    ],
    **"chosen": 2**
  },
  {
    "id": 2,
    "choices": [
      {
        "id": 200,
        "misc_data": [
          {
            "id": 2002
          },
          {
            "id": 2050
          }
        ]
      }
    ]
  }
]

Answer 1

You need a subquery to get the count:

select ..., (select count(*)
             from jsonb_array_elements(the_column) as x(e)
             where x.e ? 'chosen') as chosen_count
from the_table

If you want to check for specific values, you can use:

select ..., (select count(*)
             from jsonb_array_elements(the_column) as x(e)
             where (x.e ->> 'chosen')::int > 0) as chosen_count
from the_table