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.

You never described table_c. I assume you meant table_c in your last sentence. So, I guess, you want to count the array elements in table_c's packages column? Why does your result shows 2 at total_count, I can see only one in your example — S-Man
– S-Man, Commented Jan 7, 2021 at 7:06
Furthermore you should normalize your tables. It is never a good idea to store elements in arrays. You should store each element in a separate record. — S-Man
– S-Man, Commented Jan 7, 2021 at 7:06

Nikhil Patil · Accepted Answer · 2021-01-07 07:58:02Z

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.

answered Jan 7, 2021 at 7:58

Nikhil Patil

2,5331 gold badge9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query a column of table having datatype as jsonb - Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related