Building an array of values from Jsonb column data and querying records using that array-CodePudding

I'm working with a subscriptions and plans tables. The subscriptions table has a product_quantity column and the plans table has a jsonb column called product_quantities_config that stores data in the following format {min: 0, step: 1, max: 10}.

Given the example above, would it be posible to transform the jsonb column data into an array of values starting from the min value up to the max value to end up with something like [0, 1, 2 .. 10] ?

I need to build a query to find all subscriptions where the product_quantity is not within the plans product_quantities_config boundaries, but I don't know how to transform the plans.product_quantity_config jsonb data into an array of values in Postgres (if it's even possible).

I suppose I'd then use that array of values as a subquery to find all subscriptions with a product_quantity NOT IN the given array of values.

CodePudding user response：

You need to write a cross join statement to get a table of all values from jsonb, after which you can write a where product_quantity not in condition for the product table

select * from subscriptions where product_quantity not in (select  value::int  from plans  cross join jsonb_each_text(product_quantities_config))

Demo in DBfiddle

CodePudding user response：

I don't think you need to build an array.

It's unclear to me how the two table are related, but may something like this might do what you want:

select s.*
from subscriptions s
where not exists (select *
                  from plans
                  where s.product_quantity between (product_quantities_config ->> 'min')::int
                                               and (product_quantities_config ->> 'max')::int)