Is good idea to split the JSONB field into a separate table in my case?

Question

In my case I have a table named clients that follows the following information:

store_listing:
#######
id SERIAL PK
title VARCHAR(100) not null
features JSONB not null
comments TEXT
vat_num VARCHAR(100) NOT null

This table I use to generate:

1. Client listings (for example csv generations for various 3rd party services)
2. Change banners to website for various promos
3. Enable and disable various features that client has access to.

A typical value can be:

{
   banner_cats:{
      active: true,
      types: ['persian','aegean']
   },
   promo_petfest_2023:{
     active: true,
     meta: ['dogs','cats','giraqffes']
   },
   1bank_catzoo: {
     active: true
   },
   feature_virtual_pet: {
     active: true
   }
}

Each time I need to flag and place meta to a specific Store_listing I need to update the features column. Also in my case promos can be generated on the fly according for example a big organization (e.g. WWE) wants to make a promo where will place special banners to my website Therefore I'll have to update the integration column like this:

{
   banner_cats:{
      active: true,
      types: ['persian','aegean']
   },
   promo_petfest_2023:{
     active: true,
     meta: ['dogs','cats','giraqffes']
   },
   1bank_catzoo: {
     active: true
   },
   feature_virtual_pet: {
     active: true
   },
   wwe_wild_beast_payperview:{
      active: true
   }
}

That seems like a bad decision to me therefore I thought this approach:

features_and_promos_settings
####
listing_id FK store_listing
identifier: String (lowercase, does not contain spaces)
active: boolean
meta: jsonb
type: ENUM(PROMO,FEATURE,BANNER) NULL
#####
PK (listing_id, identifier)

So in my case the integrations column will contain this value (listing_id = 1) :

{
   banner_cats:{
      active: true,
      types: ['persian','aegean']
   },
   promo_petfest_2023:{
     active: true,
     meta: ['dogs','cats','giraqffes']
   },
   1bank_catzoo: {
     active: true
   },
   feature_virtual_pet: {
     active: true
   },
   wwe_wild_beast_payperview:{
      active: true
   }
}

Will turn into records in features_and_promos_settings

listing_id	identifier	active	meta	type
1	banner_cats	true	{types: ['persian','aegean']}	BANNER
1	promo_petfest_2023	true	{meta: ['dogs','cats','giraqffes']}	PROMO
1	1bank_catzoo	TRUE	NULL	NULL
1	feature_virtual_pet	TRUE	NULL	FEATURE
1	wwe_wild_beast_payperview	TRUE	NULL	BANNER

But would this approach will benefit me in cases such as searching and resource usage during select. The RDMS that I'll use is postgresql-11.

I expect that may reports will be generated for KPIS (e.g. to answer questions How many listings did participate in a specific promo)

Answer 1

I'd say that splitting up the JSON array into a set of rows in a table is certainly a good idea. Your updates will become faster, and your queries won't become slower.