postgresql - Query a column of table having datatype as jsonb - Postgres

Question

I have three tables lets say table_a, table_b, table_c whose structure are as follows,

table_a,

id     |     name     |     created_at     |     updated_at
---------------------------------------------------------------
1      |john doe      |2021-01-01 15:00:00 |2021-01-01 15:00:00

table_b,

id     |     package  |     created_at     |     updated_at
---------------------------------------------------------------
1      |package_1     |2021-01-01 15:01:00 |2021-01-01 15:0:00

table_c,

id     |   table_a_id |     packages                       |     created_at     |     updated_at
--------------------------------------------------------------------------------------------------------
1      |1             |[{"id":1, "package": "package_1"}]  |2021-01-01 15:10:00 |2021-01-01 15:10:00

I need to run a query and get the count of packages based on users. For example, the result should be as follows,

table_a_id     |     table_a_name     |     table_b_id     |     table_b_package     |      total_count
--------------------------------------------------------------------------------------------------------
1              |john doe              |1                   |package_1                |2

There can be multiple package in packages column of table_b as [{...}]

How can i achieve the result.

Answer 1

If you just need to count the number of elements in your table_c.packages, then you can use jsonb_array_length -

Final Query would look like -

select 
    a.id,a.name,jsonb_array_length(c.packages) as count 
from table_a a 
    join table_c c on a.id = c.table_a_id

I am not sure what role table_b plays here so I have skipped it.