Writing migration(s) to update a jsonb column in one table based on a column in second table

Questions / Help
1

Hello. I have a table named clubs and these are some of the relevant columns in this table:

COLUMN_NAME     DATA_TYPE  IS_NULLABLE  
id              uuid       NO
general_fields  jsonb      YES

and I have another table named plans and these are some of the relevant columns in this table:

COLUMN_NAME                 DATA_TYPE  IS_NULLABLE  
id                          uuid       NO
club_id                     uuid       NO
hide_in_online_application  bool       YES

A club can have multiple plans.

I want to write a query that updates the boolean type allow_applications as FALSE inside the column general_fields for those clubs in the clubs table that only have plans where hide_in_online_application is TRUE

The query will have something like:

UPDATE clubs
SET general_fields = jsonb_set(general_fields, '{allow_applications}', '"false"')
+ the condition where clubs has no plans
+ the condition where all plans from clubs are hide_in_online_application == true

What would be the best way to achieve this?

2

I decided to handle this with a raw SQL query:

UPDATE clubs
SET general_fields = jsonb_set(general_fields, '{allow_applications}', '"false"')
WHERE EXISTS(SELECT 1 FROM plans WHERE clubs.id = plans.club_id AND plans.hide_in_online_application = true)
AND NOT EXISTS(SELECT 1 FROM plans WHERE clubs.id = plans.club_id AND plans.hide_in_online_application = false)

Maybe this will help someone working on something similar.

2 Likes